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 <[email protected]> Reviewed-by: Ayush Tiwari <[email protected]> Reviewed-by: Haibo Yan <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/CAMbWs49XwhSC=e8_yeeagkmknywr3dhh0p+e4k-br_pgrin...@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(-)
