Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B4D3C1337BB9 for ; Mon, 10 Jan 2011 17:03:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 04951-08 for ; Mon, 10 Jan 2011 21:03:01 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-1.exch018.msoutlookonline.net (exhub018-1.exch018.msoutlookonline.net [64.78.17.16]) by mail.postgresql.org (Postfix) with ESMTP id 76D091337B99 for ; Mon, 10 Jan 2011 17:03:01 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-1.exch018.msoutlookonline.net ([64.78.17.16]) with mapi; Mon, 10 Jan 2011 13:03:00 -0800 From: Scott Carey To: Kevin Grittner , ??????? ???????? , Mladen Gogala CC: "pgsql-performance@postgresql.org" Date: Mon, 10 Jan 2011 13:05:04 -0800 Subject: Re: "SELECT .. WHERE NOT IN" query running for hours Thread-Topic: [PERFORM] "SELECT .. WHERE NOT IN" query running for hours Thread-Index: AcuxCcPgo06kj7JMQ0m/luHPmeo3dw== Message-ID: In-Reply-To: <4D2B19BE0200002500039215@gw.wicourts.gov> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-MacOutlook/14.2.0.101115 acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201101/59 X-Sequence-Number: 41846 On 1/10/11 12:37 PM, "Kevin Grittner" wrote: >Scott Carey wrote: >=20 >> Often, the best query plans result from 'LEFT JOIN WHERE right >> side is NULL' rather than NOT EXISTS however. I often get >> performance gains by switching NOT EXISTS queries to LEFT JOIN >> form. >=20 >Even in 8.4 and later? I would think that the anti-join that Tom >added in 8.4 would always perform at least as well as the LEFT JOIN >technique you describe. >=20 >-Kevin Yes, in 8.4. The query planner definitely does not treat the two as equivalent. I don't have a concrete example at hand right now, but I've been working exclusively on 8.4 since a month after it was released. It does often use an anti-join for NOT EXISTS, but does not seem to explore all avenues there. Or perhaps the statistics it has differ for some reason at that point. All I know, is that the resulting query plan differs sometimes and I'd say 3 out of 4 times the LEFT JOIN variant is more optimal when they differ. >