Re: Converting NOT IN to anti-joins during planning

On Wed, 6 Mar 2019 at 04:11, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> Hi Jim,
>
> Thanks for replying here.
>
> On Wed, 6 Mar 2019 at 16:37, Jim Finnerty <jfinnert(at)amazon(dot)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

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Solutions for the Enterprise