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

On Thu, Jun 24, 2021 at 10:14 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>

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
Richard