Overview
This article explains how ClickHouse optimizes Top-N queries (ORDER BY ... LIMIT N) using granule-level data skipping indexes. By leveraging min/max metadata stored in data-skipping indexes, ClickHouse can eliminate entire granules before reading any data, achieving 5-10x faster query execution and 10-100x less data read, with both static (no predicates) and dynamic (with predicates) filtering approaches.
What You'll Learn
1
How ClickHouse uses minmax data skipping indexes to eliminate granules before reading any data for Top-N queries
2
How static Top-N filtering works by preselecting granules based on min/max metadata without predicates
3
How dynamic Top-N threshold filtering progressively tightens pruning during query execution with predicates
4
When to enable use_skip_indexes_for_top_k and use_top_k_dynamic_filtering settings for optimal Top-N performance
5
Why granule-level metadata pruning becomes increasingly impactful as table sizes grow into billions of rows
Prerequisites & Requirements
- Understanding of ClickHouse granules as the smallest processing units (8,192 rows by default)
- Familiarity with SQL ORDER BY and LIMIT clauses for Top-N query patterns
- Basic understanding of data skipping indexes and primary indexes in ClickHouse
- Experience running analytical queries on large datasets in ClickHouse(optional)
Key Questions Answered
How does ClickHouse optimize Top-N queries using data skipping indexes?
ClickHouse uses minmax data skipping indexes to compare the current Top-N threshold against granule-level min/max metadata. For queries without predicates, it preselects only granules with the smallest (or largest) min values. For queries with predicates, it dynamically tightens the threshold during execution, skipping granules that cannot improve the result. This yields 5-10x faster queries and 10-100x less data read.
What is the difference between static and dynamic Top-N filtering in ClickHouse?
Static Top-N filtering applies to queries without WHERE clauses, where ClickHouse preselects granules upfront by examining min/max metadata on the ORDER BY column. Dynamic Top-N filtering applies to queries with predicates, where ClickHouse continuously maintains the current Top-N threshold during execution and progressively skips more granules as the threshold tightens with better candidates found.
How do I enable granule-level data skipping for Top-N queries in ClickHouse?
Enable the use_skip_indexes_for_top_k setting for static Top-N filtering without predicates. For dynamic filtering with predicates, also enable use_top_k_dynamic_filtering and use_skip_indexes_on_data_read settings. A minmax data skipping index must exist on the ORDER BY column. These settings are available starting from ClickHouse 25.12.
What existing Top-N optimizations does ClickHouse already apply before granule skipping?
ClickHouse applies three existing optimizations: streaming Top-N (keeps only N candidates in memory rather than sorting all rows), read-in-order (avoids sorting if data is already ordered on disk or via projections), and lazy reading (determines Top-N rows using only ORDER BY columns first, then reads remaining columns only for result rows). These are orthogonal and composable.
How much faster are ClickHouse Top-N queries with granule-level data skipping?
In benchmarks on a 100 million row table, static Top-N filtering reduced query time from 0.044s to 0.009s (roughly 5x faster) and data read from 1.2 GB to 4.95 MB. Dynamic Top-N filtering with predicates reduced query time from 0.325s to 0.033s (roughly 10x faster) and data read from 9.42 GB to 520.58 MB. Benefits grow further with larger tables.
Does ClickHouse Top-N data skipping work on very large tables with billions of rows?
Yes. In early production testing on a table with 50 billion rows, Top-N queries using skip index filtering completed in under 0.2 seconds, confirming that granule-level pruning remains effective at extreme scale. The I/O benefit grows with table size, especially when the cache is cold, as avoiding unnecessary reads at the granule level becomes increasingly impactful.
What is streaming for secondary indices in ClickHouse and how does it relate to Top-N?
Streaming for secondary indices, introduced in ClickHouse 25.9, allows data skipping index checks to be interleaved with data reads instead of evaluated upfront. For dynamic Top-N filtering, this means as each granule becomes eligible for reading after primary index analysis, its minmax index entry is immediately consulted against the current Top-N threshold, enabling progressive pruning during execution.
Why is metadata-driven Top-N pruning especially useful with object storage architectures?
In modern architectures with object storage or disaggregated compute, avoiding unnecessary reads saves not just CPU but also network I/O and latency. Since granule-level metadata pruning eliminates data reads before any rows are touched, it prevents costly network round-trips to remote storage, making Top-N queries significantly more efficient in cloud-native and disaggregated deployments.
Key Statistics & Figures
Static Top-N query speedup
~5x faster
0.044s to 0.009s
Static Top-N rows processed reduction
~100 million to ~163 thousand rows
With use_skip_indexes_for_top_k enabled
Static Top-N data read reduction
1.2 GB to 4.95 MB
Data read for Top-N query without predicates
Dynamic Top-N query speedup
~10x faster
0.325s to 0.033s
Dynamic Top-N rows processed reduction
~100 million to ~7 million rows
With dynamic Top-N threshold filtering enabled
Dynamic Top-N data read reduction
9.42 GB to ~520.58 MB
Data read for Top-N query with WHERE predicate
Production-scale validation
Under 0.2 seconds on 50 billion rows
Early testing of Top-N queries with skip index filtering on production tables
Default granule size
8,192 rows
Smallest processing unit in ClickHouse
Overall data read reduction range
10-100x less data read
Especially effective on large tables and cold cache
Benchmark hardware
AWS m6i.8xlarge
32 cores, 128 GB RAM
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Column-oriented analytical database implementing granule-level Top-N query optimizations
Language
SQL
Query language for Top-N queries using ORDER BY ... LIMIT N patterns
Cloud Infrastructure
AWS EC2
m6i.8xlarge instance used for benchmark testing (32 cores, 128 GB RAM)
Cloud Storage
AWS Ebs
gp3 volume used for benchmark testing (16k IOPS, 1000 MiB/s throughput)
Key Actionable Insights
1Add minmax data skipping indexes on columns frequently used in ORDER BY clauses for Top-N queries. This enables ClickHouse to compare granule-level min/max metadata against Top-N thresholds and skip entire granules without reading any data, dramatically reducing I/O.This is most impactful on large tables (billions+ rows) and cold cache scenarios where avoiding unnecessary disk or network reads provides the greatest benefit.
2Enable the use_skip_indexes_for_top_k setting for Top-N queries without predicates to activate static granule-level pruning. In benchmarks, this reduced rows processed from 100 million to 163 thousand and data read from 1.2 GB to 4.95 MB.This setting is available in ClickHouse 25.12 and works by preselecting only the granules with the most promising min/max values for the ORDER BY column.
3For Top-N queries with WHERE clauses, enable both use_top_k_dynamic_filtering and use_skip_indexes_on_data_read alongside use_skip_indexes_for_top_k. This activates dynamic threshold filtering that progressively prunes more granules as better Top-N candidates are found during execution.Dynamic filtering achieved 10x speedup in benchmarks by reducing processed rows from 100 million to 7 million. The threshold tightens automatically as execution progresses.
4Recognize that these new optimizations compose with existing Top-N techniques (streaming execution, read-in-order, and lazy reading). Each addresses a different bottleneck, so combining them provides cumulative benefits rather than requiring you to choose one approach.Streaming limits memory usage, read-in-order avoids sorting, lazy reading defers non-order column I/O, and data skipping eliminates granules entirely—all applicable independently within a single query.
5Consider the impact of granule-level pruning when designing table schemas and choosing primary keys. Since data is organized into granules of 8,192 rows by default, the distribution and ordering of ORDER BY column values across granules affects how many granules can be skipped.In practice, more than the minimum number of granules may be read because data is processed in blocks spanning multiple adjacent granules and multiple threads process data in parallel.
6Prioritize this optimization for cloud-native and disaggregated compute architectures where data resides in object storage. Metadata-driven pruning avoids not just CPU work but also network I/O and latency from unnecessary remote storage reads.This makes the optimization especially valuable in environments where storage latency is higher than local disk, turning Top-N into a metadata problem that can be resolved before touching the actual data.
Common Pitfalls
1
Expecting minimal granules to be read in all cases. In practice, more than the theoretically minimum number of granules may be read because data is processed in blocks that can span multiple adjacent granules, and multiple threads process data in parallel.
The granule skipping optimization provides significant benefits but the actual number of granules read depends on block sizes and parallelism settings.
2
Forgetting to create a minmax data skipping index on the ORDER BY column before enabling the optimization settings. Without the minmax index metadata, ClickHouse has no granule-level min/max values to compare against and cannot perform the pruning.
The use_skip_indexes_for_top_k and use_top_k_dynamic_filtering settings require a pre-existing minmax data skipping index on the relevant column to function.
3
Not enabling all required settings for dynamic Top-N filtering with predicates. Dynamic filtering requires three settings: use_skip_indexes_on_data_read, use_skip_indexes_for_top_k, and use_top_k_dynamic_filtering, whereas static filtering only needs use_skip_indexes_for_top_k.
Missing any of these settings when running Top-N queries with WHERE clauses will prevent the dynamic threshold filtering from activating.
4
Assuming the query condition cache will demonstrate the raw improvement from dynamic Top-N filtering. The query condition cache can mask the data skipping benefits, so benchmarks should disable it with use_query_condition_cache = 0 to isolate the effect of dynamic filtering.
When benchmarking or comparing optimization effects, disable the query condition cache to see the actual impact of granule-level pruning.
Related Concepts
Data Skipping Indexes
Minmax Indexes
Granule-based Data Processing
Primary Indexes
Projections
Streaming Execution
Lazy Materialization
Read-in-order Optimization
Query Condition Cache
Object Storage Architectures
Disaggregated Compute
Column-oriented Databases
Analytical Query Optimization
Streaming For Secondary Indices