From: PFC <lists@peufeu.com>
To: kevin kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving performance for a delete
Date: Tue, 20 May 2008 22:54:23 +0200
Message-ID: <op.ubg0cxr9cigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <51E11327-BBEB-4463-BB68-5C94589BF663@kevinkempterllc.com>
References: <1F6D96EA-31C8-47D0-9D72-664639C387C0@kevinkempterllc.com>
<51E11327-BBEB-4463-BB68-5C94589BF663@kevinkempterllc.com>
On Tue, 20 May 2008 22:03:30 +0200, kevin kempter
<kevin@kevinkempterllc.com> wrote:
> Version 8.3.1
>
>
> On May 20, 2008, at 1:51 PM, kevin kempter wrote:
>
>> Hi all;
>>
>> I have 2 tables where I basically want to delete from the first table
>> (seg_id_tmp7) any rows where the entire row already exists in the
>> second table (sl_cd_segment_dim)
>>
>> I have a query that looks like this (and it's slow):
>>
>>
>> delete from seg_id_tmp7
>> where
>> customer_srcid::text ||
Besides being slow as hell and not able to use any indexes, the string
concatenation can also yield incorrect results, for instance :
season_name::text || episode_srcid::text
Will have the same contents for
season_name='season 1' episode_srcid=12
season_name='season 11' episode_srcid=2
I suggest doing it the right way, one possibility being :
test=> EXPLAIN DELETE from test where (id,value) in (select id,value from
test2);
QUERY PLAN
-------------------------------------------------------------------------
Hash IN Join (cost=2943.00..6385.99 rows=2 width=6)
Hash Cond: ((test.id = test2.id) AND (test.value = test2.value))
-> Seq Scan on test (cost=0.00..1442.99 rows=99999 width=14)
-> Hash (cost=1443.00..1443.00 rows=100000 width=8)
-> Seq Scan on test2 (cost=0.00..1443.00 rows=100000 width=8)
Thanks to the hash it is very fast, one seq scan on both tables, instead
of one seq scan PER ROW in your query.
Another solution would be :
test=> EXPLAIN DELETE FROM test USING test2 WHERE test.id=test2.id AND
test.value=test2.value;
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=2943.00..6385.99 rows=2 width=6)
Hash Cond: ((test.id = test2.id) AND (test.value = test2.value))
-> Seq Scan on test (cost=0.00..1442.99 rows=99999 width=14)
-> Hash (cost=1443.00..1443.00 rows=100000 width=8)
-> Seq Scan on test2 (cost=0.00..1443.00 rows=100000 width=8)
Which chooses the same plan here, quite logically, as it is the best one
in this particular case.
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: lists@peufeu.com, kevin@kevinkempterllc.com
Subject: Re: improving performance for a delete
In-Reply-To: <op.ubg0cxr9cigqcu@apollo13.peufeu.com>
* 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