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

Heikki Linnakangas <hlinnaka@iki.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.

Thanks for the explanation. Attached is a demo code for the hash-join
case, which is only for PoC to show how we can make it work. It's far
from complete, at least we need to adjust the cost calculation for this
'right anti join'.

Am I going in the right direction?

Thanks