Wierd query behaviour

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_clientsWHERE 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.30rows=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.97rows=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