Modern CPUs are capable of processing instructions at extremely high speeds, often performing 4 to 8 operations per nanosecond. In contrast, accessing data from system RAM (DRAM) is significantly slower, taking between 50 and 100 nanoseconds. This discrepancy means a CPU can theoretically execute 400 to 800 instructions for every single memory access from RAM. To mitigate this, processors use a hierarchy of caches (L1, L2, and L3) that are physically closer to the core and much faster than DRAM. However, these caches are relatively small—L1 caches are typically around 64KB—requiring software to be designed with 'cache sympathy' to ensure frequently accessed data remains in these high-speed tiers.

When performing a `GROUP BY` aggregation on a large dataset, a naive implementation might use a single large hash table to store every unique group key. If the number of unique keys is large, the hash table will exceed the size of the CPU's cache. This forces the CPU to perform 'cold' memory accesses to DRAM for nearly every operation. Even if a system monitor shows the CPU at 100% utilization, the processor may actually be spending a majority of its time 'stalling'—waiting for data to arrive from memory rather than executing instructions. In a test using a 99-million-row dataset, a naive approach resulted in the CPU stalling on memory for over 50% of the time, while only retiring operations about 16% of the time.

To optimize this, developers can use a technique called radix partitioning. This involves splitting the data into a fixed number of smaller partitions (e.g., 4,096) based on a hash of the key. By processing these smaller partitions one at a time, the corresponding hash tables are much more likely to fit entirely within the CPU's cache. While this method technically requires more total operations—as data must be moved into partitions before being aggregated—the trade-off is highly beneficial. In the same test, switching to radix partitioning reduced the memory stall time from 50.1% to 23.5% and reduced the amount of data pulled from DRAM by 60%, demonstrating that increasing the total instruction count is often a winning strategy if those instructions replace slow memory waits.