Dave Crooke <dcrooke(at)gmail(dot)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