Re: NOT IN subquery optimization

Thanks all for the feedbacks! I'm working on a refined patch.

Although adding "or var is NULL" to the anti join condition forces the planner to choose nested loop anti join, it is always faster compared to the original plan. In order to enable the transformation from NOT IN to anti join when the inner/outer is nullable, we have to add some NULL test to the join condition.

We could make anti join t1, t2 on (t1.x = t2.y or t2.y IS NULL) eligible for hashjoin, it would require changes in allowing this special join quals for hash join as well as changes in hash join executor to handle NULL accordingly for the case.

Another option of transformation is to add "is not false" on top of the join condition.

Regards,
Zheng
On 3/1/19, 5:28 PM, "David Rowley" <david(dot)rowley(at)2ndquadrant(dot)com> wrote:

On Sat, 2 Mar 2019 at 05:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > I've not checked, but could we please make sure these cases are covered
> > in the regression tests today with a single liner?
>
> I'm not sure if the second one is actually a semantics bug or just a
> misoptimization? But yeah, +1 for putting in some simple tests for
> corner cases right now. Anyone want to propose a specific patch?

The second is just reducing the planner's flexibility to produce a
good plan. The first is a bug. Proposed regression test attached.

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