From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix CPU cost of right-semi and right-anti hash joins
Date: Tue, 25 Aug 2026 01:50:15 +0000
Message-ID: <E1wygIo-00000001zMG-2ykp@gemulon.postgresql.org> (raw)
Fix CPU cost of right-semi and right-anti hash joins
final_cost_hashjoin() assumed that the rows a hash join produces come
from the outer side. That does not hold for JOIN_RIGHT_SEMI and
JOIN_RIGHT_ANTI, which produce rows from the inner side: matched inner
rows for the one, unmatched inner rows for the other. hashjointuples,
which carries both the cpu_tuple_cost charge and the remaining qual
costs, was therefore counting the wrong rows, and could be far off in
either direction.
Compute hashjointuples from the inner side for these two join types,
mirroring what JOIN_SEMI and JOIN_ANTI already do with the outer side.
The fraction we need is semifactors.outer_match_frac: it is derived
from the SpecialJoinInfo, so despite its name it always describes the
semijoin's left-hand side, which is the inner side here. Compute the
semijoin factors for these join types in all cases; previously that
happened only when the inner side was provably unique.
The same mix-up affects outer_matched_rows when the inner side is
known unique, where the outer row count was multiplied by the inner
side's match fraction. A unique inner side means each outer row has
at most one match, so use the number of matching pairs instead.
A right anti join evaluates the non-hashed joinquals once per tuple
passing the hash clauses, without any short-circuit. Charge the
remaining qual costs on that pair count, and only cpu_tuple_cost on
the emitted rows. A right semi join short-circuits already-matched
inner tuples and keeps the emitted-row charge, as JOIN_SEMI does.
Nestloop and mergejoin need no equivalent fix: neither supports
JOIN_RIGHT_SEMI, nestloop doesn't support JOIN_RIGHT_ANTI either, and
final_cost_mergejoin() takes its count from approx_tuple_count(),
which multiplies the two input sizes together and so gives the same
answer whichever side is on the outside.
No backpatch as this could result in plan changes.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Reviewed-by: Haibo Yan <tristan.yim@gmail.com>
Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>
Discussion: https://postgr.es/m/CAMbWs49XwhSC=e8_yeEaGKmKNyWR3DHH0p+e4k-bR_pgRiN8nQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/db2d99323f1882af87989e698d3678affc4a5557
Modified Files
--------------
src/backend/optimizer/path/costsize.c | 183 +++++++++++++++++++-------
src/backend/optimizer/path/joinpath.c | 9 +-
src/include/nodes/pathnodes.h | 32 +++--
src/test/regress/expected/join.out | 100 ++++++++++++--
src/test/regress/expected/opr_sanity.out | 14 +-
src/test/regress/expected/select_parallel.out | 34 ++---
src/test/regress/sql/join.sql | 42 ++++++
7 files changed, 311 insertions(+), 103 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: Fix CPU cost of right-semi and right-anti hash joins
In-Reply-To: <E1wygIo-00000001zMG-2ykp@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