> I reviewed v1 of this patch. The approach looks sound to me. > > state->nullable_rels records whether a relation can be null-extended > within the subtree in general. If find_nonnullable_rels(all_quals) > proves that the relation cannot be an all-NULL row among the rows of > interest, then it must represent a real base-table row there, so its > table-level NOT NULL constraints can be used again. Hi Ziming,
Thanks for taking the time to review and test this, and sorry for the slow reply -- I was away for a few weeks. Your summary is exactly the argument: nullable_rels answers "could this rel ever be null-extended in the subtree?", while find_nonnullable_rels over the collected quals answers the narrower question "could it be null-extended in a row that survives these quals?". Only the second question matters for the Vars we are trying to prove non-null, so the NOT NULL constraints can be trusted again for rels where the answer is no. > I tested the patch with an --enable-debug --enable-cassert build. The > core regression tests passed, and git diff --check reports no problems. > > I also compared patched and unpatched builds with a few additional > cases. The strict cases were reduced to Anti Join with unchanged > results, while a non-strict condition that allowed a lower > null-extended row to pass correctly retained the outer join. I also > checked the FULL JOIN case and did not find a result difference. Glad the non-strict and FULL JOIN cases held up on your side as well; those were the ones I was least sure I had covered. I just re-checked v1 against current master (36a56229765) and it still applies cleanly, so I am not posting a new version for now. Richard's recent changes in this area (ad4b80b6f49 and e28cf96e935) are both already included in the base I tested on. Thanks again, Zhenglong Li Ziming Zhang <[email protected]> 于2026年9月21日周一 00:16写道： > > Hi Zhenglong, > > I reviewed v1 of this patch. The approach looks sound to me. > > state->nullable_rels records whether a relation can be null-extended > within the subtree in general. If find_nonnullable_rels(all_quals) > proves that the relation cannot be an all-NULL row among the rows of > interest, then it must represent a real base-table row there, so its > table-level NOT NULL constraints can be used again. > > The new proof only considers the quals already collected by > forced_null_var_is_nonnullable(), so it does not appear to weaken the > existing safety rules for outer-join reduction. > > I tested the patch with an --enable-debug --enable-cassert build. The > core regression tests passed, and git diff --check reports no problems. > > I also compared patched and unpatched builds with a few additional > cases. The strict cases were reduced to Anti Join with unchanged > results, while a non-strict condition that allowed a lower > null-extended row to pass correctly retained the outer join. I also > checked the FULL JOIN case and did not find a result difference. > > I did not find any correctness or implementation issues. The patch > looks good to me. > > Regards, > Ziming > > ________________________________________ > From: Keyerror Smart <[email protected]> > Sent: Tuesday, September 8, 2026 17:46 > To: PostgreSQL Hackers > Subject: [PATCH] Combine qual-based and NOT NULL proofs when reducing > outer joins > > Hi hackers, > > reduce_outer_joins() can convert a LEFT or FULL join to an anti-join > when an upper qual forces a Var from the nullable side to be NULL while > that Var is provably non-null in every row of interest. There are two > proof methods: quals that hold for all such rows (the subtree's safe > quals, plus the join's own ON quals in the LEFT case) can prove specific > Vars non-null, and a NOT NULL table constraint can prove a Var non-null > unless its rel can be nulled by lower-level outer joins within the > subtree. > > Previously the two methods did not combine: once a rel was found > nullable within the subtree, its NOT NULL constraints were ignored > outright. But if the collected quals are strict for the rel, it cannot > have been null-extended in any row of interest, so its NOT NULL > constraints do apply to those rows after all. Compute > find_nonnullable_rels() over the quals forced_null_var_is_nonnullable() > already collects, and have forced_null_var_is_attnotnull() trust NOT > NULL constraints of rels so proven. > > For example, with Q.ts declared NOT NULL, this query's outer join can > now be reduced to an anti-join: the upper ON clause Q.f = 1 is strict > for Q, so Q cannot be null-extended in any matching row, leaving the > upper join's own null-extension as the only way to satisfy the WHERE > clause. > > SELECT * FROM R LEFT JOIN (S LEFT JOIN Q ON S.c = Q.e) > ON R.a = S.c AND Q.f = 1 > WHERE Q.ts IS NULL; > > The same reasoning applies at a FULL join, where safe quals within one > input can prove a rel non-extended in every row that input emits. > > Patch atrtached. > > Regards, > Zhenglong Li > >
