ClickHouse: Turbocharge Queries with Index Pruning

Unlocking Query Speed

The article provides an excellent, practical demonstration of ClickHouse's index-based pruning techniques, effectively illustrating how primary indexes, lightweight projections, and skip indexes contribute to significant performance gains. The use of a real-world dataset and clear, step-by-step examples, including EXPLAIN outputs and query log data, makes the concepts highly accessible. The comparison between different pruning strategies, especially highlighting the trade-offs and scenarios where each excels, is particularly valuable for database administrators and developers looking to optimize their ClickHouse deployments. The explanation of how the primary index's efficiency is tied to the prefix of the sorting key, and how filtering on non-prefix columns degrades performance, sets the stage perfectly for introducing lightweight projections and skip indexes as solutions for these scenarios.

However, while the article effectively showcases the benefits, it could delve deeper into the operational considerations of these techniques. For instance, when discussing lightweight projections, the article mentions the storage overhead is reduced compared to full projections but still involves storing sorting keys and offsets. Clarifying the typical storage increase for lightweight projections relative to the base table, especially for tables with many columns or very wide rows, would be beneficial. Additionally, more detailed guidance on when to choose between a lightweight projection and a skip index (like minmax) beyond the correlation requirement for minmax would be helpful. For example, discussing the query latency differences, maintenance overheads, and how the complexity of the projection's sorting key impacts its effectiveness would add further depth. The article briefly touches on optimize_use_projections = 1 being enabled by default, but a more explicit discussion on how ClickHouse automatically decides to use a projection, and potential tuning parameters for projection selection, could empower users further in managing their indexing strategies.

Key Points

• ClickHouse offers three primary index-based pruning techniques: Primary Index, Lightweight Projections, and Skip Indexes.
• The Primary Index is fundamental, determining data sort order and enabling granule skipping based on filters on the primary key columns.
• Lightweight Projections act like secondary indexes without duplicating full rows, storing only the sorting key and an offset pointer, ideal for filtering on columns not in the primary key.
• Skip Indexes, like minmax, store aggregate column statistics per granule, allowing pruning without storing column values, but are most effective when the indexed column is correlated with the primary key.
• The article demonstrates significant performance improvements, reducing query times from seconds to milliseconds, by effectively leveraging these indexing strategies on a large dataset.
• Optimizing table design by choosing appropriate primary keys and understanding the benefits of additional indexes (projections, skip indexes) is crucial for high-performance analytical queries.

---

📖 Source: Index-based pruning in ClickHouse