GROUP BY query optimization techniques
Archived — this story has rotated out of today’s deck. It is kept here in full.
The gist
Pivot's Gilad Kleinman published a blog post on a slot-based method that prunes partitions in top-K GROUP BY queries.
The technique targets a common analytical query pattern where most merged partitions add nothing.
Background
Pivot is an analytical database, and Gilad Kleinman is the author of a Pivot blog post describing the work. Analytical databases typically run GROUP BY on high-cardinality grouping keys by splitting input into partitions per thread and aggregating each separately; ClickHouse, another analytical database cited in the post, uses 256 partitions per thread. The post argues that in top-K queries — such as 'which 10 items have the most orders?' — many groups and partitions are aggregated even though they cannot reach the final answer. The work is framed as a novel optimization that improved Pivot's performance significantly on these queries.
How it unfolded
- Oct 27, 2026Gilad Kleinman published a Pivot blog post describing a novel optimization that improved performance significantly on GROUP BY queries finding the top K groups by aggregate value.
- recentlyThe post explains that most analytical databases, Pivot included, partition input by grouping key per thread and aggregate each partition separately; ClickHouse uses 256 partitions, so each thread keeps its own set of 256 hash tables.
- recentlyAfter initial aggregation, workers merge assigned partition ranges, combining partial aggregates from all threads into final aggregates per partition.
- recentlyThe post notes that with 4,096 partitions, the top 10 items can belong to at most 10 partitions, leaving at least 4,086 whose results contribute nothing yet are still merged.
- recentlyA first approach kept a global counter of total orders per partition and skipped merging a partition whose total was below the smallest count in the current TopK results; the post says this requires an extreme distribution of aggregate values to prune.
- recentlyThe team's method gives each partition a fixed number of slots, each tracking the combined order count of groups mapped to it, so a partition can be pruned if every slot's total is below the smallest TopK value.
Key terms
- GROUP BY
- A SQL clause that partitions result rows into groups based on their column values, often to apply aggregate functions.
- partitions
- A division of a logical database into distinct independent parts, done for manageability, performance, availability, or load balancing.
Source: Wikipedia
Who’s saying what
- Author
- Gilad Kleinman writes that a simple operation can become extremely complex when optimized to its highest levels, and that in top-K GROUP BY queries many groups and partitions are aggregated when they don't actually need to be.
- Public
- A Reddit r/programming commenter said, speaking generally, that database systems will not find every last structural optimization because many optimizations are not free.
Still unverified
The post describes the slot-based pruning method as the approach 'we came up with', but the scraped text cuts off before presenting results.