Re: Allowing NOT IN to use ANTI joins

David Rowley <dgrowleyml@gmail.com> writes:
> I had another look at this and it appears you were right the first time, we
> need to ensure there's no NULLs on both sides of the join condition.

Ugh.  I'm back to being discouraged about the usefulness of the
optimization.

> The only other way I could imagine fixing this would be to have some other
> sort of join type that always met the join condition if the right side of
> the join had no tuples... Of course I'm not suggesting it gets implemented
> this way, I'm just otherwise out of ideas.

IIRC, we looked into implementing a true NOT IN join operator years ago.
Not only is it messy as can be, but there are patents in the area :-(.
So anything more than the most brain-dead-simple approach would be
risky.

I could see implementing a variant join operator in the hash join code,
since there you get to look at the entire inner relation before you have
to give any answers.  You could easily detect both empty-inner and
inner-contains-nulls and modify the results of matching appropriately.
However, it's not apparent how that could be made to work for either
mergejoin or nestloop-with-inner-index-scan, which greatly limits the
usefulness of the approach.  Worse yet, I think this'd be at best a
marginal improvement on the existing hashed-subplan code path.
        regards, tom lane