Understanding ClickHouse data skipping indexes

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

## Introduction[​](#introduction "Direct link to Introduction")

Many factors affect ClickHouse query performance. The critical element in most scenarios is whether ClickHouse can use the primary key when evaluating the query WHERE clause condition. Accordingly, selecting a primary key that applies to the most common query patterns is essential for effective table design.

Nevertheless, no matter how carefully tuned the primary key, there will inevitably be query use cases that can not efficiently use it. Users commonly rely on ClickHouse for time series type data, but they often wish to analyze that same data according to other business dimensions, such as customer id, website URL, or product number. In that case, query performance can be considerably worse because a full scan of each column value may be required to apply the WHERE clause condition. While ClickHouse is still relatively fast in those circumstances, evaluating millions or billions of individual values will cause "non-indexed" queries to execute much more slowly than those based on the primary key.

In a traditional relational database, one approach to this problem is to attach one or more "secondary" indexes to a table. This is a b-tree structure that permits the database to find all matching rows on disk in O(log(n)) time instead of O(n) time (a table scan), where n is the number of rows. However, this type of secondary index won't work for ClickHouse (or other column-oriented databases) because there are no individual rows on the disk to add to the index.

Instead, ClickHouse provides a different type of index, which in specific circumstances can significantly improve query speed. These structures are labeled "Skip" indexes because they enable ClickHouse to skip reading significant chunks of data that are guaranteed to have no matching values.

## Basic operation[​](#basic-operation "Direct link to Basic operation")

You can only employ Data Skipping Indexes on the MergeTree family of tables. Each data skipping has four primary arguments:

- Index name. The index name is used to create the index file in each partition. Also, it is required as a parameter when dropping or materializing the index.
- Index expression. The index expression is used to calculate the set of values stored in the index. It can be a combination of columns, simple operators, and/or a subset of functions determined by the index type.
- TYPE. The type of index controls the calculation that determines if it is possible to skip reading and evaluating each index block.
- GRANULARITY. Each indexed block consists of GRANULARITY granules. For example, if the granularity of the primary table index is 8192 rows, and the index granularity is 4, each indexed "block" will be 32768 rows.

When a user creates a data skipping index, there will be two additional files in each data part directory for the table.

- `skp_idx_{index_name}.idx`, which contains the ordered expression values
- `skp_idx_{index_name}.mrk2`, which contains the corresponding offsets into the associated data column files.

If some portion of the WHERE clause filtering condition matches the skip index expression when executing a query and reading the relevant column files, ClickHouse will use the index file data to determine whether each relevant block of data must be processed or can be bypassed (assuming that the block hasn't already been excluded by applying the primary key). To use a very simplified example, consider the following table loaded with predictable data.

```
CREATE TABLE skip_table
(
  my_key UInt64,
  my_value UInt64
)
ENGINE MergeTree primary key my_key
SETTINGS index_granularity=8192;

INSERT INTO skip_table SELECT number, intDiv(number,4096) FROM numbers(100000000);
```

When executing a simple query that doesn't use the primary key, all 100 million entries in the `my_value`
column are scanned:

```
SELECT * FROM skip_table WHERE my_value IN (125, 700)

┌─my_key─┬─my_value─┐
│ 512000 │      125 │
│ 512001 │      125 │
│    ... |      ... |
└────────┴──────────┘

8192 rows in set. Elapsed: 0.079 sec. Processed 100.00 million rows, 800.10 MB (1.26 billion rows/s., 10.10 GB/s.
```

Now add a very basic skip index:

```
ALTER TABLE skip_table ADD INDEX vix my_value TYPE set(100) GRANULARITY 2;
```

Normally skip indexes are only applied on newly inserted data, so just adding the index won't affect the above query.

To index already existing data, use this statement:

```
ALTER TABLE skip_table MATERIALIZE INDEX vix;
```

Rerun the query with the newly created index:

```
SELECT * FROM skip_table WHERE my_value IN (125, 700)

┌─my_key─┬─my_value─┐
│ 512000 │      125 │
│ 512001 │      125 │
│    ... |      ... |
└────────┴──────────┘

8192 rows in set. Elapsed: 0.051 sec. Processed 32.77 thousand rows, 360.45 KB (643.75 thousand rows/s., 7.08 MB/s.)
```

Instead of processing 100 million rows of 800 megabytes, ClickHouse has only read and analyzed 32768 rows of 360 kilobytes
-- four granules of 8192 rows each.

In a more visual form, this is how the 4096 rows with a `my_value` of 125 were read and selected, and how the following rows
were skipped without reading from disk:

You can access detailed information about skip index usage by enabling the trace when executing queries. From
clickhouse-client, set the `send_logs_level`:

```
SET send_logs_level='trace';
```

This will provide useful debugging information when trying to tune query SQL and table indexes. From the above
example, the debug log shows that the skip index dropped all but two granules:

```
<Debug> default.skip_table (933d4b2c-8cea-4bf9-8c93-c56e900eefd1) (SelectExecutor): Index `vix` has dropped 6102/6104 granules.
```

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

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

This lightweight index type requires no parameters. It stores the minimum and maximum values of the index expression
for each block (if the expression is a tuple, it separately stores the values for each member of the element
of the tuple). This type is ideal for columns that tend to be loosely sorted by value. This index type is usually the least expensive to apply during query processing.

This type of index only works correctly with a scalar or tuple expression -- the index will never be applied to expressions that return an array or map data type.

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

This lightweight index type accepts a single parameter of the max\_size of the value set per block (0 permits
an unlimited number of discrete values). This set contains all values in the block (or is empty if the number of values exceeds the max\_size). This index type works well with columns with low cardinality within each set of granules (essentially, "clumped together") but higher cardinality overall.

The cost, performance, and effectiveness of this index is dependent on the cardinality within blocks. If each block contains a large number of unique values, either evaluating the query condition against a large index set will be very expensive, or the index won't be applied because the index is empty due to exceeding max\_size.

### 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")