Re: Allowing NOT IN to use ANTI joins

On Monday, June 9, 2014, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

But all hash join methods needs to examine the entire *outer* input, no?
Have I screwed up my terminology here?

If you are using NOT IN, then once you find a NULL in the outer input (if
the outer input is the in-list: clearly you can't reverse the two inputs in
this case), you don't even need to finish reading the outer input, nor
start reading the inner input, because all rows are automatically excluded
by the weird semantics of NOT IN.

Cheers,

Jeff