Understanding ClickHouse data skipping indexes

Accordingly, the natural impulse to try to speed up ClickHouse queries by simply adding an index to key
columns is often incorrect. This advanced functionality should only be used after investigating other alternatives, such as modifying the primary key (see [How to Pick a Primary Key](/docs/guides/best-practices/sparse-primary-indexes)), using projections, or using materialized views. Even when a data skipping index is appropriate, careful tuning both the index and the table
will often be necessary.

In most cases a useful skip index requires a strong correlation between the primary key and the targeted, non-primary column/expression.
If there is no correlation (as in the above diagram), the chances of the filtering condition being met by at least one of the rows in
the block of several thousand values is high and few blocks will be skipped. In contrast, if a range of values for the primary key (like time of
day) is strongly associated with the values in the potential index column (such as television viewer ages), then a minmax type of index
is likely to be beneficial. Note that it may be possible to increase this correlation when inserting data, either by including additional
columns in the sorting/ORDER BY key, or batching inserts in a way that values associated with the primary key are grouped on insert. For
example, all of the events for a particular site\_id could be grouped and inserted together by the ingest process, even if the primary key
is a timestamp containing events from a large number of sites. This will result in many granules that contains only a few site ids, so many
blocks could be skipped when searching by a specific site\_id value.

Another good candidate for a skip index is for high cardinality expressions where any one value is relatively sparse in the data. One example
might be an observability platform that tracks error codes in API requests. Certain error codes, while rare in the data, might be particularly
important for searches. A set skip index on the error\_code column would allow bypassing the vast majority of blocks that don't contain
errors and therefore significantly improve error focused queries.

Finally, the key best practice is to test, test, test. Again, unlike b-tree secondary indexes or inverted indexes for searching documents,
data skipping index behavior isn't easily predictable. Adding them to a table incurs a meaningful cost both on data ingest and on queries
that for any number of reasons don't benefit from the index. They should always be tested on real world type of data, and testing should
include variations of the type, granularity size and other parameters. Testing will often reveal patterns and pitfalls that aren't obvious from
thought experiments alone.

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

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

- [Introduction](#introduction)- [Basic operation](#basic-operation)- [Skip index types](#skip-index-types)
      - [minmax](#minmax)- [set](#set)- [text](#text)- [Bloom filter types](#bloom-filter-types)- [Skip index functions](#skip-index-functions)- [Skip index settings](#skip-index-settings)- [Skip index best practices](#skip-best-practices)- [Related docs](#related-docs)

Was this page helpful?