Horton

Re: NOT IN subquery optimization

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: "Li, Zheng" <zhelli@amazon.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Richard Guo <riguo@pivotal.io>, "Finnerty, Jim" <jfinnert@amazon.com>
Date: 2019-03-02T00:45:29Z
Lists: pgsql-hackers 
David Rowley <david.rowley@2ndquadrant.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.

			regards, tom lane

Commits

  1. Check we don't misoptimize a NOT IN where the subquery returns no rows.