Re: BUG #17975: Nested Loop Index Scan returning wrong result

Andres Freund <andres(at)anarazel(dot)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