Re: Improve Hash/Merge Join estimate accuracy when all predicates are Hash/Merge clauses

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/