From: | "Josh Berkus" <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Wierd query behaviour |
Date: | 2002-04-26 19:38:44 |
Message-ID: | web-1385952@davinci.ethosmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Lebedev | 2002-04-26 20:01:19 | Re: Connection authentication |
Previous Message | PG Explorer | 2002-04-26 17:25:38 | Re: Wierd error for COPY command |