Re: NOT IN subquery optimization

On Sat, 2 Mar 2019 at 13:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > I think you're fighting a losing battle here with adding OR quals to
> > the join condition.
>
> Yeah --- that has a nontrivial risk of making things significantly worse,
> which makes it a hard sell. I think the most reasonable bet here is
> simply to not perform the transformation if we can't prove the inner side
> NOT NULL. That's going to catch most of the useful cases anyway IMO.

Did you mean outer side NOT NULL? The OR col IS NULL was trying to
solve the outer side nullability problem when the inner side is empty.
Of course, the inner side needs to not produce NULLs either, but
that's due to the fact that if a NULL exists in the inner side then
the anti-join should not produce any records.

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