[PATCH] Combine qual-based and NOT NULL proofs when reducing outer joins
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253722psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 01:22 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253722_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253722_1 && git checkout t253722_1Patchset v1 (message #1) is on t253722_1
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
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 <smartkeyerror@gmail.com>
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