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-03T22:06:05Z
Lists: pgsql-hackers 
David Rowley <david.rowley@2ndquadrant.com> writes:
> On Mon, 4 Mar 2019 at 04:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You absolutely will get errors during btree insertions and searches
>> if a datatype's btree comparison functions ever return NULL (for
>> non-NULL inputs).

> I understand this is the case if an index happens to be used, but
> there's no guarantee that's going to be the case. I was looking at the
> case where an index was not used.

Not following your point?  An index opclass is surely not going to be
designed on the assumption that it can never be used in an index.
Therefore, its support functions can't return NULL unless the index AM
allows that.

			regards, tom lane

Commits

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