Re: Allowing NOT IN to use ANTI joins

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Sun, Jun 8, 2014 at 5:36 AM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> 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);

> I think this will be great, I've run into this problem often from
> applications I have no control over. I thought a more complete, but
> probably much harder, solution would be to add some metadata to the hash
> anti-join infrastructure that tells it "If you find any nulls in the outer
> scan, stop running without returning any rows". I think that should work
> because the outer rel already has to run completely before any rows can be
> returned.

Huh? The point of an antijoin (or indeed most join methods) is that we
*don't* have to examine the whole inner input to make a decision.

regards, tom lane