Data skipping index examples

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

## Add indexes during CREATE TABLE (multiple examples)[​](#add-indexes-during-create-table-multiple-examples "Direct link to Add indexes during CREATE TABLE (multiple examples)")

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

```
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;
```

## Materializing on existing data and verifying[​](#materializing-on-existing-data-and-verifying "Direct link to Materializing on existing data and verifying")

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

```
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](/docs/best-practices/use-data-skipping-indices-where-appropriate#example) demonstrates EXPLAIN output structure and pruning counts.

## When to use and when to avoid skipping indexes[​](#when-use-and-when-to-avoid "Direct link to When to use and when to avoid skipping indexes")

**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

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.

## Temporarily ignore or force indexes[​](#temporarily-ignore-or-force-indexes "Direct link to Temporarily ignore or force indexes")

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`](/docs/operations/settings/settings#ignore_data_skipping_indices).

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

## Notes and caveats[​](#notes-and-caveats "Direct link to Notes and caveats")

- Skipping indexes are only supported on [MergeTree-family tables](/docs/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.

## Related docs[​](#related-docs "Direct link to Related docs")

- [Data skipping index guide](/docs/optimize/skipping-indexes)
- [Best practices guide](/docs/best-practices/use-data-skipping-indices-where-appropriate)
- [Manipulating data skipping indices](/docs/sql-reference/statements/alter/skipping-index)
- [System table information](/docs/operations/system-tables/data_skipping_indices)

- [MinMax index](#minmax-index)- [Set index](#set-index)- [Text index (text) for full text search](#textindex-for-full-text-search)- [Generic Bloom filter (scalar)](#generic-bloom-filter-scalar)- [N-gram Bloom filter (ngrambf\_v1) for substring search *(Deprecated)*](#n-gram-bloom-filter-ngrambf-v1-for-substring-search)- [Token Bloom filter (tokenbf\_v1) for word-based search *(Deprecated)*](#token-bloom-filter-tokenbf-v1-for-word-based-search)- [Add indexes during CREATE TABLE (multiple examples)](#add-indexes-during-create-table-multiple-examples)- [Materializing on existing data and verifying](#materializing-on-existing-data-and-verifying)- [When to use and when to avoid skipping indexes](#when-use-and-when-to-avoid)- [Temporarily ignore or force indexes](#temporarily-ignore-or-force-indexes)- [Notes and caveats](#notes-and-caveats)- [Related docs](#related-docs)

Was this page helpful?