Re: Optimizing NOT IN plans / verify rewrite - Mailing list pgsql-performance

Dave Crooke <dcrooke@gmail.com> wrote:

> 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.

I found that assertion intriguing, so I tested the "fast" query from
the original post against my suggestion and a version using EXCEPT.
(This was against the development HEAD, not any release.)

OP "fast":  32.9 seconds
NOT EXISTS: 11.2 seconds
EXCEPT:      7.7 seconds

That last was using this query, which just might work OK on 8.3:

DELETE FROM foo
  where foo.b in (
    select b from foo WHERE type = 'o'
    except SELECT b FROM bar
    except SELECT b FROM foo where type <> 'o');

I wonder whether this could make a reasonable alternative plan for
the optmizer to consider some day....

-Kevin