Index-based pruning in ClickHouse

Summary

ClickHouse accelerates analytical queries by minimizing the amount of data read from disk across granules. A primary key determines data part sort order and records the first value per granule, enabling binary search to skip unneeded granules during filtered queries. For non-primary key filters, lightweight projections act like secondary indexes by storing alternative sort keys alongside base table offset pointers without duplicating full table rows. Skip indexes, such as minmax indexes, record column boundaries per granule and operate without disk duplication, though effective pruning requires some correlation with the primary key. Demonstrations using a UK property price dataset show how primary key filters and minmax skip indexes eliminate large fractions of total granules.

Context

Analytical query performance depends directly on minimizing the volume of data scanned from disk during execution.

Approach / What changed

Evaluate three ClickHouse index-based pruning mechanisms—primary indexes, lightweight projections, and minmax skip indexes—against a multi-million-row UK property sales dataset.

Takeaways

• ClickHouse primary indexes record the primary key value of the first row of each default 8,192-row granule, allowing queries to prune non-matching granules prior to reading.
• Lightweight projections store alternative sort keys with _part_offset pointers instead of entire rows, avoiding full-table data duplication while pruning non-primary-key filters.
• Minmax skip indexes record minimum and maximum column values per granule without duplicating data on disk, but require correlation with the primary key to prune effectively.

Related reading