On Tue, Jan 27, 2026 at 4:10 PM Richard Guo <[email protected]> wrote: > On Tue, Jan 27, 2026 at 11:32 AM Tender Wang <[email protected]> wrote: > > But I found that the case "x IS DISTINCT FROM NULL" is converted to > > NullTest in transformAExprDistinct(). > > It will be optimized in the "case T_NullTest:" not by this patch.
> Well, while it's true that the parser would do this transformation for
> "literal" NULLs, here we are talking more about "calculated" NULLs.
> Consider "not_null_col IS DISTINCT FROM (1 + NULL)".
BTW, this reminds me that we can teach const-folding to always
transform "x IS [NOT] DISTINCT FROM NULL" to a NullTest, even when x
cannot be proven non-nullable. (The parser have already done that for
literal NULLs.)
This is safe because we know that NullTest with !argisrow is fully
equivalent to SQL's IS [NOT] DISTINCT FROM NULL, even for rowtypes.
It is also beneficial because NullTest is much more amenable to
optimization than DistinctExpr. For example, the planner can deduce
forced-null Vars from a NullTest clause (which can be used to reduce
outer join strength), whereas it lacks such insight for a DistinctExpr.
As an example, consider:
explain (costs off)
select * from t t1 left join t t2 on t1.b = t2.b
where t2.b is not distinct from null;
QUERY PLAN
------------------------------
Hash Anti Join
Hash Cond: (t1.b = t2.b)
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
(5 rows)
explain (costs off)
select * from t t1 left join t t2 on t1.b = t2.b
where t2.b is not distinct from null::int;
QUERY PLAN
-------------------------------------------------------
Hash Left Join
Hash Cond: (t1.b = t2.b)
Filter: (NOT (t2.b IS DISTINCT FROM NULL::integer))
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
(6 rows)
Please see 0003 for the details of this transformation.
- Richard
v3-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch
Description: Binary data
v3-0002-Optimize-BooleanTest-with-non-nullable-input.patch
Description: Binary data
v3-0003-Teach-planner-to-transform-x-IS-NOT-DISTINCT-FROM.patch
Description: Binary data
