From: Scott Carey <scott@richrelevance.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: ??????? ???????? <lebiathan@gmail.com>
To: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: "SELECT .. WHERE NOT IN" query running for hours
Date: Mon, 10 Jan 2011 13:05:04 -0800
Message-ID: <C950B38E.18665%scott@richrelevance.com> (raw)
In-Reply-To: <4D2B19BE0200002500039215@gw.wicourts.gov>
On 1/10/11 12:37 PM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>Scott Carey <scott@richrelevance.com> wrote:
>
>> 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.
>
>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.
>
>-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.
>
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: scott@richrelevance.com, Kevin.Grittner@wicourts.gov, lebiathan@gmail.com, mladen.gogala@vmsinfo.com
Subject: Re: "SELECT .. WHERE NOT IN" query running for hours
In-Reply-To: <C950B38E.18665%scott@richrelevance.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