From: Steve Wampler <swampler@noao.edu>
To: Magnus Hagander <mha@sollentuna.net>
Cc: Postgres-performance <pgsql-performance@postgresql.org>
Subject: Re: Help speeding up delete
Date: Tue, 15 Nov 2005 07:18:12 -0700
Message-ID: <4379EE24.8000504@noao.edu> (raw)
In-Reply-To: <6BCB9D8A16AC4241919521715F4D8BCE6C7BC4@algol.sollentuna.se>
References: <6BCB9D8A16AC4241919521715F4D8BCE6C7BC4@algol.sollentuna.se>
Magnus Hagander wrote:
>>Because I think we need to. The above would only delete rows
>>that have name = 'obsid' and value = 'oid080505'. We need to
>>delete all rows that have the same ids as those rows.
>>However, from what you note, I bet we could do:
>>
>> DELETE FROM "tmp_table2" WHERE id IN
>> (SELECT id FROM "temp_table2" WHERE name = 'obsid' and
>>value= 'oid080505');
>>
>>However, even that seems to have a much higher cost than I'd expect:
>>
>> lab.devel.configdb=# explain delete from "tmp_table2" where id in
>> (select id from tmp_table2 where name='obsid' and
>>value = 'oid080505');
>> NOTICE: QUERY PLAN:
>>
>> Seq Scan on tmp_table2 (cost=0.00..65705177237.26
>>rows=769844 width=6)
>> SubPlan
>> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
>> -> Seq Scan on tmp_table2 (cost=0.00..42674.32
>>rows=38 width=50)
>>
>> EXPLAIN
...
>
> Earlier pg versions have always been bad at dealing with IN subqueries.
> Try rewriting it as (with fixing any broken syntax, I'm not actually
> testing this :P)
>
> DELETE FROM tmp_table2 WHERE EXISTS
> (SELECT * FROM tmp_table2 t2 WHERE t2.id=tmp_table2.id AND
> t2.name='obsid' AND t2.value='oid080505')
Thanks - that looks *significantly* better:
lab.devel.configdb=# explain delete from tmp_table2 where exists
(select 1 from tmp_table2 t2 where
t2.id=tmp_table2.id and
t2.name='obsid' and t2.value='oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..9297614.80 rows=769844 width=6)
SubPlan
-> Index Scan using inv_index_2 on tmp_table2 t2 (cost=0.00..6.02 rows=1 width=0)
EXPLAIN
(This is after putting an index on the (id,name,value) tuple.) That outer seq scan
is still annoying, but maybe this will be fast enough.
I've passed this on, along with the (strong) recommendation that they
upgrade PG.
Thanks!!
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
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: pgsql-performance@postgresql.org
Cc: swampler@noao.edu, mha@sollentuna.net
Subject: Re: Help speeding up delete
In-Reply-To: <4379EE24.8000504@noao.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox