Re: BUG #17545: Incorrect selectivity for IS NOT DISTINCT FROM and NULLs

On Mon, 5 Oct 2026 14:38:05 -0400, shihao zhong <zhong950419(at)gmail(dot)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