Horton

Re: NOT IN subquery optimization

Richard Guo <riguo@pivotal.io>

View thread View on postgr.es
From: Richard Guo <riguo@pivotal.io>
To: "Li, Zheng" <zhelli@amazon.com>
Cc: "Finnerty, Jim" <jfinnert@amazon.com>, David Rowley <david.rowley@2ndquadrant.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-03-01T02:27:04Z
Lists: pgsql-hackers 
On Tue, Feb 26, 2019 at 6:51 AM Li, Zheng <zhelli@amazon.com> wrote:

> Resend the patch with a whitespace removed so that "git apply patch" works
> directly.
>
>

Hi Zheng,

I have reviewed your patch. Good job except two issues I can find:

1. The patch would give wrong results when the inner side is empty. In this
case, the whole data from outer side should be in the outputs. But with the
patch, we will lose the NULLs from outer side.

2. Because of the new added predicate 'OR (var is NULL)', we cannot use hash
join or merge join to do the ANTI JOIN.  Nested loop becomes the only
choice,
which is low-efficency.

Thanks
Richard

Commits

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