From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: tel@jklm.no, pgsql-bugs@lists.postgresql.org,
        David Rowley <dgrowleyml@gmail.com>, Peter Geoghegan <pg@bowt.ie>
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
In-reply-to: <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de>
References: <17975-98a90c156f25c952@postgresql.org>
 <20230614203651.hyc4cjdzhgrg3pav@awork3.anarazel.de>
 <20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
 <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Wed, 14 Jun 2023 15:49:28 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2020234.1686783768.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 14 Jun 2023 19:02:48 -0400
Message-ID: <2020235.1686783768@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2020235.1686783768%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> On 2023-06-14 14:12:31 -0700, Andres Freund wrote:
>> We either need to force the index that we got the uniqueness informatio=
n to be
>> used when it is partial, or add the quals from the partial unique index=
 to all
>> other index scans.

I did not study this example yet, but generally we ignore predicate
indexes unless their predicates can be proven from base restrictions
of their table (that's what predOK means).  If the base restrictions
are enforced at scan level, which they should be, then uniqueness
should hold at any join level regardless of whether we actually
scanned with that index or some other way.  Maybe we broke that
chain of reasoning somehow?

> It's not immediately obvious to me how to nicely fix this in a backpatch=
able
> way. An easy fix would be to not allow predicate indexes at all anymore =
in
> relation_has_unique_index_for(), but that's a pretty big cannon - fixes =
the
> issue though.

Yeah, that would be the easy way out if we don't find a better answer.
But right at the moment I don't understand why this is failing.

			regards, tom lane