Optimize IS NOT DISTINCT FROM to equality when possible

From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Optimize IS NOT DISTINCT FROM to equality when possible
Date: 2026-11-22 12:31:16
Message-ID: tencent_9696C9D4088DF9F0F86D7FB40C1396DF3408@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

[1] pointed out that PostgreSQL's handling of IS [NOT] DISTINCT
FROM could be improved, noting that IS NOT DISTINCT FROM cannot
currently use indexes in the same way as ordinary equality
operators.

[2] implemented an optimization for IS DISTINCT FROM by
simplifying it to an equality operator when possible.

Following the approach in [2], this patch attempts to simplify
IS NOT DISTINCT FROM to an ordinary equality expression.

Specifically, if the input node is an expression of the form
"NOT (a IS DISTINCT FROM b)", and at least one of the operands
is known to be non-NULL, it can be safely converted to "a = b".

When both operands are non-NULL, the two expressions produce
the same result. When either operand is NULL, the original
expression returns FALSE, while the ordinary equality
comparison returns NULL. They therefore have the same filtering
behavior in a WHERE clause.

This enables the planner to generate better plans by utilizing
index scans, merge joins, hash joins, and equivalence-class-based
qual deduction.

I've attached a draft patch. Any feedback or suggestions on aspects
I may have overlooked would be very welcome.

References:
[1] https://www.postgresql.org/message-id/1385220.1657600906%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/flat/CAMbWs4_kMG5qVGnhOS2C%3D3nYWZ9AT8ves834WbtbALzDmEf7%3Dg%40mail.gmail.com#c79474de5f920a4d4d2140a22b83fb99

regards,
--
ZizhuanLiu (X-MAN) 
44973863(at)qq(dot)com

Attachment Content-Type Size
v1-0001-Simplify-IS-NOT-DISTINCT-FROM-to-equality-when-po.patch application/octet-stream 5.1 KB
setup_explain.sql application/octet-stream 1.3 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message Dean Rasheed 2026-11-22 11:32:31 Re: Fix stale source values in MERGE RETURNING after a cross-partition update