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
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