Help speeding up delete - Mailing list pgsql-performance

From Steve Wampler
Subject Help speeding up delete
Date
Msg-id 43790A99.9050603@noao.edu
Whole thread Raw
Responses Re: Help speeding up delete  (Scott Lamb <slamb@slamb.org>)
Re: Help speeding up delete  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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@noao.edu
The gods that smiled on your birth are now laughing out loud.

pgsql-performance by date:

Previous
From: "Piccarello, James (James)"
Date:
Subject: Postgres recovery time
Next
From: Scott Lamb
Date:
Subject: Re: Help speeding up delete