Re: Reduce "Var IS [NOT] NULL" quals during constant folding - Mailing list pgsql-hackers

On Fri, Mar 21, 2025 at 6:14 PM Richard Guo <guofenglinux@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