50K record DELETE Begins, 100% CPU, Never Completes 1 hour later

Again, we have an odd performance problem with PGSQL, 7.4b2.

Here is the query:

delete from numplan where pkid in
(select numplan.pkid from numplan
left outer join pilothuntgroup on numplan.pkid=pilothuntgroup.fknumplan
left outer join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan
where numplan.tkpatternusage=2
and pilothuntgroup.fknumplan is null
and devicenumplanmap.fknumplan is null);

The query starts, PGSQL shoots to 134MB(!) of memory and 100% CPU and never completes.

The query works fine on smaller datasets. This occurs when 50K+ records exist in the numplan table.

Here is the query plan:

ccm=# explain delete from numplan where pkid in (select numplan.pkid from numplan left outer join pilothuntgroup on numplan.pkid=pilothuntgroup.fknumplan left outer join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan where numplan.tkpatternusage=2 and pilothuntgroup.fknumplan is null and devicenumplanmap.fknumplan is null);
QUERY PLAN
------------------------------------------------------------------------------------------------
Merge IN Join (cost=37947.25..40851.71 rows=82225 width=6)
Merge Cond: ("outer"."?column3?" = ("inner".pkid)::text)
-> Sort (cost=11481.65..11687.35 rows=82279 width=46)
Sort Key: (public.numplan.pkid)::text
-> Seq Scan on numplan (cost=0.00..2936.79 rows=82279 width=46)
-> Materialize (cost=26465.60..27930.85 rows=82225 width=40)
-> Merge Left Join (cost=23917.22..25822.60 rows=82225 width=40)
Merge Cond: (("outer".pkid)::text = "inner"."?column2?")
Filter: ("inner".fknumplan IS NULL)
-> Merge Left Join (cost=11407.97..11819.13 rows=82225 width=40)
Merge Cond: ("outer"."?column2?" = "inner"."?column2?")
Filter: ("inner".fknumplan IS NULL)
-> Sort (cost=11406.89..11612.45 rows=82225 width=40)
Sort Key: (public.numplan.pkid)::text
-> Seq Scan on numplan (cost=0.00..3142.49 rows=82225 width=40)
Filter: (tkpatternusage = 2)
-> Sort (cost=1.08..1.09 rows=4 width=42)
Sort Key: (pilothuntgroup.fknumplan)::text
-> Seq Scan on pilothuntgroup (cost=0.00..1.04 rows=4 width=42)
-> Sort (cost=12509.25..12734.70 rows=90180 width=40)
Sort Key: (devicenumplanmap.fknumplan)::text
-> Seq Scan on devicenumplanmap (cost=0.00..3326.80 rows=90180 width=40)
(22 rows)

---
Clay
Cisco Systems, Inc.
claycle(at)cisco(dot)com
(972) 813-5004

I've stopped 19,658 spam messages. You can too!
One month FREE spam protection at http://www.cloudmark.com/spamnetsig/}