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
v1-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch
Description: Binary data
