Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 020A7B5DBC6 for ; Sun, 24 Jul 2011 13:02:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 04128-03 for ; Sun, 24 Jul 2011 16:02:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id DAFCAB5D803 for ; Sun, 24 Jul 2011 13:02:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p6OG2OBr012709; Sun, 24 Jul 2011 12:02:24 -0400 (EDT) To: =?KOI8-R?B?5M3J1NLJyiD3wdPJzNjF1w==?= cc: pgsql-performance@postgresql.org Subject: Re: Bad query plan In-reply-to: References: Comments: In-reply-to =?KOI8-R?B?5M3J1NLJyiD3wdPJzNjF1w==?= message dated "Sun, 24 Jul 2011 18:06:40 +0400" Date: Sun, 24 Jul 2011 12:02:24 -0400 Message-ID: <12708.1311523344@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.093 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.193 X-Spam-Level: X-Archive-Number: 201107/208 X-Sequence-Number: 44420 =?KOI8-R?B?5M3J1NLJyiD3wdPJzNjF1w==?= writes: > explain analyze insert into large(id) select id from small where id > not in(select id from large); > [ crummy plan ] > explain analyze insert into large(id) select id from small where not > exists (select id from large l where small.id=l.id); > [ better plan ] > Both queries are semantically the same. No, they are not. NOT IN is hard to optimize because it has strange behaviors with nulls in the data. Use the NOT EXISTS formulation. regards, tom lane