Re: Optimizing NOT IN plans / verify rewrite

Dave Crooke  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