ClickHouse offers three index-based pruning techniques to minimize data reads in analytical queries. The primary index sorts data into granules and enables binary search to skip irrelevant ones. Lightweight projections (introduced in ClickHouse 25.6) store only a sort key plus a pointer back to the base table, enabling efficient filtering on non-primary-key columns without full data duplication — and multiple projections can be combined for intersection-based pruning. Skip indexes like minmax record min/max values per granule and work best when the target column correlates with the primary key. All three techniques are demonstrated on a 243 million row UK property sales dataset with concrete query timings and EXPLAIN output.
Table of contents
Pruning technique #1: Primary index #Pruning technique #2: Lightweight projections #Pruning technique #3: Skip indexes #Pruning in action: UK property dataset #Conclusion #Sort: