How ClickHouse optimizes Top-N queries

Top-N queries—finding the most recent events, highest-scoring users, or biggest transactions—are everywhere in data analytics. ClickHouse has some clever engine-level optimizations specifically designed to make these queries blazingly fast. In this video, we walk through several of these optimizations using a 100-million-row web analytics dataset, showing you exactly how they work and when to use them.

We'll cover how ClickHouse leverages sorting keys and min-max indexes to speed up top-N queries, and explore three experimental settings that can dramatically improve performance: `use_skip_indexes_for_top_k`, `use_skip_indexes_on_data_read`, and `use_top_k_dynamic_filtering`. Each optimization tackles a different scenario, from queries that align with your sorting key to those that don't.

🔸 Understanding how sorting keys and min-max indexes accelerate top-N queries
🔸 Using `use_skip_indexes_for_top_k` to skip irrelevant granules (400ms → 11ms improvement)
🔸 Applying `use_skip_indexes_on_data_read` for queries with WHERE clauses (1.5s → 100ms improvement)
🔸 Leveraging `use_top_k_dynamic_filtering` for columns without indexes (400ms → 100ms improvement)
🔸 Practical examples with real query execution times on 100M rows

Видео How ClickHouse optimizes Top-N queries канала ClickHouse