chmonitorClickHouse skip indices: the complete secondary index guide
minmax, set, bloom_filter, and tokenbf_v1 skip indices explained — what each is for, how GRANULARITY works, and the diagnostic SQL to verify one is actually firing.
Part of the ClickHouse query optimization guide.
ClickHouse "secondary indices" don't work like a B-tree in Postgres or MySQL. They're data-skipping indices: for each block of granules, ClickHouse stores a small summary (a min/max range, a set of values, or a bloom filter), and at query time it uses that summary to skip whole granule blocks that can't possibly match — without ever reading the underlying column for the skipped rows.
The four index types
Range queries on numeric/date columns not already covered by ORDER BY. Stores min/max per granule block; skips blocks entirely outside the range.
ALTER TABLE events ADD INDEX idx_amount amount TYPE minmax GRANULARITY 4;Equality / IN on low-cardinality columns. Stores up to N distinct values per block; if a block has more than N unique values, the index gives up and reads everything for that block.
ALTER TABLE events ADD INDEX idx_status status TYPE set(100) GRANULARITY 4;Equality on higher-cardinality strings or numbers. Probabilistic membership test per block; false positives read unnecessary blocks, false negatives never happen.
ALTER TABLE events ADD INDEX idx_request_id request_id TYPE bloom_filter GRANULARITY 4;Substring/text search (LIKE '%term%', log/URL search). Tokenized or n-gram bloom filter (tokenbf_v1 / ngrambf_v1) over the column's text.
ALTER TABLE events ADD INDEX idx_message message TYPE tokenbf_v1(4096, 3, 0) GRANULARITY 4;GRANULARITY N controls how many index-table granules are grouped into one
index block. A larger value means a smaller index but coarser skipping — start
at 4 and tune from there.
Adding an index doesn't backfill existing parts
ALTER TABLE ... ADD INDEX only applies to parts written after the change.
Run ALTER TABLE ... MATERIALIZE INDEX idx_name to build it for existing
parts, or wait for the natural merge cycle to rewrite them.
Common mistakes
Verify an index actually fires
EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE request_id = '3f9c1a2e-...';Look for your index name in the output and confirm Granules: N/M shows N
well below M. If the index doesn't appear in the plan, the expression
doesn't match the query's filter, or the condition wasn't eligible for
pushdown — check PREWHERE vs WHERE too.
Audit for dead indices
This flags indices with zero compressed bytes — added but never actually built, or built and then never touched by writes worth measuring:
SELECT
database,
table,
name,
type,
expr,
granularity,
formatReadableSize(data_compressed_bytes) AS compressed_size,
if(data_compressed_bytes = 0, 'dead', 'active') AS status
FROM system.data_skipping_indices
ORDER BY data_compressed_bytes DESC;Combine this with EXPLAIN indexes = 1 output over your real query workload —
an index with nonzero size that never appears in any plan is a maintenance
cost with no payoff. Drop it with ALTER TABLE ... DROP INDEX idx_name.
chmonitor tie-in
The Data Explorer's per-table Skip Indexes panel lists every index with
type, expression, granularity, and compression ratio — the same
system.data_skipping_indices data above, without SQL. The AI agent's
get_optimization_recommendations tool proposes specific skip-index DDL
(with the type and expression already chosen) when it profiles a slow query.
Related
ClickHouse skip indices in 5 minutes
A short walkthrough of the four index types and the EXPLAIN that proves one fires.
ClickHouse query optimization (pillar)
The full map of six optimization areas.
PREWHERE vs WHERE
Filter pushdown that works alongside skip indices, not instead of them.
Partition key best practices
Rule out a partitioning problem before adding indices.
Data Explorer
Browse schema, indices, and projections per table without SQL.
ClickHouse projections vs materialized views
A decision guide for choosing between a projection and a materialized view in ClickHouse — storage, query rewriting, JOIN support, lifecycle coupling, and diagnostic SQL.
Reduce ClickHouse query memory with external GROUP BY
How max_bytes_before_external_group_by spills aggregation to disk to avoid MEMORY_LIMIT_EXCEEDED, cheaper alternatives to try first, and the diagnostic SQL to confirm a spill happened.