pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Reduce LEFT JOIN to ANTI JOIN using NOT NULL constraints 2+ messages / 1 participants [nested] [flat]
* pgsql: Reduce LEFT JOIN to ANTI JOIN using NOT NULL constraints @ 2026-02-12 06:31 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2026-02-12 06:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Reduce LEFT JOIN to ANTI JOIN using NOT NULL constraints For a LEFT JOIN, if any var from the right-hand side (RHS) is forced to null by upper-level quals but is known to be non-null for any matching row, the only way the upper quals can be satisfied is if the join fails to match, producing a null-extended row. Thus, we can treat this left join as an anti-join. Previously, this transformation was limited to cases where the join's own quals were strict for the var forced to null by upper qual levels. This patch extends the logic to check table constraints, leveraging the NOT NULL attribute information already available thanks to the infrastructure introduced by e2debb643. If a forced-null var belongs to the RHS and is defined as NOT NULL in the schema (and is not nullable due to lower-level outer joins), we know that the left join can be reduced to an anti-join. Note that to ensure the var is not nullable by any lower-level outer joins within the current subtree, we collect the relids of base rels that are nullable within each subtree during the first pass of the reduce-outer-joins process. This allows us to verify in the second pass that a NOT NULL var is indeed safe to treat as non-nullable. Based on a proposal by Nicolas Adenis-Lamarre, but this is not the original patch. Suggested-by: Nicolas Adenis-Lamarre <nicolas.adenis.lamarre@gmail.com> Author: Tender Wang <tndrwang@gmail.com> Co-authored-by: Richard Guo <guofenglinux@gmail.com> Discussion: https://postgr.es/m/CACPGbctKMDP50PpRH09in+oWbHtZdahWSroRstLPOoSDKwoFsw@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/cf74558feb8f41b2bc459f59ed3f991024d04893 Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 204 +++++++++++++++++++++++++----- src/test/regress/expected/join.out | 62 +++++++++ src/test/regress/sql/join.sql | 27 ++++ 3 files changed, 260 insertions(+), 33 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree @ 2026-08-19 01:25 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2026-08-19 01:25 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree reduce_outer_joins() turns a LEFT JOIN into an ANTI JOIN when some Var that an upper qual requires to be NULL is actually non-nullable in any matching row. When that holds, only null-extended (unmatched) rows can satisfy the upper qual, which is exactly anti-join semantics. Until now we recognized such a Var as non-nullable only when the join's own clauses were strict for it, or when it was defined NOT NULL by table constraints. This patch allows strict quals applied within the RHS subtree to serve as the proof as well. Because such quals hold for every row the RHS emits, they hold for every matching row, so a Var they force non-null can become NULL above the join only by null-extension. To avoid re-walking the jointree at decision time, the first pass of the reduce-outer-joins process gathers these proving quals into its per-subtree state, alongside nullable_rels. The second pass then proves non-nullness from the RHS subtree's collected quals together with the join's own ON quals. As before, the reduction fires only when the proven, forced-null Var belongs to the RHS of the join. Author: Richard Guo <guofenglinux@gmail.com> Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com> Discussion: https://postgr.es/m/CAMbWs49H9khf+1GwyzD0TYaks6cE-OU+1mbUiOn3gZSoBiO7zg@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/a812cf647a828fc6157ca1f864f74067f088b580 Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 93 +++++++++++---- src/test/regress/expected/join.out | 189 +++++++++++++++++++++++++++++- src/test/regress/sql/join.sql | 61 +++++++++- 3 files changed, 319 insertions(+), 24 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-08-19 01:25 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-12 06:31 pgsql: Reduce LEFT JOIN to ANTI JOIN using NOT NULL constraints Richard Guo <rguo@postgresql.org> 2026-08-19 01:25 pgsql: Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree Richard Guo <rguo@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox