Re: Using each rel as both outer and inner for JOIN_ANTI

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On 24/06/2021 12:50, Richard Guo wrote:
>> I believe if we use the smaller table 'foo' as inner side for this
>> query, we would have a cheaper plan.

> How would that work?

I think you could make it work for the hash-join case by extending
the existing mechanism for right joins: emit nothing during the main
scan, but mark hashtable entries when a match is found. Then make
a post-pass and emit hash entries that never found a match. So
basically just the inverse behavior of a right join, but with the
same state info.

Merge join could likely support "right anti join" too, though the
benefit of swapping inputs tends to be small there, so it may not be
worth doing.

Obviously there's a pretty fair amount of code to write to make this
happen.

regards, tom lane