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

[Clay Luther]

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 nevercompletes.

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 fromnumplan 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=82225width=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.
cl*****@cisco.com
[phone]
I've stopped 19,658 spam messages. You can too!
One month FREE spam protection at http://www.cloudmark.com/spamnetsig/}

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Clay Luther" <cl*****@cisco.com> writes:

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);

The left join/is null thingies look like a workaround for our pre-7.4
lack of performance with NOT IN queries. Have you tried expressing
this more straightforwardly with NOT IN?

Also, what sort_mem setting are you using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

"Clay Luther" <cl*****@cisco.com> writes:

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);

The left join/is null thingies look like a workaround for our pre-7.4
lack of performance with NOT IN queries. Have you tried expressing
this more straightforwardly with NOT IN?

Also, what sort_mem setting are you using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match