On Fri, Mar 21, 2025 at 6:14 PM Richard Guo <guofengli...@gmail.com> wrote: > I'm wondering whether we can collect that information while building > the RangeTblEntry for a base or other relation, so that it's available > before constant folding. This could also enable other optimizations, > such as checking if a NOT IN subquery's output columns and its > left-hand expressions are all certainly not NULL, in which case we can > convert it to an anti-join. > > Attached is a draft patch to reduce NullTest on a NOT NULL column in > eval_const_expressions.
FWIW, reducing "Var IS [NOT] NULL" quals during constant folding can somewhat influence the decision on join ordering later. For instance, create table t (a int not null, b int); select * from t t1 left join (t t2 left join t t3 on t2.a is not null) on t1.b = t2.b; For this query, "t2.a is not null" is reduced to true during constant folding and then ignored, which leads to us being unable to commute t1/t2 join with t2/t3 join. OTOH, constant-folding NullTest for Vars may enable join orders that were previously impossible. For instance, select * from t t1 left join (t t2 left join t t3 on t2.a is null or t2.b = t3.b) on t1.b = t2.b; Previously the t2/t3 join's clause is not strict for t2 due to the IS NULL qual, which prevents t2/t3 join from commuting with t1/t2 join. Now, the IS NULL qual is removed during constant folding, allowing us to generate a plan with the join order (t1/t2)/t3. Not quite sure if this is something we need to worry about. Thanks Richard
