It's the Memory Latency, Stupid

Database queries that perform group by operations will now run significantly faster if they previously stalled on memory latency.

The Pivot database engine has introduced a new optimization for its GROUP BY aggregation function, addressing a significant performance bottleneck caused by memory latency. When CPUs operate at 100% utilization, they are often stalled waiting for data from RAM, which is orders of magnitude slower than CPU operations. This is exacerbated by large datasets with many unique groups, leading to frequent "cold" memory accesses. To combat this, Pivot's new approach, called radix partitioning, divides the data into smaller partitions. The first phase hashes each incoming row's key and assigns it to one of 4096 partitions. The second phase processes each partition individually, building a smaller HashMap for aggregation. This strategy aims to keep the HashMaps within the faster CPU caches, reducing the time CPUs spend waiting for memory.

The core issue identified is that even when a CPU reports 100% utilization, it can be heavily bottlenecked by memory access speeds. A modern CPU can perform hundreds of instructions in the time it takes to access RAM, and while CPU caches mitigate this, large datasets with many unique groups can exhaust cache capacity. Pivot's naive implementation resulted in over 50% of CPU time spent stalled on memory, with only 16.1% of operations being retired, demonstrating the severity of the latency problem.

By employing radix partitioning, Pivot re-engineers the aggregation process to favor cache utilization. The strategy of dividing the data into 4096 partitions ensures that the HashMaps used for aggregation are considerably smaller, increasing the probability that they reside within the faster CPU caches. This allows the CPU to perform more operations locally without waiting for slower main memory, thereby improving overall query execution speed for GROUP BY operations, especially on large and diverse datasets.