public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Wampler <[email protected]>
To: Postgres-performance <[email protected]>
Subject: Help speeding up delete
Date: Mon, 14 Nov 2005 15:07:21 -0700
Message-ID: <[email protected]> (raw)
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 -- [email protected]
The gods that smiled on your birth are now laughing out loud.
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Help speeding up delete
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox