Re: [PATCH 0/2] Fix two hash join costing errors in final_cost_hashjoin()

On 11/11/2026 13:12, He Kuang wrote:
> 0001: Multi-column hash keys estimate the bucket size from the largest
> single-column ndistinct (the minimum over per-clause fractions), while
> the output row count of the same join is estimated under the
> independence assumption that calc_joinrel_size_estimate() already makes.
> The same cost table therefore holds two contradictory beliefs about one
> join: on a 1M x 1M three-column join with 100 distinct values per column
> (one row per bucket), hash join is priced at 166,622,722 and never
> chosen; with the corrected bucket size it costs 282,717 and runs in
> 1,946 ms versus 4,154 ms for the merge join it used to pick (-53%).
> The estimate now comes from estimate_num_groups() on the clauses
> estimate_multivariate_bucketsize() hands back, so bucket size and output
> rows are built from the same estimator. SEMI/ANTI joins are
> deliberately excluded: their probe charge is already scaled by
> inner_scan_frac, and repricing it regressed a merge-semi-join query of
> the workload by 27%.

I think it's worth looking at the earlier discussion on this list. 0001 is
essentially the approach we discussed previously [1], which concluded with
6bb6a62f3 - only using the product where the extended stats backed it up. If you
have any new arguments for re-visiting that, please put them forward.

[1]
https://www.postgresql.org/message-id/1de52b5f-e4a6-5729-eeef-51d524534665%40enterprisedb.com

--
regards, Andrei Lepikhov,
pgEdge