> 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'.
I applied the patch and executed some queries. Hash Right Anti Joins
seem to be working correctly. Though, some of the tests are failing.
I guessed it's because the other join algorithms do not support right
anti join, but I couldn't reproduce it.
I am impressed by how simple the patch is: only 2 lines to support a
new join algorithm. This is a good case for the quality of Postgres
code. I hope supporting the other join algorithms would be similar.
I am not sure how the cost estimation should differ from straight anti
join. It seems to me that the planner is already making the right
choice by taking into account the cost of the Hash node which makes
the whole cost greater when the inner table is much bigger.
I am not an expert planner, but it feels to me like a good feature
that can provide better plans in some cases. Given it works correctly
and the implementation is so simple, the only argument against it may
be increased planning time. I know that the planner performance is
affected negatively by the number of join paths to consider. This may
not be a bigger deal as typically there are not many anti joins in a
query, but it'd still be a good idea to do some performance tests.