pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Burd <greg@burd.me>
To: pgsql-bugs@postgresql.org
Cc: zhong950419@gmail.com
Cc: rahul@rhyadav.dev
Cc: rpsalmi@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Cc: guofenglinux@gmail.com
Subject: Re: BUG #17545: Incorrect selectivity for IS NOT DISTINCT FROM and NULLs
Date: Mon, 26 Oct 2026 13:18:07 +0000
Message-ID: <at9R47rsFaDzBVFp@floki> (raw)

On Mon, 5 Oct 2026 14:38:05 -0400, shihao zhong <zhong950419@gmail.com> wrote:
> Hi Rahul,
> 
> https://commitfest.postgresql.org/patch/7396/
> 
> Thanks,
> Shihao

Hey Shihao,

First off, thanks for working on this.  This issue caught my attention,
so I measured the selectivity error this fixes in an effort to quantify
the size of it, and it turns out to be large in the join case.

I tested on master at the patch's merge-base (126976a8ffc) and
master+v2.  For a join on t1.a IS NOT DISTINCT FROM t2.a, where both
columns carry NULLs, the estimate must count NULL-NULL pairs as matching
(NULL IS NOT DISTINCT FROM NULL is true).  So I had a solver run a
differential selectivity check over 120 randomized two-table joins with
varied NULL fractions and distinct counts, estimate vs actual rows:

   master:   worst case off by 4915x
   master+v2: worst case 1.0x (exact on every case I generated)

The ScalarArrayOp sibling the patch also touches, col <> ALL (ARRAY[..
NULL]), estimates within 1.5x of actual on the patched build.

One note on reproducing it: the error is specific to the join path.  My
first attempt used IS NOT DISTINCT FROM as a scalar filter (WHERE a IS
NOT DISTINCT FROM <const>), where master already estimates well, so a
scalar-only test would wrongly suggest nothing was wrong.  The patch's
planner_est.sql exercises the join form, which is where the 4915x comes
from.

+1 for the patch.

best.

-greg







view thread (9+ messages)

Message-ID: <at9R47rsFaDzBVFp@floki>
Permalink:  ../at9R47rsFaDzBVFp@floki/
Also on:    postgresql.org/message-id/at9R47rsFaDzBVFp@floki

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: greg@burd.me, zhong950419@gmail.com, rahul@rhyadav.dev, rpsalmi@gmail.com, guofenglinux@gmail.com
  Subject: Re: BUG #17545: Incorrect selectivity for IS NOT DISTINCT FROM and NULLs
  In-Reply-To: <at9R47rsFaDzBVFp@floki>
  References: <at9R47rsFaDzBVFp@floki>

* 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