Re: NOT IN subquery optimization

On Mon, 4 Mar 2019 at 11:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > On Mon, 4 Mar 2019 at 04:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

I agree that it makes sense that the behaviour of the two match. I was
trying to hint towards that when I said:

> If you're saying something doing that is
> fundamentally broken, then I guess we're okay.

but likely I didn't make that very clear.

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