ClickHouseadvanced

Data Skipping Indexes

Secondary indexes that let ClickHouse skip granules on non-sorting-key columns, and when they are worth their cost.

2 min readAdvancedUpdated 31 Jul 2026Edit this page

A data skipping index stores a summary per block of granules — a minimum and maximum, a set of values, or a Bloom filter — and lets ClickHouse skip blocks that cannot match a predicate.

They are not B-tree indexes. They never point at rows; they only allow blocks to be excluded.

Types

Type	Stores	Good for
`minmax`	Min and max per block	Columns correlated with the sort order, e.g. a secondary timestamp
`set(N)`	Up to N distinct values per block	Low-cardinality columns clustered in the data
`bloom_filter(p)`	Bloom filter of values	Equality on high-cardinality columns
`ngrambf_v1`	Bloom filter of n-grams	`LIKE '%substring%'` searches
`tokenbf_v1`	Bloom filter of tokens	Whole-word matching in text

ALTER TABLE events
  ADD INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4;
 
ALTER TABLE events
  ADD INDEX idx_country country TYPE set(100) GRANULARITY 4;
 
-- Existing data is not indexed until you materialise it.
ALTER TABLE events MATERIALIZE INDEX idx_user;

GRANULARITY 4 means one index entry per 4 granules — roughly 32,768 rows with the default index_granularity. Larger values make the index smaller and coarser.

They only work if the data is clustered

Measuring the effect

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 4711;

The output lists each index and how many granules it dropped. If an index drops nothing, remove it.

Compare read_rows before and after:

SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read
FROM system.query_log
WHERE type = 'QueryFinish' AND query LIKE '%user_id = 4711%'
ORDER BY event_time DESC LIMIT 5;

Cost

Skipping indexes are maintained on every insert and merge, and they consume disk and page cache. An index that skips nothing is pure overhead on the write path.