Re: Optimizing NOT IN plans / verify rewrite

With Oracle, I've found an anti-union (MINUS in Oracle, EXCEPT in PGSQL) to
be often a bit better than an anti-join, which is in turn faster than NOT
IN. Depends of course on row distribution and index layouts, and a bunch of
other details.

Depending on what you're returning, it can pay to make sure this computation
is done with the shortest possible rows, if necessary using a subquery.

Cheers
Dave

On Mon, Aug 2, 2010 at 2:49 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov>wrote:

> Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
>
> > No NULLs. It looks like it's a good deal slower than the LOJ
> > version, but a good deal faster than the original.
>
> On 8.4 and later the NOT EXISTS I suggested is a bit faster than
> your fast version, since Tom did some very nice work in this area,
> implementing semi join and anti join. If you've got much load with
> this kind of query, it might be worth upgrading.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>