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(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?

I'd say your next best move is, over on the other thread, to put up
your argument against what Tom and I mentioned, then detail out what
exactly you're planning. Likely this will save time. I personally
don't think that ignoring this part is going to allow you to progress
your patch too much further in PostgreSQL. Consensus about how $thing
works is something that's needed before the $thing can ever be
committed. Sometimes lack of objection can count, but an unaddressed
objection is not consensus. Not trying to make this hard, just trying
to explain the process.

[1] https://www.postgresql.org/message-id/CAKJS1f8q4S%2B5Z7WSRDWJd__SwqMr12JdWKXTDo35ptzneRvZnw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/5420.1551487529%40sss.pgh.pa.us

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services