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:02:48 -0400
Message-ID: <2020235.1686783768@sss.pgh.pa.us> (raw)
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>
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 information 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 backpatchable
> 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
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: <2020235.1686783768@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