Feedback-ID: id4a34324:Fastmail
Date: Wed, 14 Jun 2023 15:49:28 -0700
From: Andres Freund <andres@anarazel.de>
To: tel@jklm.no, pgsql-bugs@lists.postgresql.org,
	Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <dgrowleyml@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
Message-ID: <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de>
References: <17975-98a90c156f25c952@postgresql.org>
 <20230614203651.hyc4cjdzhgrg3pav@awork3.anarazel.de>
 <20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
Archived-At: 
 <https://www.postgresql.org/message-id/20230614224928.l255k2okc3ms3iph%40awork3.anarazel.de>
Precedence: bulk

Hi,

On 2023-06-14 14:12:31 -0700, Andres Freund wrote:
> I think it's a problem with the uniqueness determination / missing a
> qual / index selection.
> 
> There are two rows in b with b.c_id = 13880, except that one of them has a
> NULL a_id:
> 
> => SELECT * FROM b WHERE c_id = 13880;
> ┌────────┬───────┬────────┐
> │   id   │ c_id  │  a_id  │
> ├────────┼───────┼────────┤
> │ 326048 │ 13880 │ (null) │
> │ 572151 │ 13880 │ 955968 │
> └────────┴───────┴────────┘
> (2 rows)
> 
> . The uniqueness information comes from:
>   "index_a_cannot_share_c" UNIQUE, btree (c_id) WHERE a_id IS NOT NULL
> 
> But note that we aren't using that index, we use
>   "index_b_c_id" btree (c_id)
> 
> which of course also contains the a_id = NULL row.
> 
> 
> 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 suspect this is an issue going back to 9c7f5229. Indeed, 9.6 doesn't
reproduce the issue (9c7f5229 was in 10). I haven't bisected it down to that,
but it seems pretty likely - and if it's not that commit, it's a closely
related one.

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.

Greetings,

Andres Freund