pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: tel@jklm.no, pgsql-bugs@lists.postgresql.org, Peter Geoghegan <pg@bowt.ie>
Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
Date: Wed, 14 Jun 2023 20:28:20 -0400
Message-ID: <2029337.1686788900@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvrewBui0hCXSEaR=X4Tpyo3hRZu6kTRuf4r4kGEA3LC9g@mail.gmail.com>
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>
	<2026451.1686787166@sss.pgh.pa.us>
	<CAApHDvrewBui0hCXSEaR=X4Tpyo3hRZu6kTRuf4r4kGEA3LC9g@mail.gmail.com>

David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 15 Jun 2023 at 11:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But how did it decide that the partial index is predOK, if there's not
>> a qual forcing a_id to not be null?

> In this case a.id = b.a_id AND c.id = b.c_id are used as proofs. I
> didn't debug all the way, but I assume we deduce that the NOT NULL
> index is ok due to the strict join quals.

Ah.  So the index is okay to use as far as the final result is
concerned, but it's not okay for the purpose to which it's being put.

I concur that we'd better just not use partial indexes in
relation_has_unique_index_for.

			regards, tom lane





view thread (18+ messages)  latest in thread

Message-ID: <2029337.1686788900@sss.pgh.pa.us>
Permalink:  ../2029337.1686788900@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2029337.1686788900@sss.pgh.pa.us

 · 

reply
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, dgrowleyml@gmail.com, andres@anarazel.de, pg@bowt.ie
  Subject: Re: BUG #17975: Nested Loop Index Scan returning wrong result
  In-Reply-To: <2029337.1686788900@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