| From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
|---|---|
| To: | Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Optimize IS DISTINCT FROM with non-nullable inputs |
| Date: | 2026-01-23 13:40:01 |
| Message-ID: | CAMbWs49BMAOWvkdSHxpUDnniqJcEcGq3_8dd_5wTR4xrQY8urA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Unlike ordinary comparison operators, the IS [NOT] DISTINCT FROM
predicate treats NULL as a normal data value rather than "unknown".
For non-null inputs, its semantics are identical to standard
operators: IS DISTINCT FROM is equivalent to <>, and IS NOT DISTINCT
FROM is equivalent to =.
Currently, the planner simplifies DistinctExpr only if all inputs are
constants. I'm thinking that maybe we can optimize cases where inputs
are non-constant but proven to be non-nullable, by converting "x IS
DISTINCT FROM y" to "x <> y". This representation exposes the
comparison to the planner as a standard operator. If the clause is
negated (e.g. IS NOT DISTINCT FROM), the resulting "=" operator can
allow the planner to use index scans, merge joins, hash joins, and
EC-based qual deductions.
Attached is a draft patch for this optimization.
I'm kind of concerned about whether there are edge cases where this
transformation is not safe, specifically regarding "rowtype" inputs.
Any feedback would be appreciated.
- Richard
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch | application/octet-stream | 7.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | tushar | 2026-01-23 13:36:47 | Re: Non-text mode for pg_dumpall |