> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Consolidated Skip Index Examples

# Data skipping index examples

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](/reference/engines/table-engines/mergetree-family/mergetree).

**Index syntax:**

```sql theme={null}
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.

<h2 id="minmax-index">
  MinMax index
</h2>

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

```sql theme={null}
-- 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](/concepts/best-practices/using-data-skipping-indices#example) with `EXPLAIN` and pruning.

<h2 id="set-index">
  Set index
</h2>

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

```sql theme={null}
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](/concepts/features/performance/skip-indexes/skipping-indexes#basic-operation).

<h2 id="textindex-for-full-text-search">
  Text index (text) for full text search
</h2>

`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](/reference/engines/table-engines/mergetree-family/textindexes) for more details and examples.

```sql theme={null}
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](/guides/use-cases/observability/build-your-own/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,

<h2 id="generic-bloom-filter-scalar">
  Generic Bloom filter (scalar)
</h2>

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).

```sql theme={null}
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);
```

<h2 id="n-gram-bloom-filter-ngrambf-v1-for-substring-search">
  N-gram Bloom filter (ngrambf\_v1) for substring search *(Deprecated)*
</h2>

<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](/reference/engines/table-engines/mergetree-family/textindexes) for further details).
</Note>

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](/reference/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) for further details.

```sql theme={null}
-- 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](/guides/use-cases/observability/build-your-own/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:

```sql theme={null}
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](/reference/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) for complete tuning guidance.

<h2 id="token-bloom-filter-tokenbf-v1-for-word-based-search">
  Token Bloom filter (tokenbf\_v1) for word-based search *(Deprecated)*
</h2>

<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](/reference/engines/table-engines/mergetree-family/textindexes) for further details).
</Note>

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

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

```sql theme={null}
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](/guides/use-cases/observability/build-your-own/schema-design#text-index-for-full-text-search).

<h2 id="add-indexes-during-create-table-multiple-examples">
  Add indexes during CREATE TABLE (multiple examples)
</h2>

Skipping indexes also support composite expressions and `Map`/`Tuple`/`Nested` types. This is demonstrated in the example below:

```sql theme={null}
CREATE TABLE t
(
  u64 UInt64,
  s String,
  m Map(String, String),

  INDEX idx_bf u64 TYPE bloom_filter(0.01) GRANULARITY 3,
  INDEX idx_minmax u64 TYPE minmax GRANULARITY 1,
  INDEX idx_set u64 * length(s) TYPE set(1000) GRANULARITY 4,
  INDEX idx_ngram s TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1,
  INDEX idx_token mapKeys(m) TYPE tokenbf_v1(10000, 7, 7) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY u64;
```

<h2 id="materializing-on-existing-data-and-verifying">
  Materializing on existing data and verifying
</h2>

You can add an index to existing data parts using `MATERIALIZE`, and inspect pruning with `EXPLAIN` or trace logs, as shown below:

```sql theme={null}
ALTER TABLE t MATERIALIZE INDEX idx_bf;

EXPLAIN indexes = 1
SELECT count() FROM t WHERE u64 IN (123, 456);

-- Optional: detailed pruning info
SET send_logs_level = 'trace';
```

This [worked minmax example](/concepts/best-practices/using-data-skipping-indices#example) demonstrates EXPLAIN output structure and pruning counts.

<h2 id="when-use-and-when-to-avoid">
  When to use and when to avoid skipping indexes
</h2>

**Use skip indexes when:**

* Filter values are sparse within data blocks
* Strong correlation exists with `ORDER BY` columns or data ingestion patterns group similar values together
* Performing text searches on large log datasets (`ngrambf_v1`/`tokenbf_v1` types)

**Avoid skip indexes when:**

* Most blocks likely contain at least one matching value (blocks will be read regardless)
* Filtering on high-cardinality columns with no correlation to data ordering

<Info>
  **Important considerations**

  If a value appears even once in a data block, ClickHouse must read the entire block. Test indexes with realistic datasets and adjust granularity and type-specific parameters based on actual performance measurements.
</Info>

<h2 id="temporarily-ignore-or-force-indexes">
  Temporarily ignore or force indexes
</h2>

Disable specific indexes by name for individual queries during testing and troubleshooting. Settings also exist to force index usage when needed. See [`ignore_data_skipping_indices`](/reference/settings/session-settings#ignore_data_skipping_indices).

```sql theme={null}
-- Ignore an index by name
SELECT * FROM logs
WHERE hasToken(lower(msg), 'exception')
SETTINGS ignore_data_skipping_indices = 'msg_token';
```

<h2 id="notes-and-caveats">
  Notes and caveats
</h2>

* Skipping indexes are only supported on [MergeTree-family tables](/reference/engines/table-engines/mergetree-family/mergetree); pruning happens at the granule/block level.
* Bloom-filter-based indexes are probabilistic (false positives cause extra reads but won't skip valid data).
* Bloom filters and other skip indexes should be validated with `EXPLAIN` and tracing; adjust granularity to balance pruning vs. index size.

<h2 id="related-docs">
  Related docs
</h2>

* [Data skipping index guide](/concepts/features/performance/skip-indexes/skipping-indexes)
* [Best practices guide](/concepts/best-practices/using-data-skipping-indices)
* [Manipulating data skipping indices](/reference/statements/alter/skipping-index)
* [System table information](/reference/system-tables/data_skipping_indices)