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