Re: Converting NOT IN to anti-joins during planning

Hi Jim,

Thanks for replying here.

On Wed, 6 Mar 2019 at 16:37, Jim Finnerty <jfinnert@amazon.com> wrote:
>
> Actually, we're working hard to integrate the two approaches.  I haven't had
> time since I returned to review your patch, but I understand that you were
> checking for strict predicates as part of the nullness checking criteria,
> and we definitely must have that.  Zheng tells me that he has combined your
> patch with ours, but before we put out a new patch, we're trying to figure
> out how to preserve the existing NOT IN execution plan in the case where the
> materialized subplan fits in memory.  This (good) plan is effectively an
> in-memory hash anti-join.
>
> This is tricky to do because the NOT IN Subplan to anti-join transformation
> currently happens early in the planning process, whereas the decision to
> materialize is made much later, when the best path is being converted into a
> Plan.

I guess you're still going with the OR ... IS NULL in your patch then?
otherwise, you'd likely find that the transformation (when NULLs are
not possible) is always a win since it'll allow hash anti-joins. (see
#2 in the original email on this thread)  FWIW I mentioned in [1] and
Tom confirmed in [2] that we both think hacking the join condition to
add an OR .. IS NULL is a bad idea. I guess you're not deterred by
that?