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>
Cc: Peter Geoghegan <pg@bowt.ie>
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
Date: Wed, 14 Jun 2023 19:59:26 -0400
Message-ID: <2026451.1686787166@sss.pgh.pa.us> (raw)
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>
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 unique
> 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 decide
> use the non-partial index for the index scan. That ends up returning a row
> which with a_is = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, dgrowleyml@gmail.com, pg@bowt.ie
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
In-Reply-To: <2026451.1686787166@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox