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?
Surely we want both?
1. Transform when we can
2. Else apply some other approach if the cost can be reduced by doing it