Optimize IS DISTINCT FROM with non-nullable inputs

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

Browse pgsql-hackers by date

  From Date Subject
Previous Message tushar 2026-01-23 13:36:47 Re: Non-text mode for pg_dumpall