We've got an older system in production (PG 7.2.4). Recently
one of the users has wanted to implement a selective delete,
but is finding that the time it appears to take exceeds her
patience factor by several orders of magnitude. Here's
a synopsis of her report. It appears that the "WHERE
id IN ..." is resulting in a seq scan that is causing
the problem, but we're not SQL expert enough to know
what to do about it.
Can someone point out what we're doing wrong, or how we
could get a (much) faster delete? Thanks!
Report:
============================================================
This command yields results in only a few seconds:
# SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
However, the following command does not seen to want to ever
complete (the person running this killed it after 1/2 hour).
# DELETE FROM "tmp_table2" WHERE id IN
# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
==============================================================
The table has four columns. There are 6175 rows satifying the condition
given, and the table itself has 1539688 entries. Layout is:
lab.devel.configdb=# \d tmp_table2
Table "tmp_table2"
Column | Type | Modifiers
--------+--------------------------+-----------
id | character varying(64) |
name | character varying(64) |
units | character varying(32) |
value | text |
time | timestamp with time zone |
==============================================================
lab.devel.configdb=# EXPLAIN DELETE FROM "tmp_table2" WHERE id IN
lab.devel.configdb-# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..154893452082.10 rows=769844 width=6)
SubPlan
-> Materialize (cost=100600.52..100600.52 rows=296330 width=100)
-> Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
lab.devel.configdb=# EXPLAIN (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
--
Steve Wampler -- swampler@xxxxxxxx
The gods that smiled on your birth are now laughing out loud.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
