Can GROUP BY still be optimized after all these years?

Analytical databases commonly perform high-cardinality GROUP BY operations by having CPU threads aggregate separate slices of data, then merge partial results across hash-based partitions. This produces every group, even when a query only needs the top K groups ordered by an aggregate. For queries such as finding the ten most-ordered items, most partitions may contain no group capable of making the final list, yet the engine still spends time merging them. A simple partition-wide total can provide a safe upper bound: if it is below the current tenth-place count, no group in that partition can qualify. But this bound is often too coarse. Many modest groups can add up to a large total, preventing pruning even though none could enter the top K.

The proposed method gives each partition a fixed set of hash-selected slots, each accumulating the values of groups assigned to it. Because an individual group cannot exceed its slot’s total, a partition can be skipped when every slot’s total falls below the current top-K cutoff. More slots provide finer bounds without requiring more partitions; however, flat distributions may offer little benefit, so the engine should detect when to disable the optimization. In Pivot’s ClickBench test, query 16, which groups search records and returns the ten largest counts, ran in 22.2 ms rather than 35.6 ms - a 1.6× speedup - while more than 70% of partitions were pruned. The technique could save additional network and disk work in future distributed and spill-to-disk execution.