Data skipping index examples

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/best-practices/skipping-indexes-examples.md)

This page consolidates ClickHouse data skipping index examples, showing how to declare each type, when to use them, and how to verify they're applied. All features work with [MergeTree-family tables](/docs/engines/table-engines/mergetree-family/mergetree).

**Index syntax:**

```
INDEX name expr TYPE type(...) [GRANULARITY N]
```

ClickHouse supports six skip index types:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Index Type Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **minmax** Tracks minimum and maximum values in each granule|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **set(N)** Stores up to N distinct values per granule|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | **text** Inverted index over tokenized string data for full text search|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **bloom\_filter([false\_positive\_rate])** Probabilistic filter for existence checks|  |  |  |  | | --- | --- | --- | --- | | **ngrambf\_v1** N-gram bloom filter for substring searches|  |  | | --- | --- | | **tokenbf\_v1** Token-based bloom filter for full-text searches | | | | | | | | | | | | | |

Each section provides examples with sample data and demonstrates how to verify index usage in query execution.

## MinMax index[​](#minmax-index "Direct link to MinMax index")

The`minmax` index is best for range predicates on loosely sorted data or columns correlated with `ORDER BY`.

```
-- Define in CREATE TABLE
CREATE TABLE events
(
  ts DateTime,
  user_id UInt64,
  value UInt32,
  INDEX ts_minmax ts TYPE minmax GRANULARITY 1
)
ENGINE=MergeTree
ORDER BY ts;

-- Or add later and materialize
ALTER TABLE events ADD INDEX ts_minmax ts TYPE minmax GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX ts_minmax;

-- Query that benefits from the index
SELECT count() FROM events WHERE ts >= now() - 3600;

-- Verify usage
EXPLAIN indexes = 1
SELECT count() FROM events WHERE ts >= now() - 3600;
```

See a [worked example](/docs/best-practices/use-data-skipping-indices-where-appropriate#example) with `EXPLAIN` and pruning.

## Set index[​](#set-index "Direct link to Set index")

Use the `set` index when local (per-block) cardinality is low; not helpful if each block has many distinct values.

```
ALTER TABLE events ADD INDEX user_set user_id TYPE set(100) GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX user_set;

SELECT * FROM events WHERE user_id IN (101, 202);

EXPLAIN indexes = 1
SELECT * FROM events WHERE user_id IN (101, 202);
```

A creation/materialization workflow and the before/after effect are shown in the [basic operation guide](/docs/optimize/skipping-indexes#basic-operation).

## Text index (text) for full text search[​](#textindex-for-full-text-search "Direct link to Text index (text) for full text search")

`text` is an inverted index over tokenized text data.
Designed specifically for full-text search workloads, enabling efficient and deterministic token and term lookup.
Recommended for natural language or large-scale text search use cases.

Just see [Full-text Search with Text Indexes](/docs/engines/table-engines/mergetree-family/textindexes) for more details and examples.

```
ALTER TABLE logs ADD INDEX msg_text msg TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE logs MATERIALIZE INDEX msg_text;

SELECT count() FROM logs WHERE hasAllTokens(msg, 'exception');
```

See a more complete observability example [here](/docs/use-cases/observability/schema-design#text-index-for-full-text-search) documentation.

The text index is totally deterministic and fully tunable in terms of tokenization and text processing at a cost of some more storage consumption compared with bloom filter–based indexes,

## Generic Bloom filter (scalar)[​](#generic-bloom-filter-scalar "Direct link to Generic Bloom filter (scalar)")

The `bloom_filter` index is good for "needle in a haystack" equality/IN membership. It accepts an optional parameter which is the false-positive rate (default 0.025).

```
ALTER TABLE events ADD INDEX value_bf value TYPE bloom_filter(0.01) GRANULARITY 3;
ALTER TABLE events MATERIALIZE INDEX value_bf;

SELECT * FROM events WHERE value IN (7, 42, 99);

EXPLAIN indexes = 1
SELECT * FROM events WHERE value IN (7, 42, 99);
```

## N-gram Bloom filter (ngrambf\_v1) for substring search *(Deprecated)*[​](#n-gram-bloom-filter-ngrambf-v1-for-substring-search "Direct link to n-gram-bloom-filter-ngrambf-v1-for-substring-search")

Note

The usage of `ngrambf_v1` indexes for full-text search is deprecated in ClickHouse versions `>= 26.2` in favor of `text` indexes (see [here](/docs/engines/table-engines/mergetree-family/textindexes) for further details).

The `ngrambf_v1` index splits strings into n-grams. It works well for `LIKE '%...%'` queries. It supports String/FixedString/Map (via mapKeys/mapValues), as well as tunable size, hash count, and seed. See the documentation for [N-gram bloom filter](/docs/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) for further details.

```
-- Create index for substring search
ALTER TABLE logs ADD INDEX msg_ngram msg TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1;
ALTER TABLE logs MATERIALIZE INDEX msg_ngram;

-- Substring search
SELECT count() FROM logs WHERE msg LIKE '%timeout%';

EXPLAIN indexes = 1
SELECT count() FROM logs WHERE msg LIKE '%timeout%';
```

[This guide](/docs/use-cases/observability/schema-design#text-index-for-full-text-search) shows practical examples and when to use token vs ngram.

**Parameter optimization helpers:**

The four ngrambf\_v1 parameters (n-gram size, bitmap size, hash functions, seed) significantly impact performance and memory usage. Use these functions to calculate optimal bitmap size and hash function count based on your expected n-gram volume and desired false positive rate:

```
CREATE FUNCTION bfEstimateFunctions AS
(total_grams, bits) -> round((bits / total_grams) * log(2));

CREATE FUNCTION bfEstimateBmSize AS
(total_grams, p_false) -> ceil((total_grams * log(p_false)) / log(1 / pow(2, log(2))));

-- Example sizing for 4300 ngrams, p_false = 0.0001
SELECT bfEstimateBmSize(4300, 0.0001) / 8 AS size_bytes;  -- ~10304
SELECT bfEstimateFunctions(4300, bfEstimateBmSize(4300, 0.0001)) AS k; -- ~13
```

See [parameter docs](/docs/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) for complete tuning guidance.

## Token Bloom filter (tokenbf\_v1) for word-based search *(Deprecated)*[​](#token-bloom-filter-tokenbf-v1-for-word-based-search "Direct link to token-bloom-filter-tokenbf-v1-for-word-based-search")

Note

The usage of `tokenbf_v1` indexes for full-text search is deprecated in ClickHouse versions `>= 26.2` in favor of `text` indexes (see [here](/docs/engines/table-engines/mergetree-family/textindexes) for further details).

`tokenbf_v1` indexes tokens separated by non-alphanumeric characters. You should use it with [`hasToken`](/docs/sql-reference/functions/string-search-functions#hasToken), `LIKE` word patterns or equals/IN. It supports `String`/`FixedString`/`Map` types.

See [Token bloom filter](/docs/engines/table-engines/mergetree-family/mergetree#token-bloom-filter) and [Bloom filter types](/docs/optimize/skipping-indexes#skip-index-types) pages for more details.

```
ALTER TABLE logs ADD INDEX msg_token lower(msg) TYPE tokenbf_v1(10000, 7, 7) GRANULARITY 1;
ALTER TABLE logs MATERIALIZE INDEX msg_token;

-- Word search (case-insensitive via lower)
SELECT count() FROM logs WHERE hasToken(lower(msg), 'exception');

EXPLAIN indexes = 1
SELECT count() FROM logs WHERE hasToken(lower(msg), 'exception');
```

See observability examples and guidance on token vs ngram [here](/docs/use-cases/observability/schema-design#text-index-for-full-text-search).