pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Wierd query behaviour
2+ messages / 2 participants
[nested] [flat]

* Wierd query behaviour
@ 2002-04-26 19:38 Josh Berkus <josh@agliodbs.com>
  2002-04-26 22:43 ` Re: Wierd query behaviour Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Josh Berkus @ 2002-04-26 19:38 UTC (permalink / raw)
  To: pgsql-sql

Folks,

Here's an interesting whodunit:

The following query:

UPDATE cases SET status = 99
WHERE status = 1 AND NOT EXISTS(
	SELECT case_id FROM case_clients
	WHERE date_resolved IS NULL OR
		date_resolved > ((current_date - "interval"('1 year'))::DATE)
		and case_clients.case_id = cases.case_id);

With this simple plan:

Seq Scan on cases  (cost=0.00..6708.34 rows=28429 width=146)
  SubPlan
    ->  Seq Scan on case_clients  (cost=0.00..2898.30 rows=48073
width=4)

... Never completes on my system.  It just hangs, for at least 15
minutes before I cancel it.


This query, desingned to acheive the same result:

UPDATE cases SET status = 99
FROM (SELECT case_id, max(coalesce(date_resolved, '2100-12-31'::DATE))
as maxdate
      FROM case_clients WHERE case_status NOT IN ('CLS','CL') GROUP BY
case_id) max_res
WHERE status = 1 and maxdate < ((current_date - "interval"('1
year'))::DATE)
		and max_res.case_id = cases.case_id;

With this fearsome-looking plan:

Merge Join  (cost=19202.83..19429.31 rows=5615 width=150)
  ->  Sort  (cost=15685.45..15685.45 rows=56859 width=146)
        ->  Seq Scan on cases  (cost=0.00..3254.99 rows=56859
width=146)
  ->  Sort  (cost=3517.38..3517.38 rows=5657 width=8)
        ->  Subquery Scan max_res  (cost=0.00..3164.80 rows=5657
width=8)
              ->  Aggregate  (cost=0.00..3164.80 rows=5657 width=8)
                    ->  Group  (cost=0.00..2881.97 rows=56568 width=8)
                          ->  Index Scan using idx_caseclients_case on
case_clients  (cost=0.00..2740.55 rows=56568 width=8)

.... Completes in about 30 seconds.  

I don't need a solution, as the re-writing of the query solved the
problem for me.  It's just curious, that's all.

FYI:  All relevant columns have indexes, the DB has been vacuumed, the
row and width estimates look accurate, and I am not getting any
transaction log warnings or errors from postmaster.

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Wierd query behaviour
  2002-04-26 19:38 Wierd query behaviour Josh Berkus <josh@agliodbs.com>
@ 2002-04-26 22:43 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-04-26 22:43 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> UPDATE cases SET status = 99
> WHERE status = 1 AND NOT EXISTS(
> 	SELECT case_id FROM case_clients
> 	WHERE date_resolved IS NULL OR
> 		date_resolved > ((current_date - "interval"('1 year'))::DATE)
> 		and case_clients.case_id = cases.case_id);

It says here that OR binds at lower priority than AND.  Did you really
mean

	... WHERE (date_resolved IS NULL OR
		date_resolved > ((current_date - "interval"('1 year'))::DATE))
		and case_clients.case_id = cases.case_id);

Since IS NULL isn't indexable, the first version cannot be indexscanned
at all.  The second version gives you a shot at using the
case_clients.case_id index.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-04-26 22:43 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-26 19:38 Wierd query behaviour Josh Berkus <josh@agliodbs.com>
2002-04-26 22:43 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox