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/ph0pr18mb444308903d2f293cea42b1cca6...@ph0pr18mb4443.namprd18.prod.outlook.com
