Understanding ClickHouse data skipping indexes

### text[​](#text "Direct link to text")

For workloads that involve natural language or free-form text search (e.g., searching words or phrases in large text columns), ClickHouse provides a **text index** (a real inverted index).
Text index supports efficient full-text search semantics and tokenized lookups. It is the recommended choice for full-text search queries because it provides deterministic token indexing and better performance for search functions such as `hasAnyToken`, `hasAllTokens` but also optimize all common text search functions.

See the text index documentation for details [here](/docs/engines/table-engines/mergetree-family/textindexes).

### Bloom filter types[​](#bloom-filter-types "Direct link to Bloom filter types")

A *Bloom filter* is a data structure that allows space-efficient testing of set membership at the cost of a slight chance of false positives. A false positive isn't a significant concern in the case of skip indexes because the only disadvantage is reading a few unnecessary blocks. However, the potential for false positives does mean that the indexed expression should be expected to be true, otherwise valid data may be skipped.

Because Bloom filters can more efficiently handle testing for a large number of discrete values, they can be appropriate for conditional expressions that produce more values to test. In particular, a Bloom filter index can be applied to arrays, where every value of the array is tested, and to maps, by converting either the keys or values to an array using the mapKeys or mapValues function.

There are three Data Skipping Index types based on Bloom filters:

- The basic **bloom\_filter** which takes a single optional parameter of the allowed "false positive" rate between 0 and 1 (if unspecified, .025 is used).
- The specialized **tokenbf\_v1** *(Deprecated)*). It takes three parameters, all related to tuning the bloom filter used: (1) the size of the filter in bytes (larger filters have fewer false positives, at some cost in storage), (2) number of hash functions applied (again, more hash filters reduce false positives), and (3) the seed for the bloom filter hash functions. See the calculator [here](https://hur.st/bloomfilter/) for more detail on how these parameters affect bloom filter functionality.
  This index works only with String, FixedString, and Map datatypes. The input expression is split into character sequences separated by non-alphanumeric characters. For example, a column value of `This is a candidate for a "full text" search` will contain the tokens `This` `is` `a` `candidate` `for` `full` `text` `search`. It is intended for use in LIKE, EQUALS, IN, hasToken() and similar searches for words and other values within longer strings. For example, one possible use might be searching for a small number of class names or line numbers in a column of free form application log lines.
- The specialized **ngrambf\_v1** *(Deprecated)*. This index functions the same as the token index. It takes one additional parameter before the Bloom filter settings, the size of the ngrams to index. An ngram is a character string of length `n` of any characters, so the string `A short string` with an ngram size of 4 would be indexed as:

  ```
  'A sh', ' sho', 'shor', 'hort', 'ort ', 'rt s', 't st', ' str', 'stri', 'trin', 'ring'
  ```

This index can also be useful for text searches, particularly languages without word breaks, such as Chinese.

> For full-text search workloads, the dedicated **text index** (see [Text index for full-text search](/docs/engines/table-engines/mergetree-family/textindexes)) is recommended over the deprecated *tokenbf\_v1* or *ngrambf\_v1* indexes.
> The text index provides a true inverted index with better search performance, more predictable behavior, and greater flexibility and performance compared with token-based Bloom filter indexes.

## Skip index functions[​](#skip-index-functions "Direct link to Skip index functions")

The core purpose of data-skipping indexes is to limit the amount of data analyzed by popular queries. Given the analytic nature of ClickHouse data, the pattern of those queries in most cases includes functional expressions. Accordingly, skip indexes must interact correctly with common functions to be efficient. This can happen either when:

- data is inserted and the index is defined as a functional expression (with the result of the expression stored in the index files), or
- the query is processed and the expression is applied to the stored index values to determine whether to exclude the block.

Each type of skip index works on a subset of available ClickHouse functions appropriate to the index implementation listed
[here](/docs/engines/table-engines/mergetree-family/mergetree#functions-support). In general, set indexes and Bloom filter based indexes (another type of set index) are both unordered and therefore don't work with ranges. In contrast, minmax indexes work particularly well with ranges since determining whether ranges intersect is very fast. The efficacy of partial match functions LIKE, startsWith, endsWith, and hasToken depend on the index type used, the index expression, and the particular shape of the data.

## Skip index settings[​](#skip-index-settings "Direct link to Skip index settings")

There are two available settings that apply to skip indexes.

- **use\_skip\_indexes** (0 or 1, default 1). Not all queries can efficiently use skip indexes. If a particular filtering condition is
  likely to include most granules, applying the data skipping index incurs an unnecessary, and sometimes significant, cost. Set the value to
  0 for queries that are unlikely to benefit from any skip indexes.
- **force\_data\_skipping\_indices** (comma separated list of index names). This setting can be used to prevent some kinds of inefficient
  queries. In circumstances where querying a table is too expensive unless a skip index is used, using this setting with one or more index
  names will return an exception for any query that doesn't use the listed index. This would prevent poorly written queries from
  consuming server resources.

## Skip index best practices[​](#skip-best-practices "Direct link to Skip index best practices")

Skip indexes aren't intuitive, especially for those accustomed to secondary row-based indexes from the RDMS realm or inverted indexes from document stores. To get any benefit, applying a ClickHouse data skipping index must avoid enough granule reads to offset the cost of calculating the index. Critically, if a value occurs even once in an indexed block, it means the entire block must be read into memory and evaluated, and the index cost has been needlessly incurred.

Consider the following data distribution:

Assume the primary/order by key is `timestamp`, and there is an index on `visitor_id`. Consider the following query:

```
SELECT timestamp, url FROM table WHERE visitor_id = 1001`
```

A traditional secondary index would be very advantageous with this kind of data distribution. Instead of reading all 32768 rows to find
the 5 rows with the requested visitor\_id, the secondary index would include just five row locations, and only those five rows would be
read from disk. The exact opposite is true for a ClickHouse data skipping index. All 32768 values in the `visitor_id` column will be tested
regardless of the type of skip index.

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.