From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree
Date: Wed, 19 Aug 2026 01:25:22 +0000
Message-ID: <E1wwV3R-000000018cJ-1Mch@gemulon.postgresql.org> (raw)
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(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree
In-Reply-To: <E1wwV3R-000000018cJ-1Mch@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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