Full-text search with text indexes

To understand a query utilizes direct read, run the query with `EXPLAIN PLAN actions = 1`.
As an example, a query with disabled direct read

```
EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read
         use_skip_indexes_on_data_read = 1;
```

returns

```
[...]
Filter ((WHERE + Change column names to column identifiers))
Filter column: hasToken(__table1.col, 'some_token'_String) (removed)
Actions: INPUT : 0 -> col String : 0
         COLUMN Const(String) -> 'some_token'_String String : 1
         FUNCTION hasToken(col :: 0, 'some_token'_String :: 1) -> hasToken(__table1.col, 'some_token'_String) UInt8 : 2
[...]
```

whereas the same query run with `query_plan_direct_read_from_text_index = 1`

```
EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 1, -- enable direct read
         use_skip_indexes_on_data_read = 1;
```

returns

```
[...]
Expression (Before GROUP BY)
Positions:
  Filter
  Filter column: __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 (removed)
  Actions: INPUT :: 0 -> __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 UInt8 : 0
[...]
```

The second EXPLAIN PLAN output contains a virtual column `__text_index_<index_name>_<function_name>_<id>`.
If this column is present, then direct read is used.

If the WHERE filter clause only contains text search functions, the query can avoid reading the column data entirely and has the greatest performance benefit by direct read.
However, even if the text column is accessed elsewhere in the query, direct read will still provide performance improvement.

**Direct read as a hint**

Direct read as a hint is based on the same principles as normal direct read, but instead adds an additional filter build from the text index data without removing the underlying text column.
It is used for functions when reading only from the text index would produce false positives.

Supported functions are: `like`, `startsWith`, `endsWith`, `equals`, `has`, `mapContainsKey`, and `mapContainsValue`.

The additional filter can provide additional selectivity to restrict the result set in combination with other filters further, helping to reduce the amount of data read from other columns.

Direct read as a hint is controlled by setting [query\_plan\_text\_index\_add\_hint](/docs/operations/settings/settings#query_plan_text_index_add_hint) (enabled by default).

Example of query without hint:

```
EXPLAIN actions = 1
SELECT count()
FROM table
WHERE (col LIKE '%some-token%') AND (d >= today())
SETTINGS use_skip_indexes_on_data_read = 1, query_plan_text_index_add_hint = 0
FORMAT TSV
```

returns

```
[...]
Prewhere filter column: and(like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]
```

whereas the same query run with `query_plan_text_index_add_hint = 1`

```
EXPLAIN actions = 1
SELECT count()
FROM table
WHERE col LIKE '%some-token%'
SETTINGS use_skip_indexes_on_data_read = 1, query_plan_text_index_add_hint = 1
```

returns

```
[...]
Prewhere filter column: and(__text_index_idx_col_like_d306f7c9c95238594618ac23eb7a3f74, like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]
```

In the second EXPLAIN PLAN output, you can see that an additional conjunct (`__text_index_...`) has been added to the filter condition.
Thanks to the [PREWHERE](/docs/sql-reference/statements/select/prewhere) optimization, the filter condition is broken down into three separate conjuncts, which are applied in order of increasing computational complexity.
For this query, the application order is `__text_index_...`, then `greaterOrEquals(...)`, and finally `like(...)`.
This ordering enables skipping even more data granules than the granules skipped by the text index and the original filter, before reading the heavy columns used in the query after `WHERE` clause further reducing the amount of data to read.

### LIKE/ILIKE queries[​](#like-ilike-queries-perf "Direct link to LIKE/ILIKE queries")

When a LIKE/ILIKE query pattern is `%<alpha-numeric-characters-without-spaces>%` and the text index tokenizer is `splitByNonAlpha`, ClickHouse leverages the inverted index to speed up LIKE/ILIKE queries significantly. To achieve that, ClickHouse scans the inverted index dictionary instead of a full-table scan to find the matching pattern.

When the optimization is enabled, LIKE/ILIKE queries should be significantly faster than a full-table scan. However, when the pattern matches most dictionary tokens, the performance can be worse compared to a full-table scan. Luckily, there is a fallback mechanism to prevent that.

The optimization is controlled by a setting:

- [use\_text\_index\_like\_evaluation\_by\_dictionary\_scan](/docs/operations/settings/settings#use_text_index_like_evaluation_by_dictionary_scan)

The fallback mechanism is controlled by two settings:

- [text\_index\_like\_min\_pattern\_length](/docs/operations/settings/settings#text_index_like_min_pattern_length)
- [text\_index\_like\_max\_postings\_to\_read](/docs/operations/settings/settings#text_index_like_max_postings_to_read)

This optimization supports only functions `like` and `ilike`.

### Caching[​](#caching "Direct link to Caching")

Different caches are available to buffer parts of the text index in memory (see section [Implementation Details](#implementation)):
Currently, there are caches for the deserialized headers, tokens, and posting lists of the text index to reduce I/O.
They can be enabled via settings [use\_text\_index\_header\_cache](/docs/operations/settings/settings#use_text_index_header_cache), [use\_text\_index\_tokens\_cache](/docs/operations/settings/settings#use_text_index_tokens_cache), and [use\_text\_index\_postings\_cache](/docs/operations/settings/settings#use_text_index_postings_cache).

By default, all caches are disabled.
To clear the caches, use statement [SYSTEM CLEAR TEXT INDEX CACHES](/docs/sql-reference/statements/system#drop-text-index-caches)

Please refer the following server settings to configure the caches.

#### Tokens cache settings[​](#caching-tokens "Direct link to Tokens cache settings")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [text\_index\_tokens\_cache\_policy](/docs/operations/server-configuration-parameters/settings#text_index_tokens_cache_policy) Text index tokens cache policy name.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [text\_index\_tokens\_cache\_size](/docs/operations/server-configuration-parameters/settings#text_index_tokens_cache_size) Maximum cache size in bytes.|  |  |  |  | | --- | --- | --- | --- | | [text\_index\_tokens\_cache\_max\_entries](/docs/operations/server-configuration-parameters/settings#text_index_tokens_cache_max_entries) Maximum number of deserialized tokens in cache.|  |  | | --- | --- | | [text\_index\_tokens\_cache\_size\_ratio](/docs/operations/server-configuration-parameters/settings#text_index_tokens_cache_size_ratio) The size of the protected queue in the text index tokens cache relative to the cache's total size. | | | | | | | | | |

#### Header cache settings[​](#caching-header "Direct link to Header cache settings")