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
