Horton

Re: NOT IN subquery optimization

David Rowley <david.rowley@2ndquadrant.com>

View thread View on postgr.es
From: David Rowley <david.rowley@2ndquadrant.com>
To: "Li, Zheng" <zhelli@amazon.com>
Cc: "Finnerty, Jim" <jfinnert@amazon.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-02-27T00:24:26Z
Lists: pgsql-hackers 
On Wed, 27 Feb 2019 at 13:05, Li, Zheng <zhelli@amazon.com> wrote:
> -With the latest fix (for the empty table case), our patch does the transformation as long as the outer is non-nullable regardless of the inner nullability, experiments show that the results are always faster.

Hi Zheng,

I'm interested to know how this works without testing for inner
nullability.  If any of the inner side's join exprs are NULL then no
records can match. What do you propose to work around that?

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

Commits

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