From: Andres Freund <andres@anarazel.de>
To: tel@jklm.no, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
To: 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 15:49:28 -0700
Message-ID: <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de> (raw)
In-Reply-To: <20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
References: <17975-98a90c156f25c952@postgresql.org>
<20230614203651.hyc4cjdzhgrg3pav@awork3.anarazel.de>
<20230614211231.pudg2y2rz6az2vij@awork3.anarazel.de>
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
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: andres@anarazel.de, tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, pg@bowt.ie
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
In-Reply-To: <20230614224928.l255k2okc3ms3iph@awork3.anarazel.de>
* 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