Re: Allowing NOT IN to use ANTI joins

Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs.

A simple example of this (without a subquery) is:

select 1 where 3 not in (1, 2, null); returns 0 rows because 3 <> NULL is unknown.

The attached patch allows an ANTI-join plan to be generated in cases like:

CREATE TABLE a (id INT PRIMARY KEY, b_id INT NOT NULL);

CREATE TABLE b (id INT NOT NULL);

SELECT * FROM a WHERE b_id NOT IN(SELECT id FROM b);

To generate a plan like:

                           QUERY PLAN

-----------------------------------------------------------------

 Hash Anti Join  (cost=64.00..137.13 rows=1070 width=8)

   Hash Cond: (a.b_id = b.id)

   ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)

   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)

         ->  Seq Scan on b  (cost=0.00..34.00 rows=2400 width=4)