On 7/10/25 13:09, Ilia Evdokimov wrote:
The planner currently calls approx_tuple_count() to estimate
hashjointuples and mergejointuples. That makes sense when
joinrestrictinfo contains additional clauses beyond the hash/merge
equality list. But if all join restriction clauses are exactly those
hash/merge clauses, the estimate already computed in
path->jpath.path.rows is usually more accurate (and free).
This patch reuses path->jpath.path.rows in that case and skips
approx_tuple_count().
I went back and looked more closely at the twoi cases that got worse -
select_parallel.sql and updatable_views.sql - and it turns out both are
explained by the same root cause: neither query is a plain inner
join.select_parallel.sql's case is a semi join, and
updatable_views.sql's is a left/right join. In both cases
path->jpath.rows is not the same quantity that
mergejointuples/hashjointuples are supposed to present.
calc_joinrel_size_estimate() computes rows differently depending on
jointype. For JOIN_INNER it's outer_rows * inner_rows * selectivity -
exactly the quantity approx_tuple_count() tries to approximate, just
computed more accurately. So only for JOIN_INNER do
path->jpath.path.rows and "tuples passing the merge/hash quals" coincide.
The updated v2-patch restricts the substitution to path->jpath.jointype
== JOIN_INNER.
Looking forward to your feedback!
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From 74b98007437540bb3fd4b91725835eee330b7703 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Mon, 7 Sep 2026 20:33:06 +0300
Subject: [PATCH v2] Use exact join size estimate for plain inner merge/hash
joins
For a plain inner join, path->jpath.path.rows already equals the
number of tuples passing the merge/hash clauses, so
final_cost_mergejoin()/final_cost_hashjoin() can use it directly
instead of recomputing an approximation via approx_tuple_count().
For SEMI/LEFT/FULL joins this doesn't hold, so keep the substitution
JOIN_INNER-only.
---
src/backend/optimizer/path/costsize.c | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 7bbddb8bee4..d9714369374 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -4058,7 +4058,11 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path,
* Get approx # tuples passing the mergequals. We use approx_tuple_count
* here because we need an estimate done with JOIN_INNER semantics.
*/
- mergejointuples = approx_tuple_count(root, &path->jpath, mergeclauses);
+ if (path->jpath.jointype == JOIN_INNER &&
+ list_length(path->jpath.joinrestrictinfo) == list_length(mergeclauses))
+ mergejointuples = path->jpath.path.rows;
+ else
+ mergejointuples = approx_tuple_count(root, &path->jpath, mergeclauses);
/*
* When there are equal merge keys in the outer relation, the mergejoin
@@ -4711,6 +4715,8 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
hashjointuples = outer_path_rows - outer_matched_rows;
else if (path->jpath.jointype == JOIN_SEMI || extra->inner_unique)
hashjointuples = outer_matched_rows;
+ else if (path->jpath.jointype == JOIN_INNER && list_length(path->jpath.joinrestrictinfo) == list_length(hashclauses))
+ hashjointuples = path->jpath.path.rows;
else
hashjointuples = approx_tuple_count(root, &path->jpath, hashclauses);
--
2.34.1
