Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 35 ยท Performance and Indexing

Data Skipping Indexes

Learn ClickHouse data skipping indexes: minmax, set, bloom_filter, ngrambf and tokenbf, how GRANULARITY works, how to add and materialize them and when they do not help.

The sparse primary index only helps filters on the leading columns of the sorting key. What about a query such as WHERE url LIKE '%checkout%' or WHERE status = 503 when neither column is in the key? A data skipping index (also called a secondary index) answers a different question than a B-tree: not "where is this row?" but "which blocks can I rule out?"

ClickHouse statements below are read-only snippets; the runnable stand-ins model block skipping with SQL, so you can count the blocks you would avoid.

How skipping works

A skipping index stores a small summary for each group of granules. The size of the group is the index GRANULARITY: a value of 4 means one summary entry per 4 granules, so 32,768 rows. Before reading a block, ClickHouse checks the summary. If the summary proves that no row in the block can match the WHERE condition, the whole block is skipped.

flowchart LR
    Q["WHERE status = 503"] --> S["Index summary per block"]
    S -->|"cannot match"| X["Skip block"]
    S -->|"might match"| R["Read block and filter rows"]

An index may produce false positives (reads a block that has no match) but never false negatives.

The effect depends entirely on how your data is laid out. If matching rows are scattered evenly across all blocks, every block "might match" and nothing is skipped. Skipping indexes shine when the indexed value correlates with the sorting order, or when matches are rare and clustered.

The index types

Type Stores per block Good for
minmax min and max of an expression range filters on a column loosely correlated with the key, such as a timestamp when sorting by another column
set(N) up to N distinct values (empty if more) low-cardinality columns, equality and IN
bloom_filter(p) a Bloom filter with false-positive rate p equality on high-cardinality values (ids, tokens)
tokenbf_v1(size, hashes, seed) Bloom filter of whole tokens (split on non-alphanumerics) hasToken, LIKE and equality on words in logs
ngrambf_v1(n, size, hashes, seed) Bloom filter of n-grams substring search LIKE '%abc%'
text (newer versions) an inverted full-text index full-text search, availability depends on version

Creating an index

Add it in the table definition or later with ALTER:

SQL
CREATE TABLE logs
(
    ts      DateTime,
    service LowCardinality(String),
    status  UInt16,
    trace   String,
    message String,
    INDEX idx_status  status            TYPE set(100)                   GRANULARITY 2,
    INDEX idx_trace   trace             TYPE bloom_filter(0.01)         GRANULARITY 4,
    INDEX idx_words   message           TYPE tokenbf_v1(10240, 3, 0)    GRANULARITY 4
)
ENGINE = MergeTree
ORDER BY (service, ts);

ALTER TABLE logs ADD INDEX idx_ts_minmax ts TYPE minmax GRANULARITY 8;

ALTER TABLE ... ADD INDEX only applies to new parts. To build the index for existing data you run:

SQL
ALTER TABLE logs MATERIALIZE INDEX idx_ts_minmax;

This is a heavy mutation on big tables, so schedule it during quiet periods. ALTER TABLE logs DROP INDEX idx_status removes an index.

Using and checking indexes

Queries need no special syntax. The planner uses an index when the WHERE expression matches its definition. The expression must match, so an index on lower(message) helps only conditions written with lower(message).

SQL
SELECT count() FROM logs WHERE trace = 'a1b2c3d4';
SELECT count() FROM logs WHERE hasToken(message, 'timeout');

Check pruning with:

SQL
EXPLAIN indexes = 1 SELECT count() FROM logs WHERE trace = 'a1b2c3d4';

The output lists each index with Granules: selected/total. If the selected number is close to the total, the index is not paying for itself, because it costs disk space, insert time and a little query-planning time.

When a skipping index is the wrong tool

  • If you can put the column into the sorting key or build a projection with a different sort order, that usually beats a skipping index. See projections.
  • On columns whose values are spread uniformly, nothing is skipped.
  • Very selective point lookups on huge tables may suit a bloom filter well, but remember that ClickHouse still reads whole granules.
  • Do not index every column. Add one at a time and measure with EXPLAIN, system.query_log and read_rows.

A well-known trick is to build a minmax index on a column that grows with the sort key. For example, a table sorted by (user_id) but with an insert-ordered ts: each part covers a limited time range, so a minmax on ts lets time-range filters skip entire parts.

Stand-in: block summaries in SQL

The stand-in divides rows into blocks of 100 and builds a min/max summary and a distinct-set summary per block. Then it counts how many blocks a filter has to read. This is the logic of minmax and set indexes (stand-in; the real indexes are stored in skp_idx_* files beside the data).

SQL Stand-in: minmax pruning on a correlated column

Press Run (or Ctrl + Enter) to see the result here.

The timestamp filter skips almost everything because ts is correlated with block order. The status = 503 filter skips nothing: one failure every 50 rows means every block contains a match, so an index on status would be useless here.

SQL Stand-in: clustering the rare value makes the index useful

Press Run (or Ctrl + Enter) to see the result here.

Same number of failures per row would give a very different benefit; the layout decides.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a data skipping index tell ClickHouse?

    It stores a summary per group of granules and lets the engine skip blocks that cannot match; matching blocks are still read and filtered.
  2. 2. What does GRANULARITY 4 mean for a skipping index?

    Index granularity is counted in table granules, so GRANULARITY 4 covers 4 x 8192 rows by default.
  3. 3. You added an index with ALTER TABLE ADD INDEX. Old parts are not using it. What is missing?

    ADD INDEX applies to new parts only; MATERIALIZE INDEX builds it for existing data.
  4. 4. When is a set or bloom_filter index likely useless?

    If every block contains a match, nothing can be skipped, and the index only adds cost.

Summary

  • Skipping indexes rule out blocks of granules; they do not locate single rows.
  • Choose among minmax, set, bloom_filter, tokenbf_v1 and ngrambf_v1 by the filter type.
  • Add with ADD INDEX, build for old data with MATERIALIZE INDEX, and verify with EXPLAIN indexes = 1.
  • Benefit depends on data layout; prefer the sorting key or a projection when you can.

Next: Projections, Codecs and LowCardinality