Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLLz8-00000001ShF-0FBu for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 14:47:38 +0000 Received: from localhost ([127.0.0.1]:47390 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLLz7-00000005urI-1jka for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 14:47:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:58734) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLKaf-00000004xGS-3al4 for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:18:17 +0000 Received: from mail-24421.protonmail.ch ([109.224.244.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLKac-000000013tw-48WG for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:18:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=burd.me; s=protonmail; t=1793020692; x=1793279892; bh=QfRjNixy4NjhywT2fsNLdbbGZnFZ86ijif5DgKhtZvY=; h=Date:To:From:Cc:Subject:Message-ID:Feedback-ID:From:To:Cc:Date: Subject:Reply-To:Feedback-ID:Message-ID:BIMI-Selector; b=fjWZuScf5bNcvz3PuvlS/9AgBnD9bf+mUErgNUz2Ii6j4pffMwNEwffd9b5/Zr579 XklMGQxeoUdjlS6ptRfiPWOB7RO3eA3llvZgsapE/FvzxRQgvWBCYrRqNnjRULCg3G QWiACB2DLFyegUI6Bff8fTYwGdWbdbdYxY4evnwOLWms2fC0bo2zdtwH9uMUut95Oa 3oHtTHIM79dUUbZ9TX2v0X2PVztr67dsy56PEox+gjNxCqs9sRpuD3NJH8wcjb3KuT RHb/fYwfKCaAuiua8+yp5G1QWsN91nIvdqSKOxut2zSCvK4FuKBoXc3EimnD9JkJUa ViXYfBQhOagBg== Date: Mon, 26 Oct 2026 13:18:07 +0000 To: pgsql-bugs@postgresql.org From: Greg Burd Cc: zhong950419@gmail.com, rahul@rhyadav.dev, rpsalmi@gmail.com, pgsql-bugs@lists.postgresql.org, guofenglinux@gmail.com Subject: Re: BUG #17545: Incorrect selectivity for IS NOT DISTINCT FROM and NULLs Message-ID: Feedback-ID: 2668248:user:proton X-Pm-Message-ID: 47e8f837a539ba96d8a5654bd3d3dda72e16d714 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 5 Oct 2026 14:38:05 -0400, shihao zhong wro= te: > Hi Rahul, >=20 > https://commitfest.postgresql.org/patch/7396/ >=20 > 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 ), 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