MergeTree table engine

The index declaration is in the columns section of the `CREATE` query.

```
INDEX index_name expr TYPE type(...) [GRANULARITY granularity_value]
```

For tables from the `*MergeTree` family, data skipping indices can be specified.

These indices aggregate some information about the specified expression on blocks, which consist of `granularity_value` granules (the size of the granule is specified using the `index_granularity` setting in the table engine). Then these aggregates are used in `SELECT` queries for reducing the amount of data to read from the disk by skipping big blocks of data where the `where` query cannot be satisfied.

The `GRANULARITY` clause can be omitted, the default value of `granularity_value` is 1.

**Example**

```
CREATE TABLE table_name
(
    u64 UInt64,
    i32 Int32,
    s String,
    ...
    INDEX idx1 u64 TYPE bloom_filter GRANULARITY 3,
    INDEX idx2 u64 * i32 TYPE minmax GRANULARITY 3,
    INDEX idx3 u64 * length(s) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...
```

Indices from the example can be used by ClickHouse to reduce the amount of data to read from disk in the following queries:

```
SELECT count() FROM table WHERE u64 == 10;
SELECT count() FROM table WHERE u64 * i32 >= 1234
SELECT count() FROM table WHERE u64 * length(s) == 1234
```

Data skipping indexes can also be created on composite columns:

```
-- on columns of type Map:
INDEX map_key_index mapKeys(map_column) TYPE bloom_filter
INDEX map_value_index mapValues(map_column) TYPE bloom_filter

-- on columns of type JSON:
INDEX json_paths_index JSONAllPaths(json_column) TYPE bloom_filter

-- on columns of type Tuple:
INDEX tuple_1_index tuple_column.1 TYPE bloom_filter
INDEX tuple_2_index tuple_column.2 TYPE bloom_filter

-- on columns of type Nested:
INDEX nested_1_index col.nested_col1 TYPE bloom_filter
INDEX nested_2_index col.nested_col2 TYPE bloom_filter
```

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

The `MergeTree` table engine supports the following types of skip indexes.
For more information on how skip indexes can be used for performance optimization
see ["Understanding ClickHouse data skipping indexes"](/docs/optimize/skipping-indexes).

- [`MinMax`](#minmax) index
- [`Set`](#set) index
- [`bloom_filter`](#bloom-filter) index
- [`ngrambf_v1`](#n-gram-bloom-filter) index *(Deprecated)*
- [`tokenbf_v1`](#token-bloom-filter) index *(Deprecated)*
- [`text`](#text) index
- [`vector_similarity`](#vector-similarity) index

#### MinMax skip index[​](#minmax "Direct link to MinMax skip index")

For each index granule, the minimum and maximum values of an expression are stored.
(If the expression is of type `tuple`, it stores the minimum and maximum for each tuple element.)

```
minmax
```

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

For each index granule at most `max_rows` many unique values of the specified expression are stored.
`max_rows = 0` means "store all unique values".

```
set(max_rows)
```

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

For each index granule stores a [bloom filter](https://en.wikipedia.org/wiki/Bloom_filter) for the specified columns.

```
bloom_filter([false_positive_rate])
```

The `false_positive_rate` parameter can take on a value between 0 and 1 (by default: `0.025`) and specifies the probability of generating a positive (which increases the amount of data to be read).

The following data types are supported:

- `(U)Int*`
- `Float*`
- `Enum`
- `Date`
- `DateTime`
- `String`
- `FixedString`
- `Array`
- `LowCardinality`
- `Nullable`
- `UUID`
- `Map`

Map data type: specifying index creation with keys or values

For the `Map` data type, the client can specify if the index should be created for keys or for values using the [`mapKeys`](/docs/sql-reference/functions/tuple-map-functions#mapKeys) or [`mapValues`](/docs/sql-reference/functions/tuple-map-functions#mapValues) functions.

JSON data type: indexing JSON paths

For the [`JSON`](/docs/sql-reference/data-types/newjson) data type, a bloom filter index can be created on the set of paths using the [`JSONAllPaths`](/docs/sql-reference/functions/json-functions#JSONAllPaths) function. This allows skipping granules where a queried JSON path is absent. See [Data skipping indexes for JSON](/docs/sql-reference/data-types/newjson#data-skipping-indexes-for-json) for details.

#### N-gram bloom filter *(Deprecated)*[​](#n-gram-bloom-filter "Direct link to n-gram-bloom-filter")

Note

With general availability (GA) of the `text` index starting from ClickHouse version 26.2, the `ngrambf_v1` index is no longer recommended for full text search.

See page ["Full-text search with text indexes"](/docs/engines/table-engines/mergetree-family/textindexes) for details.

For each index granule stores a [bloom filter](https://en.wikipedia.org/wiki/Bloom_filter) for the [n-grams](https://en.wikipedia.org/wiki/N-gram) of the specified columns.

```
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
```

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `n` ngram size|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `size_of_bloom_filter_in_bytes` Bloom filter size in bytes. You can use a large value here, for example, `256` or `512`, because it can be compressed well).| `number_of_hash_functions` The number of hash functions used in the bloom filter.|  |  | | --- | --- | | `random_seed` Seed for the bloom filter hash functions. | | | | | | | | | |

This index only works with the following data types:

- [`String`](/docs/sql-reference/data-types/string)
- [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- [`Map`](/docs/sql-reference/data-types/map)

To estimate the parameters of `ngrambf_v1`, you can use the following [User Defined Functions (UDFs)](/docs/sql-reference/statements/create/function).

```
CREATE FUNCTION bfEstimateFunctions [ON CLUSTER cluster]
AS
(total_number_of_all_grams, size_of_bloom_filter_in_bits) -> round((size_of_bloom_filter_in_bits / total_number_of_all_grams) * log(2));

CREATE FUNCTION bfEstimateBmSize [ON CLUSTER cluster]
AS
(total_number_of_all_grams,  probability_of_false_positives) -> ceil((total_number_of_all_grams * log(probability_of_false_positives)) / log(1 / pow(2, log(2))));

CREATE FUNCTION bfEstimateFalsePositive [ON CLUSTER cluster]
AS
(total_number_of_all_grams, number_of_hash_functions, size_of_bloom_filter_in_bytes) -> pow(1 - exp(-number_of_hash_functions/ (size_of_bloom_filter_in_bytes / total_number_of_all_grams)), number_of_hash_functions);

CREATE FUNCTION bfEstimateGramNumber [ON CLUSTER cluster]
AS
(number_of_hash_functions, probability_of_false_positives, size_of_bloom_filter_in_bytes) -> ceil(size_of_bloom_filter_in_bytes / (-number_of_hash_functions / log(1 - exp(log(probability_of_false_positives) / number_of_hash_functions))))
```

To use these functions, you need to specify at least two parameters:

- `total_number_of_all_grams`
- `probability_of_false_positives`

For example, there are `4300` ngrams in the granule and you expect false positives to be less than `0.0001`.
The other parameters can then be estimated by executing the following queries:

```
--- estimate number of bits in the filter
SELECT bfEstimateBmSize(4300, 0.0001) / 8 AS size_of_bloom_filter_in_bytes;

┌─size_of_bloom_filter_in_bytes─┐
│                         10304 │
└───────────────────────────────┘

--- estimate number of hash functions
SELECT bfEstimateFunctions(4300, bfEstimateBmSize(4300, 0.0001)) as number_of_hash_functions

┌─number_of_hash_functions─┐
│                       13 │
└──────────────────────────┘
```

Of course, you can also use those functions to estimate parameters for other conditions.
The functions above refer to the bloom filter calculator [here](https://hur.st/bloomfilter).

#### Token bloom filter[​](#token-bloom-filter "Direct link to Token bloom filter")

Note