Review: Price hashed aggregation for hash tables that exceed CPU cache capacity

Hi Will,

This is a review of CF 7244 [1].

Contents & Purpose

==================

The patch adds a CPU-cache residency penalty to hashed and mixed aggregation, according to the effective_cpu_cache_size setting. The intention is to improve hash-versus-sort decisions when the hash table exceeds the cpu cache capacity.

The changes include configuration registration, sample configuration, and user documentation. There are no new regression tests.

Initial Run

==================

The patch applies cleanly to HEAD. I found every test passes and compilation works smoothly.

Performance

==================

The intended semijoin case shows a useful improvement. After running unique_attrib.sql, With serial execution and work_mem=256MB, my median measured execution time decreased from 1,272 ms with the penalty disabled to 998 ms at 8MB.

However, I may have found a regression with a wide aggregate argument:

SELECT sum(n)
FROM (
SELECT k, sum(octet_length(p)) AS n
FROM d
GROUP BY k
) q;
With 400,000 rows, 200,000 integer groups, and a 512-byte p, the penalty changes the plan from HashAggregate to Sort + GroupAggregate. One paired series slowed from 137 ms to 169 ms and a second slowed from 118 ms to 160 ms. Forcing hash prevented this slowdown.

The new calculation uses input_width=520, but the wide aggregate argument is consumed rather than retained in each hash entry. The added cost implies approximately 109 MiB of modeled residency, while EXPLAIN reports about 16 MiB of peak hash memory.

The input-width heuristic already exists in spill costing, but the new cache penalty makes it cause an additional plan-selection regression. Could the calculation estimate the retained grouping tuple and transition state more closely?

Nitpicking & Conclusion

==================

One suggestion would be for tests which deliberately activate the penalty. At the default work_mem=4MB and hash_mem_multiplier=2, the memory cap prevents the default 8MB cache setting from adding a charge, so passing default tests does not establish coverage of the new calculation.

The existing cost_agg() comment still says sorted and hashed aggregation have exactly equal total CPU cost. The new “per input tuple” wording also might be misleading because hash_entry_cost already includes multiplication by the input row count.

The intended workload benefits, but I recommend another revision addressing the residency estimate and adding focused regression coverage.

- Ethan

[1] CF 7244, 2026-09-01,Price hashed aggregation for hash tables that exceed CPU cache capacity
https://www.postgresql.org/message-id/flat/PH0PR18MB444308903D2F293CEA42B1CCA6A82(at)PH0PR18MB4443(dot)namprd18(dot)prod(dot)outlook(dot)com