Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 4F986475451 for ; Fri, 26 Apr 2002 15:38:44 -0400 (EDT) Received: from [209.122.114.82] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1385952 for ; Fri, 26 Apr 2002 12:38:44 -0700 From: "Josh Berkus" Subject: Wierd query behaviour To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Fri, 26 Apr 2002 12:38:44 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200204/334 X-Sequence-Number: 7598 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