X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E2B2DD1B430 for ; Thu, 25 Dec 2003 01:51:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 27485-10 for ; Wed, 24 Dec 2003 21:50:26 -0400 (AST) Received: from millenium.mst.co.jp (unknown [210.230.185.241]) by svr1.postgresql.org (Postfix) with ESMTP id C6FEBD1B487 for ; Wed, 24 Dec 2003 21:50:21 -0400 (AST) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id hBP1dEJ31486 for ; Thu, 25 Dec 2003 10:39:14 +0900 Message-ID: <013d01c3ca89$7307d7d0$7201a8c0@mst1x5r347kymb> From: "Iain" To: Subject: not in vs not exists - vastly diferent performance Date: Thu, 25 Dec 2003 10:50:18 +0900 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0139_01C3CAD4.E2E853E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, HTML_FONT_FACE_ODD X-Spam-Level: X-Archive-Number: 200312/174 X-Sequence-Number: 16340 This is a multi-part message in MIME format. ------=_NextPart_000_0139_01C3CAD4.E2E853E0 Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: 7bit Hi All, I found this interesting and thought I'd offer it up for comment. I have the following delete: delete from tableB where id not in (select id from tableA); There are about 100,000 rows in table A, and about 500,000 in table B. id is indexed on both tables. This is just a development DB, and I wanted to clean it up so I could put in RI constraints. Somewhere along the line, records have been deleted from tableA leaving orphans in tableB. I launched the query yesterday afternoon, and it hadn't returned as of this morning, 15 hours later. Running top showed that CPU utilization was running close to 100%, and the disk was not busy at all. Anyway, I killed it and did some testing: Analyse revealed this plan, and varying random_page_cost between 1 and 4 didn't affect it: Seq Scan on tableB (cost=0.00..1003619849.56 rows=251513 width=6) Filter: (NOT (subplan)) SubPlan -> Seq Scan on tableA (cost=0.00..3738.64 rows=100664 width=4) recoding the delete to use not exists as follows: delete from tableB where not exists (select id from tableA where tableA.id = tableB.id); Gave this plan: Seq Scan on tableB (cost=0.00..719522.41 rows=236131 width=6) Filter: (NOT (subplan)) SubPlan -> Index Scan using tableB_pk on tableA(cost=0.00..3.01 rows=2 width=4) Index Cond: ((id)::integer = ($0)::integer) This deleted 1200 rows in about 2 seconds. Much better. Anyway, I was a little surprised that "not in" chose to use a seq scan on the tableA in this case. I had imagined, given that statistics were up to date and the size of the table, that the plan would have been similar to that generated by not exists, or perhaps would have used a hash table based on the tableA ids. Something to think about anyway. Regards Iain ------=_NextPart_000_0139_01C3CAD4.E2E853E0 Content-Type: text/html; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable

Hi All,

I found this interesting and thou= ght I'd=20 offer it up for comment.

I have the following delete:

delete from tableB where id not i= n (select=20 id from tableA);

There are about 100,000 rows in t= able A,=20 and about 500,000 in table B. id is indexed on both tables. This is just a= =20 development DB, and I wanted to clean it up so I could put in RI constraint= s.=20 Somewhere along the line, records have been deleted from tableA leaving orp= hans=20 in tableB.

I launched the query yesterday af= ternoon,=20 and it hadn't returned as of this morning, 15 hours later. Running top= =20 showed that CPU utilization was running close to 100%, and the disk was=20 not busy at all. Anyway, I killed it and did some testing:

Analyse revealed this plan, and v= arying=20 random_page_cost between 1 and 4 didn't affect it:

Seq Scan on tableB =20 (cost=3D0.00..1003619849.56 rows=3D251513 width=3D6)
   Filter= : (NOT=20 (subplan))
   SubPlan
     -> = Seq=20 Scan on tableA (cost=3D0.00..3738.64 rows=3D100664 width=3D4)

recoding the delete to use not ex= ists as=20 follows:

delete from tableB where not= =20 exists (select id from tableA where tableA.id =3D tableB.id);

Gave this plan:

Seq Scan on tableB =20 (cost=3D0.00..719522.41 rows=3D236131 width=3D6)
   Filter: (N= OT=20 (subplan))
   SubPlan
     -> = Index=20 Scan using tableB_pk on tableA(cost=3D0.00..3.01 rows=3D2=20 width=3D4)
           = Index=20 Cond: ((id)::integer =3D ($0)::integer)

This deleted 1200 rows in about 2= seconds.=20 Much better.

Anyway, I was a little surprised = that "not=20 in" chose to use a seq scan on the tableA in this case. I had imagined, giv= en=20 that statistics were up to date and the size of the table, that the plan wo= uld=20 have been similar to that generated by not exists, or perhaps would have us= ed a=20 hash table based on the tableA ids.

Something to think about=20 anyway.

Regards

Iain

------=_NextPart_000_0139_01C3CAD4.E2E853E0--