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: <20230614235711.x2ffx54zi7mnt6ho@awork3.anarazel.de>
References: <17975-98a90c156f25c952@postgresql.org>
 <20230614203651.hyc4cjdzhgrg3pav@awork3.anarazel.de>
 <20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
 <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de>
 <2020235.1686783768@sss.pgh.pa.us>
 <20230614235711.x2ffx54zi7mnt6ho@awork3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Wed, 14 Jun 2023 16:57:11 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2026450.1686787166.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 14 Jun 2023 19:59:26 -0400
Message-ID: <2026451.1686787166@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2026451.1686787166%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> On 2023-06-14 19:02:48 -0400, Tom Lane wrote:
>> 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).

> It doesn't really hold at lower join levels with partial unique indexes,=
 at
> least as far as inner_unique goes. In this case we have one partial uniq=
ue
> index on b(c_id) WHERE a_id IS NOT NULL, and we have a plain index on b(=
c_id).
> inner_unique is set to true based on the partial index - but then we dec=
ide
> use the non-partial index for the index scan. That ends up returning a r=
ow
> which with a_is =3D NULL, which won't find a match in the upper join
> levels.

But how did it decide that the partial index is predOK, if there's not
a qual forcing a_id to not be null?

			regards, tom lane