From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Reduce outer joins to anti joins for whole-row IS NULL tests
Date: Wed, 19 Aug 2026 01:25:22 +0000
Message-ID: <E1wwV3R-000000018cN-1Zxn@gemulon.postgresql.org> (raw)
Reduce outer joins to anti joins for whole-row IS NULL tests
reduce_outer_joins() recognizes "WHERE b.z IS NULL" above an outer
join as an anti-join condition, but not the whole-row "WHERE b IS
NULL", which is a natural way to ask for an anti-join without naming a
specific column. Teach it to recognize the whole-row form too.
A row that the join null-extends has all of b's columns set to NULL,
so it satisfies "b IS NULL". A matched row satisfies the test only if
its columns happen to be all NULL. Hence proving any one column of b
non-null in matching rows rules out every matched row, leaving only
null-extended rows: exactly anti-join semantics. This mirrors the
single-column case, and the same proofs apply: a NOT NULL table
constraint, a strict join clause (for LEFT joins), or strict quals
within the relation's subtree. Because any one column suffices, the
whole-row test reduces in a strict superset of the cases a
single-column test does.
To implement this, find_forced_null_vars() now reports a whole-row Var
tested with row-format IS NULL as a varattno-zero entry meaning that
all of the relation's columns are forced null. Row-format tests on
ordinary composite-type columns remain excluded, since such a test
does not force the column null: it is also true when the column is a
non-null row whose fields are all NULL. The proof functions in
reduce_outer_joins() treat a whole-row entry accordingly: it is
refuted by proving any one column of its relation non-null. A match
on the whole-row attribute itself proves nothing, because a non-null
composite datum can still have all columns NULL, so the per-column
matching now explicitly excludes that attribute.
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/02119d6c87e20bd17f9b94b0420c2713d62275bc
Modified Files
--------------
src/backend/optimizer/prep/prepjointree.c | 101 +++++++++++++++----
src/backend/optimizer/util/clauses.c | 19 +++-
src/test/regress/expected/join.out | 156 ++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 60 ++++++++++++
4 files changed, 314 insertions(+), 22 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 outer joins to anti joins for whole-row IS NULL tests
In-Reply-To: <E1wwV3R-000000018cN-1Zxn@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