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
