It's the Memory Latency, Stupid

A database query can report 100% CPU use while the processor spends much of its time stalled, waiting for data from RAM. The key bottleneck in a GROUP BY with millions of distinct values is often memory latency: a large hash table makes lookups likely to miss the CPU’s small, fast caches and fetch data from much slower DRAM. The argument is that doing more CPU work can still make a query faster if it reduces those costly memory accesses.

To test this, the author compares a straightforward aggregation against a partitioned approach on ClickBench’s 99-million-row hits dataset, with about 17 million unique UserIDs. The partitioned version hashes each key into one of 4,096 vectors, then aggregates each partition in a smaller hash table. On a single core of an AMD EPYC 9124, this extra work reduced memory-stall time from roughly 50% to 23.5% and DRAM reads from 4.5 GB to 1.5 GB; runtime fell to about 2.7-3.3 seconds, depending on the run. Pivot’s more optimized single-core implementation took about 1.85 seconds and read 954 MB, while its 16-core execution took about 172 milliseconds. The broader lesson is to optimize for data locality and cache behavior, not simply to minimize instructions.