Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 022F12E0031 for ; Tue, 20 May 2008 17:47:53 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49104-09 for ; Tue, 20 May 2008 17:47:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by developer.postgresql.org (Postfix) with SMTP id C07442E002C for ; Tue, 20 May 2008 17:47:49 -0300 (ADT) Received: (qmail 16710 invoked by uid 503); 20 May 2008 20:48:08 -0000 Received: from unknown (HELO mail243.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 20 May 2008 20:48:08 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 20 May 2008 20:47:42 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 20 May 2008 20:47:41 -0000 Date: Tue, 20 May 2008 22:54:23 +0200 To: "kevin kempter" Subject: Re: improving performance for a delete From: PFC Cc: pgsql-performance@postgresql.org Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <1F6D96EA-31C8-47D0-9D72-664639C387C0@kevinkempterllc.com> <51E11327-BBEB-4463-BB68-5C94589BF663@kevinkempterllc.com> Content-Transfer-Encoding: Quoted-Printable Message-ID: In-Reply-To: <51E11327-BBEB-4463-BB68-5C94589BF663@kevinkempterllc.com> User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 4101934837700036283 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.648 tagged_above=0 required=5 tests=AWL=0.648 X-Spam-Level: X-Archive-Number: 200805/278 X-Sequence-Number: 30246 On Tue, 20 May 2008 22:03:30 +0200, kevin kempter = 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=3D'season 1' episode_srcid=3D12 season_name=3D'season 11' episode_srcid=3D2 I suggest doing it the right way, one possibility being : test=3D> EXPLAIN DELETE from test where (id,value) in (select id,value f= rom = test2); QUERY PLAN ------------------------------------------------------------------------= - Hash IN Join (cost=3D2943.00..6385.99 rows=3D2 width=3D6) Hash Cond: ((test.id =3D test2.id) AND (test.value =3D test2.value))= -> Seq Scan on test (cost=3D0.00..1442.99 rows=3D99999 width=3D14)= -> Hash (cost=3D1443.00..1443.00 rows=3D100000 width=3D8) -> Seq Scan on test2 (cost=3D0.00..1443.00 rows=3D100000 wid= th=3D8) Thanks to the hash it is very fast, one seq scan on both tables, instea= d = of one seq scan PER ROW in your query. Another solution would be : test=3D> EXPLAIN DELETE FROM test USING test2 WHERE test.id=3Dtest2.id A= ND = test.value=3Dtest2.value; QUERY PLAN ------------------------------------------------------------------------= - Hash Join (cost=3D2943.00..6385.99 rows=3D2 width=3D6) Hash Cond: ((test.id =3D test2.id) AND (test.value =3D test2.value))= -> Seq Scan on test (cost=3D0.00..1442.99 rows=3D99999 width=3D14)= -> Hash (cost=3D1443.00..1443.00 rows=3D100000 width=3D8) -> Seq Scan on test2 (cost=3D0.00..1443.00 rows=3D100000 wid= th=3D8) = Which chooses the same plan here, quite logically, as it is the best on= e = in this particular case.