Use data skipping indices where appropriate

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

Data skipping indices should be considered when previous best practices have been followed i.e. types are optimized, a good primary key has been selected and materialized views have been exploited. If you're new to skipping indices, [this guide](/docs/optimize/skipping-indexes) is a good place to start.

These types of indices can be used to accelerate query performance if used carefully with an understanding of how they work.

ClickHouse provides a powerful mechanism called **data skipping indices** that can dramatically reduce the amount of data scanned during query execution — particularly when the primary key isn't helpful for a specific filter condition. Unlike traditional databases that rely on row-based secondary indexes (like B-trees), ClickHouse is a column-store and doesn't store row locations in a way that supports such structures. Instead, it uses skip indexes, which help it avoid reading blocks of data guaranteed not to match a query's filtering conditions.

Skip indexes work by storing metadata about blocks of data — such as min/max values, value sets, or Bloom filter representations — and using this metadata during query execution to determine which data blocks can be skipped entirely. They apply only to the [MergeTree family](/docs/engines/table-engines/mergetree-family/mergetree) of table engines and are defined using an expression, an index type, a name, and a granularity that defines the size of each indexed block. These indexes are stored alongside the table data and are consulted when the query filter matches the index expression.

There are several types of data skipping indexes, each suited to different types of queries and data distributions:

- **minmax**: Tracks the minimum and maximum value of an expression per block. Ideal for range queries on loosely sorted data.
- **set(N)**: Tracks a set of values up to a specified size N for each block. Effective on columns with low cardinality per blocks.
- **text**: Builds an inverted index over tokenized string data, enabling efficient and deterministic full-text search. Recommended for natural language or large free-form text columns where precise token lookup and scalable multi-term search are required, instead of approximate Bloom filter–based approaches.
- **bloom\_filter**: Probabilistically determines if a value exists in a block, allowing fast approximate filtering for set membership. Effective for optimizing queries looking for the “needle in a haystack”, where a positive match is needed.
- **tokenbf\_v1 / ngrambf\_v1**: *(Deprecated)* Specialized Bloom filter variants designed for searching tokens or character sequences in strings — particularly useful for log data or text search use cases. Deprecated in ClickHouse versions >= 26.2 in favor of text indexes.

While powerful, skip indexes must be used with care. They only provide benefit when they eliminate a meaningful number of data blocks, and can actually introduce overhead if the query or data structure doesn't align. If even a single matching value exists in a block, that entire block must still be read.

**Effective skip index usage often depends on a strong correlation between the indexed column and the table's primary key, or inserting data in a way that groups similar values together.**

In general, data skipping indices are best applied after ensuring proper primary key design and type optimization. They're particularly useful for:

- Columns with high overall cardinality but low cardinality within a block.
- Rare values that are critical for search (e.g. error codes, specific IDs).
- Cases where filtering occurs on non-primary key columns with localized distribution.

Always:

1. Test skip indexes on real data with realistic queries. Try different index types and granularity values.
2. Evaluate their impact using tools like send\_logs\_level='trace' and `EXPLAIN indexes=1` to view index effectiveness.
3. Always evaluate the size of an index and how it is impacted by granularity. Reducing granularity size often will improve performance to a point, resulting in more granules being filtered and needing to be scanned. However, as index size increases with lower granularity, performance can also degrade. Measure the performance and index size for various granularity data points. This is particularly pertinent on bloom filter indexes.

**When used appropriately, skip indexes can provide a substantial performance boost — when used blindly, they can add unnecessary cost.**

For a more detailed guide on Data Skipping Indices see [here](/docs/sql-reference/statements/alter/skipping-index).

## Example[​](#example "Direct link to Example")

Consider the following optimized table. This contains Stack Overflow data with a row per post.

```
CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))
```

This table is optimized for queries which filter and aggregate by post type and date. Suppose we wished to count the number of posts with over 10,000,000 views published after 2009.

```
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─count()─┐
│     5   │
└─────────┘

1 row in set. Elapsed: 0.720 sec. Processed 59.55 million rows, 230.23 MB (82.66 million rows/s., 319.56 MB/s.)
```

This query is able to exclude some of the rows (and granules) using the primary index. However, the majority of rows still need to be read as indicated by the above response and the following `EXPLAIN indexes = 1`:

```
EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)
LIMIT 1