When do data skipping indexes help ClickHouse queries?
Data skipping indexes help when a self-managed MergeTree table runs selective filters on columns that are not at the front of the ordering key, so queries read far more granules than they need. A skip index stores a small summary per group of granules (ClickHouse's unit of index-driven reads), and the engine uses that summary to skip blocks that cannot contain matching rows. The gain depends on two conditions: the filter must be selective enough that most blocks can be ruled out, and the ordering key must be unable to serve the predicate. If the filter matches most of the data, or the column is already prefix-aligned with the ORDER BY clause, a skip index adds disk and maintenance cost without a measurable benefit.
Treat skip indexes as secondary to the ordering key. The ordering key decides how data is physically laid out and is the first tool to reach for, while skip indexes only prune blocks after that layout is set. Every skip index is also written on insert and rebuilt as parts merge, so it adds work to ingestion and background merges, and it grows with the table. Before creating one, confirm the query pattern and selectivity on a copy of the table, and ask the team who owns the schema whether a key change or a materialized view would solve the problem more cleanly. The exact index types, granularity settings and their behavior should be verified against the ClickHouse documentation for the version you run.
Choosing minmax, set or Bloom filter for a filter pattern
Skip indexes only help when the filter shape matches what the index stores for each granule. The cards below compare the common choices side by side, so you can match a predicate pattern to an index type before you add one to a table.
minmax for range predicates
A minmax index stores the smallest and largest value of an expression for each group of granules. It suits range filters such as a timestamp window or a numeric band on a column that is loosely correlated with the ordering key. It does not help when values are scattered across the table, because every block's range then overlaps the filter and nothing gets skipped. Check the column's value distribution per part before choosing it.
set for low-cardinality equality
A set index keeps the distinct values of an expression seen in each granule, up to a limit you configure. It works for equality and IN filters on columns with few distinct values inside a granule, such as a status code or region. If a granule holds more distinct values than the limit, the index stores nothing useful for it, so test the limit against real data. It does not help for high-cardinality identifiers.
Bloom filter for high-cardinality lookups
A Bloom filter stores a compact probabilistic summary of values per granule and answers whether a value is possibly present. It fits equality and IN lists on high-cardinality columns such as trace IDs, user IDs or session tokens, where a set index would overflow. False positives mean some granules are read unnecessarily, and range predicates get no benefit. Tune the false positive rate and the granularity together, then confirm with EXPLAIN.
Granularity and expression matter
Every skip index works on groups of granules set by its GRANULARITY clause, so a larger value means fewer index entries but coarser skipping. The index also only matches the exact expression it was built on, so a filter on lower(email) will not use an index defined on email. Align the index expression with the WHERE clause your queries actually use, and retest after changing either.
Shapes these indexes do not help
None of these three index types helps a predicate that wraps the column in a function the index does not match, a LIKE pattern with a leading wildcard, or a filter on a column that is never selective within the ordering key's leading prefix. Ordering keys and partitions often serve these patterns better. Ask your team to list the top filters from system.query_log before adding any index.
Skip index options and when each fits
This table compares the main ClickHouse skip index types by filter shape, column cardinality, false-positive behavior and storage cost. Read each row as a starting hypothesis, then confirm the choice with EXPLAIN and rows-read counts on a copy of your data, because skip index benefit depends on how values are spread across granules.
| Index type | Best filter shape | Column cardinality and false positives | Storage, insert and tuning notes |
|---|---|---|---|
| minmax | Range predicates on a column loosely correlated with the ordering key, such as timestamps or ingest sequence numbers | Works well when values cluster by granule; false positives rise when values are scattered across granules | Small index footprint; GRANULARITY sets how many granules share one entry, so test coarser and finer values |
| set(max_rows) | Equality or IN filters on low-cardinality columns such as status codes or region names | Stores distinct values per block up to max_rows; a block that exceeds the limit cannot be skipped | Storage grows with distinct values per block; choose max_rows from measured cardinality, not from guesses |
| bloom_filter | Equality or IN filters on high-cardinality strings or IDs that are rare within most granules | Probabilistic; false-positive rate depends on the filter size and hash count you set, so measure rows read | Parameters are set in the index definition and trade size for accuracy; confirm the settings against current documentation |
| tokenbf_v1 | Token searches such as hasToken or log-style words within larger string values | Probabilistic at the token level; common tokens often match most granules and reduce benefit | Tokenization and filter size are set at creation; test against your real log message distribution |
| ngrambf_v1 | Substring matches such as LIKE with a fixed-length pattern inside longer text | Probabilistic at the n-gram level; short n-grams and common fragments raise false positives | Index size and insert cost grow with string length and n-gram size; benchmark insert throughput before rollout |
| GRANULARITY (all types) | Applies to every index type; controls how many index granules one stored entry covers | Coarser values shrink the index but skip fewer granules; finer values skip more but cost more storage and build time | Set a starting value, measure rows read in system.query_log, then adjust; the right value is data-specific |
| Any skip index | Filters that the ordering key cannot serve, or on columns outside the leading key | Benefit is often small when the filter already matches most granules | Confirm with EXPLAIN indexes = 1 that the index is applied, and test on a copy of production-scale data |
How to add and verify a skip index on a copy first
This sequence confirms that a skip index is justified by the workload, adds it to a test copy, and checks with representative queries that it skips granules before the change reaches production. Each step ends with a check that decides whether the next step runs.
Profile slow queries in query log
Start from the workload, not the index. Use system.query_log to find the slow queries that filter on a column, and record their WHERE clauses, read row counts and durations. Confirm that the filter column is outside the table's ORDER BY key, because a column already in the leading part of the key is usually pruned without a skip index. Done when you have a short list of queries, the table they hit, and the filter columns they share.
Match the index type to the filter
Choose the index from the shape of the predicate. A minmax index suits range or equality filters on a column whose values cluster by insert order, such as timestamps that rise over time. A set index suits equality or IN filters on a low-cardinality column, with a maximum size you set. A Bloom filter suits equality or IN filters on a high-cardinality value, such as an identifier, where values are spread across many parts. Write down the granularity you intend to test. Done when each target query maps to one index type and you can explain why the others would not prune well.
Add the index to a test copy
Create a copy of the table with a representative slice of production data, using the same engine, ORDER BY and partitioning. Run ALTER TABLE with ADD INDEX on the copy, naming the index, its expression, its type and its granularity. The statement only defines the index for future parts, so no existing data is indexed yet. Done when SHOW CREATE TABLE on the copy lists the new index.
Materialize the index for existing parts
Run ALTER TABLE with MATERIALIZE INDEX on the copy to build the index for parts that already exist. Without this step, queries will read old parts without the index, and results will look like the index does nothing. Watch system.mutations until the mutation for the index finishes, and check that no mutation is stuck or failed. Done when every active part reports the index as built.
Run representative queries on the copy
Run the same queries you profiled in the first step, with the same parameters or realistic sample values, against the copy with and without the index. Record the rows read and the duration for each run. Compare against the baseline on the copy without the index, not against production, since data volumes and part layout differ. Done when you have paired results per query and can see whether rows read fall for the target filters.
Check granules skipped in EXPLAIN
Run EXPLAIN indexes = 1 on each target query and read the Skip index section of the plan. Confirm that the index appears, that granules are removed, and that the remaining granule count is lower than the total. An index that appears but removes no granules is not helping the query and should be dropped or redesigned. Done when every target query shows the index in the plan with a measurable reduction in granules read.
Roll the index to production
Apply the same ALTER TABLE ADD INDEX statement to production during a window your team has agreed, then run MATERIALIZE INDEX and monitor system.mutations until it completes. Use the same EXPLAIN and query checks against production. Keep the copy's results and the agreed rollback statement, DROP INDEX, in your change record. Confirm that the target queries show the expected reduction and that ingestion latency and merge activity remain within your normal range. Done when production matches the copy results.
Why skip indexes come after the ordering key and materialized views
Decide the ordering key before you add any data skipping index, because the ordering key is the first and cheapest pruning mechanism ClickHouse has. A MergeTree table stores rows sorted by the ORDER BY columns, so filters on a leading prefix of those columns let the server skip whole granules without reading them. Choose the key from the filters your most frequent and most expensive queries use, ordered so that the most selective and most commonly filtered columns come first. A skip index is then a targeted addition for the filters that remain after the key has done its work, not a substitute for a poor key. Test the candidate key on a copy of representative data and compare rows read in the query log before you commit to it, since changing the key later means rebuilding the table.
Materialized views can change which columns an index should target, so tune skip indexes after those decisions are settled. An incremental materialized view writes to a separate target table, and that target may use a different ORDER BY, pre-aggregate the data, or drop columns entirely. An index added to the raw table may help little once a dashboard reads from the target table, and an index on the target may be wasted if the view is later rebuilt with a different key. Agree on which tables serve which queries, then add indexes only where a query on that specific table still reads too many granules. For the view design itself, see the sibling topics on materialized views and projections.
Confirm the decision with EXPLAIN rather than assuming the index helped. Run the query with EXPLAIN indexes = 1 and check whether the skip index appears in the output and how many granules it leaves after each stage, then compare rows read in system.query_log before and after the change on the same data. Record which queries each index serves, because a new index adds insert and merge cost to every write, and an index that no query uses is a cost with no benefit. Ask your team to confirm the exact EXPLAIN output format for the ClickHouse version you run, since that output changes between releases.
Questions teams ask before adding skip indexes
Do skip indexes replace the ordering key in ClickHouse?
No. The ordering key (the ORDER BY of a MergeTree table) decides how data is physically sorted and is the main way ClickHouse prunes data for filters on leading columns. A skip index stores summary information per block of granules and helps when a filter targets a column that is not in the leading part of the key. Skip indexes add to the ordering key, they do not substitute for it. If the key does not match your most common filters, fix the key first, because changing it usually means rebuilding the table. Test the key on a copy before deciding which fix is needed.
How do we confirm a skip index is actually used?
Run EXPLAIN indexes = 1 on the exact query and check the output for the index name and the number of granules read before and after it. The rows read figure from system.query_log on the same query, compared before and after the index is added, shows whether the index reduced work in practice. Note that an index created on existing data only applies to parts written after creation unless you materialize it with ALTER TABLE ... MATERIALIZE INDEX. Check both the plan and the measured reads, because an index can appear in the plan while skipping few granules.
What do skip indexes cost at insert and merge time?
Each index adds work when parts are written and when they are merged, and it adds storage for the index data itself. The cost grows with table size, with the number of indexes, and with the granularity you choose. Indexes that rarely help a query pattern still pay this cost on every insert. Ask your team to measure insert throughput and merge duration on a representative copy with and without each candidate index, and to record disk use per index. Keep the list short and remove any index that the query log shows is never used.
Should we build skip indexes ourselves or hire a partner?
Many teams can do this in-house if they already run ClickHouse in production and have someone who reads query plans well. A partner is more useful when the table is large, there are many candidate columns, or the team has no time to run controlled tests. DeployOpen is a Chainscore Labs company that implements and tunes self-hosted open-source platforms in infrastructure you control, and most engagements are fixed-scope with scope and deliverables agreed in writing before work starts. Ask any provider to state the tested query set, the test environment and the acceptance criteria in the quote.
What drives the effort of adding skip indexes?
Effort follows four factors: the size of the largest tables, the number of candidate columns and query shapes, whether representative test data exists, and how many environments must be validated. Large tables increase the time for each index build and each test run. Many candidate columns multiply the number of experiments. Without a copy of production-like data and a set of real dashboard and investigation queries, the team has to build that baseline first. Ask a provider to break the quote into discovery, test design, index changes and verification so you can see which items you can take in-house.
How do we test a skip index change safely?
Test on a copy of the table with a representative data volume and the queries your users actually run, then record rows read, query duration and insert or merge impact before and after. Apply the change first to a non-production replica or cluster, and keep a rollback path, which for an index is dropping it with ALTER TABLE ... DROP INDEX. Confirm that the change does not affect the replication of other replicas. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, and reading the changelog before any upgrade that accompanies the change.
When is a projection or materialized view a better fix than a skip index?
Choose a projection or materialized view when the problem is repeated aggregation or a different sort order over the same data, because those structures store precomputed results or re-sorted copies that answer whole query shapes. Choose a skip index when a selective filter on a column outside the ordering key reads too many granules and the query shape is stable. Skip indexes are cheaper to try and easier to remove, but they help less with heavy aggregations. Compare the candidates on the same query set, and confirm the trade-off with the separate pages on projections and materialized views before committing to storage and insert cost.
Operational risks of skip indexes in production
Skip indexes are cheap to add and easy to forget. The cards below cover the costs they add to writes and merges, the ways they drift from the table as it changes, and the access and replication questions that decide who can change them and where the change lands.
Storage and merge overhead
Every skip index stores extra granule-level metadata alongside the data parts, and that metadata is built on insert and rewritten as parts merge. A table with several indexes on high-volume columns can see its write path and background merges slow down before queries show any benefit. Check the on-disk size of each index in system.data_skipping_indices or by comparing part sizes before and after creation, and measure insert throughput on a copy of production data.
Stale indexes after schema changes
An index is defined against specific columns and expressions. When a column is renamed, retyped or replaced by a computed expression, the index may no longer match how queries filter the data, and ClickHouse will not warn you that it has stopped helping. Review index definitions in the same change request as schema changes, and confirm after each release that the queries your dashboards rely on still show index use in EXPLAIN.
False positives that give no gain
A skip index only helps when it can rule out granules that do not match the filter. A Bloom filter on a high-cardinality column with values spread across every granule, or a minmax index on a column with a wide range inside each granule, lets almost everything through. The index still costs storage and writes while reading nearly the same data. Compare rows read with and without the index for representative queries, and drop indexes that do not reduce them.
Index changes and replicated tables
On ReplicatedMergeTree tables, an ALTER that adds or materializes an index is coordinated through ClickHouse Keeper and applies to each replica. Existing parts may need the index built explicitly before queries benefit, and until that finishes, older data is scanned without it. Check replica state and mutation progress after the change, and confirm the exact behavior for your version in the replication and ALTER documentation before running it on a production cluster.
ALTER privileges and change control
Adding or dropping an index is a schema change, so it requires the ALTER privilege on the table. Many environments grant broad rights to a default or shared account, which lets any session change index definitions on production data. Create named roles for schema changes, restrict the default user as the access control documentation recommends, and route index changes through review and the same pre-production test used for other schema work.
Where to confirm index behavior before you change production
Test every skip index change on a pre-production ClickHouse environment with representative data and the queries your dashboards and jobs actually run, before you touch production. The ClickHouse production FAQ recommends exactly this kind of pre-production environment and advises reading the changelog before any upgrade, because minor releases can include backward-incompatible changes. Those changes can affect how a skip index is declared, created, materialized or chosen by the optimizer. Before you start, run SELECT version() on the target cluster and read the skip index reference for that exact release, not the latest documentation. Record the version alongside your EXPLAIN output so that later comparisons are meaningful.
Index creation on a large table rewrites index data for existing parts, so plan the change window and the disk and CPU headroom it needs, and measure that cost on the copy first. Take a fresh backup with BACKUP before the change, following the ClickHouse backup documentation, which warns that a backup that has never been test-restored will probably not restore when you need it. Restore it to a spare cluster and confirm the table, indexes and row counts match. Replication does not help here, since replicas faithfully copy a bad ALTER or a dropped index. Write down a rollback step, such as dropping the new index, and confirm who can approve the change and who runs the restore test.
How DeployOpen scopes and tunes ClickHouse indexes
DeployOpen approaches skip indexes as one part of a table design that starts with the ordering key and the queries your teams run most often. We test each index against a copy of your data, confirm with EXPLAIN that queries use it, and hand over the reasoning along with the configuration.
How we scope
- Inventory the slow filters, table sizes and query patterns that the index work is meant to serve.
- Record which columns, index types and granularity values are in scope in the written, fixed-scope agreement.
- Agree how index tests run on a representative copy of your data before any change reaches production.
How we build
- Review the ordering key and any materialized views first, so skip indexes are added only where they fill a real gap.
- Create candidate minmax, set or Bloom filter indexes on a non-production ClickHouse cluster with representative data.
- Compare EXPLAIN output and rows read before and after each change, and keep every result that supports the decision.
How we hand over
- Document each index with its column, type, granularity, the queries it serves and the measured effect.
- Provide the DDL, rollout and rollback steps, and the checks that confirm indexes were built on existing parts.
- Walk your team through the EXPLAIN checks and the criteria for retiring an index that stops helping.
How we support
- Review index usage again when query patterns change, new columns arrive or the table grows substantially.
- Re-test indexes during version upgrades, since query planning and index behavior can change between releases.
- Ongoing support, upgrades and continuous testing are available as a retainer after the initial engagement ends.
