Busca de texto completo com índices de texto

Encontre rapidamente termos de busca em textos.

Índices de texto (também conhecidos como índices invertidos) permitem realizar buscas de texto completo com rapidez em dados textuais. Um índice de texto armazena um mapeamento de tokens para os números das linhas que contêm cada token. Os tokens são gerados por um processo chamado tokenização. Por exemplo, o tokenizador padrão do ClickHouse converte a frase em inglês “The cat likes mice.” nos tokens [“The”, “cat”, “likes”, “mice”].

Como exemplo, considere uma tabela com uma única coluna e três linhas

1: The cat likes mice.
2: Mice are afraid of dogs.
3: I have two dogs and a cat.

Os tokens correspondentes são:

1: The, cat, likes, mice
2: Mice, are, afraid, of, dogs
3: I, have, two, dogs, and, a, cat

Normalmente, preferimos pesquisar sem diferenciar maiúsculas de minúsculas; por isso, convertemos os tokens para minúsculas:

1: the, cat, likes, mice
2: mice, are, afraid, of, dogs
3: i, have, two, dogs, and, a, cat

Também removeremos palavras de preenchimento, como “I”, “the” e “and”, pois elas aparecem em quase todas as linhas:

1: cat, likes, mice
2: mice, afraid, dogs
3: have, two, dogs, cat

Um índice de texto contém (conceitualmente) estas informações:

afraid : [2]
cat    : [1, 3]
dogs   : [2, 3]
have   : [3]
likes  : [1]
mice   : [1]
two    : [3]

Dado um token de busca, essa estrutura de índice permite localizar rapidamente todas as linhas correspondentes.

​

Criando um índice de texto

Os índices de texto estão disponíveis de forma geral (GA) no ClickHouse 26.2 e versões mais recentes. Nessas versões, não é necessário configurar nenhuma opção especial para usar o índice de texto. Recomendamos fortemente usar versões do ClickHouse >= 26.2 em produção.

Os índices de texto podem ser usados com qualquer versão do ClickHouse >= 26.2, independentemente da configuração de compatibilidade.

Para criar um índice de texto, use a seguinte sintaxe:

Query

CREATE TABLE table
(
    key UInt64,
    str String,
    INDEX text_idx(str) TYPE text(
                                -- Parâmetros obrigatórios:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | asciiCJK
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Parâmetros opcionais:
                                [, preprocessor = expression(str)]
                                -- Parâmetros avançados opcionais:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )
)
ENGINE = MergeTree
ORDER BY key

Índices de texto podem ser definidos em colunas dos seguintes tipos:

• String e FixedString,
• Array(String) e Array(FixedString),
• Map (por meio das funções mapKeys e mapValues), e
• JSON (por meio das funções JSONAllPaths e JSONAllValues).

Colunas do tipo Nullable(T) e LowCardinality() também são suportadas, incluindo Array(Nullable(String or FixedString)).

Como alternativa, para adicionar um índice de texto a uma tabela existente:

Query

ALTER TABLE table
    ADD INDEX text_idx(str) TYPE text(
                                -- Parâmetros obrigatórios:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | asciiCJK
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Parâmetros opcionais:
                                [, preprocessor = expression(str)]
                                -- Parâmetros avançados opcionais:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )

Se você adicionar um índice a uma tabela existente, recomendamos materializar o índice para as partes existentes da tabela (caso contrário, a busca nas partes sem índice recorrerá a varreduras lentas por força bruta).

Query

ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;

Para remover um índice de texto, execute

Query

ALTER TABLE table DROP INDEX text_idx;

Argumento do tokenizador (obrigatório). O argumento tokenizer especifica o tokenizador:

• splitByNonAlpha divide strings em caracteres ASCII não alfanuméricos (consulte a função splitByNonAlpha).
• splitByString(S) divide strings usando determinadas strings separadoras S definidas pelo usuário (consulte a função splitByString). Os separadores podem ser especificados usando um parâmetro opcional; por exemplo, tokenizer = splitByString([', ', '; ', '\n', '\\']). Observe que cada string pode ser composta por vários caracteres (', ' no exemplo). A lista padrão de separadores, se não for especificada explicitamente (por exemplo, tokenizer = splitByString), é um único espaço em branco [' '].
• asciiCJK divide strings em tokens usando regras de limite de palavras do Unicode (semelhantes a Unicode Text Segmentation (UAX #29)). Caracteres ASCII alfanuméricos e sublinhados formam tokens com conectores (ASCII : para letras, . e ' para caracteres do mesmo tipo). Caracteres Unicode não ASCII, incluindo caracteres CJK, tornam-se tokens de um único caractere.
• ngrams(N) divide strings em n-grams de tamanho fixo N (consulte a função ngrams). O comprimento do ngram pode ser especificado usando um parâmetro inteiro opcional entre 1 e 8; por exemplo, tokenizer = ngrams(3). O tamanho padrão do ngram, se não for especificado explicitamente (por exemplo, tokenizer = ngrams), é 3.
• sparseGrams(min_length, max_length, min_cutoff_length) divide strings em n-grams de comprimento variável com no mínimo min_length e no máximo max_length caracteres (inclusive) (consulte a função sparseGrams). A menos que sejam especificados explicitamente, min_length e max_length assumem, por padrão, os valores 3 e 100. Se o parâmetro min_cutoff_length for fornecido, somente n-grams com comprimento maior ou igual a min_cutoff_length serão retornados. Em comparação com ngrams(N), o tokenizador sparseGrams produz N-grams de comprimento variável, permitindo uma representação mais flexível do texto original. Por exemplo, tokenizer = sparseGrams(3, 5, 4) gera internamente 3-, 4- e 5-grams a partir da string de entrada, mas apenas os 4- e 5-grams são retornados.
• array não realiza tokenização, ou seja, cada valor da linha é um token (consulte a função array).

Todos os tokenizadores disponíveis estão listados em system.tokenizers.

O tokenizador splitByString aplica os separadores de divisão da esquerda para a direita. Isso pode criar ambiguidades. Por exemplo, as strings separadoras ['%21', '%'] farão com que %21abc seja tokenizado como ['abc'], enquanto inverter as duas strings separadoras para ['%', '%21'] produzirá ['21abc']. Na maioria dos casos, convém que a correspondência dê preferência primeiro aos separadores mais longos. Em geral, isso pode ser feito passando as strings separadoras em ordem decrescente de comprimento. Se as strings separadoras formarem um prefix code, elas poderão ser passadas em qualquer ordem.

Para entender como um tokenizador divide a string de entrada, você pode usar as funções tokens e tokensForLikePattern:

Exemplo:

Query

SELECT tokens('abc def', 'ngrams', 3);

Response

['abc','bc ','c d',' de','def']

Como trabalhar com entradas não ASCII. Índices de texto podem ser criados sobre dados textuais em qualquer idioma e conjunto de caracteres. Para textos não ASCII, recomenda-se o tokenizador asciiCJK, pois ele lida corretamente com os limites de palavras em Unicode, incluindo caracteres CJK. :::

Argumento do preprocessador (opcional). O preprocessador refere-se a uma expressão aplicada à string de entrada antes da tokenização.

Casos de uso típicos do argumento do preprocessador incluem

1. Conversão para minúsculas/maiúsculas, ou case folding para permitir correspondência sem diferenciar maiúsculas de minúsculas, por exemplo, lower, lowerUTF8, caseFoldUTF8.
2. Normalização UTF-8, por exemplo, normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, normalizeUTF8NFKCCasefold, toValidUTF8.
3. Remoção ou transformação de caracteres ou substrings indesejados, como acentos, por exemplo, extractTextFromHTML, substring, idnaEncode, translate, removeDiacriticsUTF8.

A expressão do pré-processador deve transformar um valor de entrada do tipo String ou FixedString em um valor do mesmo tipo. Se o índice de texto foi criado em uma coluna do tipo Nullable(T) ou LowCardinality(T), então a expressão do pré-processador deve aceitar valores anuláveis ou de baixa cardinalidade (ou seja, sem lançar uma exceção).

Exemplos:

• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))
• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))
• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col)))
• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = removeDiacriticsUTF8(caseFoldUTF8(col)))

Além disso, a expressão do pré-processador deve referenciar apenas a coluna ou expressão sobre a qual o índice de texto está definido.

Exemplos:

• INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = upper(lower(col)))
• INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(lower(col), lower(col)))
• Não é permitido: INDEX idx(lower(col)) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))

Não é permitido usar funções não determinísticas.

As funções hasToken, hasAllTokens e hasAnyTokens usam o pré-processador para primeiro transformar o termo de busca antes de tokenizá-lo.

Por exemplo,

Query

CREATE TABLE table
(
    str String,
    INDEX idx(str) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, 'Foo');

é equivalente a:

Query

CREATE TABLE table
(
    str String,
    INDEX idx(lower(str)) TYPE text(tokenizer = 'splitByNonAlpha')
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, lower('Foo'));

Neste caso, a expressão de pré-processamento transforma os elementos do array individualmente.

Exemplo:

Query

CREATE TABLE table
(
    arr Array(String),
    INDEX idx arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(arr))

    -- Isso não é permitido:
    INDEX idx_illegal arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = arraySort(arr))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(arr, 'foo');

Para definir um pré-processador em um índice de texto na compilação de colunas do tipo Map, os usuários precisam decidir se o índice é compilado com base nas chaves ou nos valores do map.

Exemplo:

Query

CREATE TABLE table
(
    map Map(String, String),
    INDEX idx mapKeys(map)  TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(mapKeys(map)))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(mapKeys(map), 'foo');

Outros argumentos (opcionais).

Granularidade do índice. Os índices de texto são implementados no ClickHouse como um tipo de skip indexes. No entanto, diferentemente de outros skip indexes, os índices de texto usam granularidade infinita (100 milhões). Isso pode ser observado na definição da tabela de um índice de texto.

Exemplo:

Query

CREATE TABLE table(
    k UInt64,
    s String,
    INDEX idx(s) TYPE text(tokenizer = ngrams(2)))
ENGINE = MergeTree()
ORDER BY k;

SHOW CREATE TABLE table;

Response

┌─statement──────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.table                                            ↴│
│↳(                                                                     ↴│
│↳    `k` UInt64,                                                       ↴│
│↳    `s` String,                                                       ↴│
│↳    INDEX idx s TYPE text(tokenizer = ngrams(2)) GRANULARITY 100000000↴│ <-- here
│↳)                                                                     ↴│
│↳ENGINE = MergeTree                                                    ↴│
│↳ORDER BY k                                                            ↴│
│↳SETTINGS index_granularity = 8192                                      │
└────────────────────────────────────────────────────────────────────────┘

A granularidade de índice muito alta garante que o índice de texto seja criado para toda a parte. Uma granularidade de índice especificada explicitamente é ignorada.

​

Usando um índice de texto

Usar um índice de texto em consultas SELECT é simples, pois as funções comuns de busca em strings usarão o índice automaticamente. Se não houver um índice em uma coluna ou parte da tabela, as funções de busca em strings recorrerão a varreduras lentas por força bruta.

Recomendamos usar as funções hasAnyTokens e hasAllTokens para pesquisar no índice de texto; consulte abaixo. Essas funções funcionam com todos os tokenizadores disponíveis e todas as expressões de pré-processamento possíveis. Como as outras funções compatíveis surgiram historicamente antes do índice de texto, elas precisaram manter seu comportamento legado em muitos casos (por exemplo, sem suporte a pré-processamento).

​

Funções suportadas

O índice de texto pode ser usado com funções de texto na cláusula WHERE ou nas cláusulas PREWHERE:

SELECT [...]
FROM [...]
WHERE string_search_function(column_with_text_index)

​

=

= (equals) corresponde ao termo de busca informado por completo.

Exemplo:

SELECT * from table WHERE str = 'Hello';

​

IN

IN (in) é semelhante a equals, mas faz correspondência com todos os termos pesquisados.

Exemplo:

SELECT * from table WHERE str IN ('Hello', 'World');

NOT IN (notIn) não é suportado pelo índice de texto.

​

LIKE and match

Atualmente, essas funções usam o índice de texto para filtragem apenas se o tokenizador do índice for splitByNonAlpha, ngrams ou sparseGrams.

NOT LIKE (notLike) não é compatível com o índice de texto.

Para usar LIKE (like) e a função match com índices de texto, o ClickHouse precisa conseguir extrair tokens completos do termo de busca. No caso do índice com o tokenizador ngrams, isso acontece se o comprimento das strings pesquisadas entre caracteres curinga for igual ou maior que o comprimento do ngram.

Exemplo de índice de texto com o tokenizador splitByNonAlpha:

SELECT count() FROM table WHERE comment LIKE 'support%';

support no exemplo pode corresponder a support, supports, supporting etc. Esse tipo de consulta é uma consulta de substring e não pode ser acelerada com um índice de texto.

Para usar um índice de texto em consultas LIKE, o padrão LIKE deve ser reescrito da seguinte forma:

SELECT count() FROM table WHERE comment LIKE ' support %'; -- ou `% support %`

Os espaços à esquerda e à direita de support garantem que o termo possa ser extraído como um token.

Felizmente, há um caso especial em que o ClickHouse pode aproveitar o índice invertido para acelerar significativamente consultas LIKE.

Consulte a seção sobre otimização de desempenho de LIKE/ILIKE para mais detalhes.

​

startsWith and endsWith

Assim como LIKE, as funções startsWith e endsWith só podem usar um índice de texto se for possível extrair tokens completos do termo de busca. No índice com o tokenizer ngrams, isso acontece quando o comprimento das Strings pesquisadas entre caracteres curinga é igual ou maior que o comprimento do ngram.

Exemplo de índice de texto com o tokenizer splitByNonAlpha:

SELECT count() FROM table WHERE startsWith(comment, 'clickhouse support');

No exemplo, apenas clickhouse é considerado um token. support não é considerado um token porque pode corresponder a support, supports, supporting etc.

Para encontrar todas as linhas que começam com clickhouse supports, termine o padrão de busca com um espaço no final:

startsWith(comment, 'clickhouse supports ')`

Da mesma forma, endsWith deve ser usado com um espaço no início:

SELECT count() FROM table WHERE endsWith(comment, ' olap engine');

​

hasToken e hasTokenOrNull

A função hasToken parece simples de usar, mas tem algumas limitações com tokenizadores não padrão e expressões de pré-processamento. Recomendamos usar as funções hasAnyTokens e hasAllTokens.

As funções hasToken e hasTokenOrNull fazem correspondência com um único token fornecido.

Ao contrário das funções mencionadas anteriormente, elas não tokenizam o termo de busca (partem do pressuposto de que a entrada é um único token).

Exemplo:

SELECT count() FROM table WHERE hasToken(comment, 'clickhouse');

​

hasAnyTokens and hasAllTokens

As funções hasAnyTokens e hasAllTokens fazem a correspondência com um ou com todos os tokens fornecidos.

Essas duas funções aceitam os tokens de busca como uma string, que será tokenizada usando o mesmo tokenizador da coluna de índice, ou como um array de tokens já processados, aos quais nenhuma tokenização será aplicada antes da busca. Consulte a documentação da função para mais informações.

Exemplo:

-- Tokens de busca passados como argumento string
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Tokens de busca passados como Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

​

hasPhrase

A função hasPhrase faz correspondência com uma frase: todos os tokens devem aparecer de forma consecutiva e na mesma ordem da string de busca.

Ao contrário de hasAllTokens, que exige apenas que todos os tokens estejam presentes em algum ponto, hasPhrase exige que eles apareçam em sequência. A frase de busca é tokenizada usando o mesmo tokenizador configurado para a coluna de índice. Observe que a função requer um dos tokenizadores splitByNonAlpha, splitByString, ngrams ou asciiCJK.

Exemplo:

-- Corresponde: 'clickhouse' e 'olap' devem aparecer consecutivamente nessa ordem
SELECT count() FROM table WHERE hasPhrase(comment, 'clickhouse olap');

-- NÃO corresponde a uma linha contendo 'olap clickhouse' (ordem incorreta)
-- NÃO corresponde a uma linha contendo 'clickhouse fast olap' (não consecutivos)

​

has

A função Array has verifica a correspondência de um único token em um array de strings.

Exemplo:

SELECT count() FROM table WHERE has(array, 'clickhouse');

​

hasAny e hasAll

As funções de array hasAny e hasAll verificam se a coluna de array indexada contém alguma ou todas as strings procuradas de um conjunto constante.

Exemplo:

SELECT count() FROM table WHERE hasAny(tags, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAll(tags, ['clickhouse', 'olap']);

​

mapContains

A função mapContains (um alias de mapContainsKey) faz a correspondência com os tokens extraídos da string pesquisada nas chaves de um map. O comportamento é semelhante ao da função equals com uma coluna String. O índice de texto é usado apenas se tiver sido criado em uma expressão mapKeys(map).

Exemplo:

SELECT count() FROM table WHERE mapContainsKey(map, 'clickhouse');
-- OU
SELECT count() FROM table WHERE mapContains(map, 'clickhouse');

​

mapContainsValue

A função mapContainsValue faz correspondência com os tokens extraídos da string pesquisada nos valores de um map. O comportamento é semelhante ao da função equals com uma coluna String. O índice de texto só é usado se tiver sido criado em uma expressão mapValues(map).

Exemplo:

SELECT count() FROM table WHERE mapContainsValue(map, 'clickhouse');

​

mapContainsKeyLike and mapContainsValueLike

As funções mapContainsKeyLike e mapContainsValueLike comparam um padrão com todas as chaves ou valores (respectivamente) de um map.

Exemplo:

SELECT count() FROM table WHERE mapContainsKeyLike(map, '% clickhouse %');
SELECT count() FROM table WHERE mapContainsValueLike(map, '% clickhouse %');

​

operator[]

O operador de acesso operator[] pode ser usado com o índice de texto para filtrar as chaves e os valores. O índice de texto só é usado se for criado nas expressões mapKeys(map) ou mapValues(map), ou em ambas.

Exemplo:

SELECT count() FROM table WHERE map['engine'] = 'clickhouse';

Veja os exemplos a seguir de como usar colunas do tipo Array(T) e Map(K, V) com o índice de texto.

​

Indexação de colunas Array(String)

Imagine uma plataforma de blogs, em que os autores categorizam suas publicações usando palavras-chave. Queremos que os usuários descubram conteúdo relacionado pesquisando tópicos ou clicando neles.

Considere esta definição de tabela:

CREATE TABLE posts
(
    post_id UInt64,
    title String,
    content String,
    keywords Array(String)
)
ENGINE = MergeTree
ORDER BY (post_id);

Sem um índice de texto, encontrar posts com uma palavra-chave específica (por exemplo, clickhouse) exige a varredura de todos os registros:

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- varredura lenta da tabela inteira - verifica cada keyword em cada post

À medida que a plataforma cresce, isso se torna cada vez mais lento, porque a consulta precisa examinar o array keywords em cada linha. Para contornar esse problema de desempenho, definimos um índice de texto para a coluna keywords:

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Não se esqueça de reconstruir o índice para os dados existentes

​

Indexação de colunas Map

Em muitos casos de uso de observabilidade, as mensagens de log são divididas em “componentes” e armazenadas nos tipos de dados apropriados, por exemplo, data e hora para o timestamp, enum para o nível de log etc. Os campos de métricas são mais bem armazenados como pares chave-valor. As equipes de operações precisam pesquisar nos logs com eficiência para depuração, incidentes de segurança e monitoramento.

Considere esta tabela de logs:

CREATE TABLE logs
(
    id UInt64,
    timestamp DateTime,
    message String,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY (timestamp);

Sem um índice de texto, pesquisar dados do tipo Map exige varreduras completas da tabela:

-- Encontra todos os logs com dados de rate limiting:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- varredura lenta na tabela completa

-- Encontra todos os logs de um IP específico:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- varredura lenta na tabela completa

À medida que o volume de logs aumenta, essas consultas ficam lentas.

A solução é criar um índice de texto para as chaves e os valores do Map. Use mapKeys para criar um índice de texto quando precisar encontrar logs por nomes de campos ou tipos de atributos:

ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;

Use mapValues para criar um índice de texto quando precisar pesquisar no conteúdo real dos atributos:

ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;

Exemplos de consultas:

-- Encontra todas as requisições com limite de taxa:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Encontra todos os logs de um IP específico:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- Encontra todos os logs em que algum atributo inclui um erro:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast

​

Indexação de colunas JSON

Índices de texto podem ser usados com colunas JSON de três maneiras:

1. Índices em subcolunas específicas — crie um índice de texto em um caminho JSON conhecido, assim como em uma coluna comum. Isso indexa os valores nesse caminho.
2. Índices baseados em caminhos com JSONAllPaths — indexam todos os caminhos presentes em cada grânulo para ignorar grânulos que não podem conter o caminho consultado. Semelhante ao que ocorre com colunas Map.
3. Índices baseados em valores com JSONAllValues — indexam todos os valores em todos os caminhos JSON para acelerar a busca de texto completo em qualquer subcoluna JSON com um único índice.

​

Índices em subcolunas específicas

Você pode criar um skip index em qualquer subcoluna JSON usando a mesma sintaxe das colunas comuns.

Há duas maneiras de referenciar uma subcoluna JSON em uma expressão de índice:

• Caminho tipado declarado no type hint de JSON — acesse-o diretamente pelo nome: json.a.
• Caminho dinâmico com cast explícito — use a sintaxe de cast ::: json.b::String.

Exemplo de definição de índice:

Query

CREATE TABLE sensor_data
(
    data JSON(sensor_id String),
    INDEX idx_sensor data.sensor_id TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_location data.location::String TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number , 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);

Exemplo de consulta:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id = 'id_5';

Response

...
    Indexes:
      Skip
        Name: idx_sensor
        Description: text
        Condition: (mode: All; tokens: ["5", "id"])
        Parts: 1/2
        Granules: 1/8

Exemplo de consulta:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';

Response

...
    Indexes:
      Skip
        Name: idx_location
        Description: text
        Condition: (mode: All; tokens: ["5", "room"])
        Parts: 1/2
        Granules: 1/8

​

Índices baseados em caminhos com JSONAllPaths

Assim como nas colunas Map, é possível criar índices de texto em colunas JSON usando JSONAllPaths. O índice armazena o conjunto de caminhos JSON presentes em cada grânulo e os utiliza para ignorar grânulos nos quais o caminho consultado está ausente.

Definição de exemplo do índice:

Query

CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');

Você pode usar EXPLAIN indexes = 1 para verificar se o skip index está sendo usado. Quando um caminho existe apenas em uma parte, o índice ignora a outra parte.

Exemplo:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';

Response

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

Quando um caminho não existe em nenhuma parte, todas as partes e todos os grânulos são ignorados.

Exemplo:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;

Response

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["nonexistent"])
        Parts: 0/2
        Granules: 0/2

IS NOT NULL também usa o índice — ele ignora os grânulos em que o caminho não existe (já que o valor seria NULL):

Exemplo:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;

Response

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

​

Índices baseados em valores com JSONAllValues

Índices de texto podem ser usados para acelerar pesquisas em colunas JSON por meio da função JSONAllValues.

JSONAllValues retorna todos os valores de uma coluna JSON como Array(String). Valores de tipos de dados que não são string (por exemplo, inteiros e arrays) são convertidos para sua representação em texto. Um índice de texto criado com JSONAllValues indexa essas representações textuais em todos os caminhos JSON de cada linha. Esse índice pode então acelerar consultas que filtram subcolunas JSON específicas. Quando uma consulta filtra uma subcoluna específica (por exemplo, data.user_name = 'alice'), o índice de texto pode rapidamente ignorar linhas (e grânulos) que não contêm os tokens pesquisados em nenhum de seus valores JSON.

O índice pode gerar falsos positivos quando caminhos JSON diferentes contêm os mesmos tokens. Por exemplo, se a linha 1 tiver {"a": "hello", "b": "world"} e uma consulta procurar por data.a = 'world', o índice de texto não consegue distinguir que world pertence ao caminho b, e não a a. Nesses casos, o índice não ignorará a linha, e o filtro nos dados reais da coluna fará a avaliação final. Esse é o mesmo comportamento de outros casos de uso de índice de texto, em que o índice atua como um pré-filtro rápido.

Criando o índice

Exemplo de definição de índice:

CREATE TABLE events
(
    id UInt64,
    data JSON,
    INDEX json_idx JSONAllValues(data) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

Padrões de consulta suportados

Depois de criado, o índice pode acelerar consultas em subcolunas JSON usando as mesmas funções usadas com colunas String e a função equals para todas as colunas.

Acesso à subcoluna:

SELECT * FROM events WHERE data.user_name = 'alice';
SELECT * FROM events WHERE data.message LIKE '% error %';
SELECT * FROM events WHERE startsWith(data.status, 'fail');
SELECT * FROM events WHERE hasToken(data.title, 'clickhouse');

Acesso à subcoluna com CAST explícito:

SELECT * FROM events WHERE hasAllTokens(data.message::String, 'connection timeout');
SELECT * FROM events WHERE data.status_code::UInt64 = 404;
SELECT * FROM events WHERE has(data.tags::Array(String), 'bug')

operador IN:

SELECT * FROM events WHERE data.level IN ('error', 'critical');

​

Busca por frase

O índice de texto oferece suporte à busca por frase por meio da função hasPhrase. Todos os tokens da frase devem aparecer de forma consecutiva e na mesma ordem no documento.

O índice de texto acelera a busca por frase ao intersectar as lista de postings de todos os tokens da frase para identificar grânulos candidatos. Dentro desses grânulos, o ClickHouse então verifica a adjacência exata entre os tokens.

hasPhrase tem suporte com os tokenizadores splitByNonAlpha, splitByString, ngrams e asciiCJK.

A frase é tokenizada usando o tokenizador configurado no índice. Os caracteres separadores do tokenizador na frase são ignorados: hasPhrase(text, 'quick+brown') é equivalente a hasPhrase(text, 'quick brown') para o tokenizador splitByNonAlpha.

​

Exemplo

Query

CREATE TABLE tab (
    id UInt32,
    text String,
    INDEX idx(text) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO tab VALUES
    (1, 'weather in New York'),
    (2, 'New weather in York'),
    (3, 'weather in New Orleans');

Query

SELECT id, text FROM tab WHERE hasPhrase(text, 'weather in New York');

Response

   ┌─id─┬─text────────────────┐
1. │  1 │ weather in New York │
   └────┴─────────────────────┘

A linha 2 ('New weather in York') não corresponde porque os tokens estão na ordem incorreta. A linha 3 ('weather in New Orleans') não corresponde porque não contém o token 'York'.

​

Otimização de desempenho

​

Leitura direta

Certos tipos de consultas de texto podem ser acelerados significativamente por uma otimização chamada “leitura direta”.

Exemplo:

SELECT column_a, column_b, ...
FROM [...]
WHERE string_search_function(column_with_text_index)

A otimização de leitura direta responde à consulta exclusivamente usando o índice de texto (isto é, lookups no índice de texto), sem acessar a coluna de texto subjacente. Os lookups no índice de texto leem relativamente poucos dados e, por isso, são muito mais rápidos do que os skip indexes usuais no ClickHouse (que fazem um lookup no skip index, seguido do carregamento e da filtragem dos grânulos restantes).

A leitura direta é controlada por duas configurações:

• A configuração query_plan_direct_read_from_text_index (true por padrão) especifica se a leitura direta está habilitada de modo geral.
• A configuração use_skip_indexes_on_data_read era um pré-requisito para a leitura direta em versões do ClickHouse < 26.4.

Funções compatíveis

A otimização de leitura direta oferece suporte às funções hasToken, hasAllTokens e hasAnyTokens. Se o índice de texto for definido com um tokenizer array, a leitura direta também terá suporte para as funções equals, has, hasAny, hasAll, mapContainsKey e mapContainsValue. Essas funções também podem ser combinadas com os operadores AND, OR e NOT. As cláusulas WHERE ou PREWHERE também podem conter filtros adicionais de funções que não sejam de busca de texto (para colunas de texto ou outras colunas) - nesse caso, a otimização de leitura direta ainda será usada, mas será menos eficaz (ela se aplica apenas às funções de busca de texto compatíveis).

Para verificar se uma consulta utiliza leitura direta, execute a consulta com EXPLAIN PLAN actions = 1. Como exemplo, uma consulta com a leitura direta desabilitada

EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- desabilitar leitura direta

retorna

[...]
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
[...]

ao executar a mesma consulta com 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, -- ativa direct read

retorna

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

A segunda saída de EXPLAIN PLAN contém uma coluna virtual __text_index_<index_name>_<function_name>_<id>. Se essa coluna estiver presente, a leitura direta será usada.

Se a cláusula de filtro WHERE contiver apenas funções de busca de texto, a consulta poderá evitar completamente a leitura dos dados da coluna e obter o maior ganho de desempenho com a leitura direta. No entanto, mesmo que a coluna de texto seja acessada em outra parte da consulta, a leitura direta ainda proporcionará melhoria de desempenho.

Leitura direta como hint

A leitura direta como hint se baseia nos mesmos princípios da leitura direta normal, mas adiciona um filtro extra construído a partir dos dados do índice de texto, sem remover a coluna de texto subjacente. Ela é usada para funções em que ler apenas do índice de texto produziria falsos positivos.

As funções compatíveis são: like, startsWith, endsWith, equals, has, hasPhrase, mapContainsKey e mapContainsValue.

O filtro adicional pode oferecer seletividade extra para restringir ainda mais o conjunto de resultados em combinação com outros filtros, ajudando a reduzir a quantidade de dados lidos de outras colunas.

A leitura direta como hint é controlada pela configuração query_plan_text_index_add_hint (ativada por padrão).

Exemplo de consulta sem hint:

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

retorna

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

enquanto a mesma consulta é executada com query_plan_text_index_add_hint = 1

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

retorna

[...]
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)
[...]

Na segunda saída de EXPLAIN PLAN, você pode ver que um termo adicional (__text_index_...) foi adicionado à condição de filtro. Graças à otimização PREWHERE, a condição de filtro é dividida em três termos separados, aplicados em ordem crescente de complexidade computacional. Para esta consulta, a ordem de aplicação é __text_index_..., depois greaterOrEquals(...) e, por fim, like(...). Essa ordenação permite ignorar ainda mais grânulos de dados do que os já ignorados pelo índice de texto e pelo filtro original, antes da leitura das colunas pesadas usadas na consulta após a cláusula WHERE, reduzindo ainda mais a quantidade de dados a ser lida.

​

Consultas LIKE/ILIKE

Quando um padrão de consulta LIKE/ILIKE é %<caracteres-alfanuméricos-sem-espaços>% e o tokenizer do índice de texto é splitByNonAlpha ou array, o ClickHouse usa o índice invertido para acelerar significativamente as consultas LIKE/ILIKE. Para isso, o ClickHouse examina o dicionário do índice invertido em vez de fazer uma varredura completa da tabela para encontrar o padrão correspondente.

Quando a otimização está habilitada, as consultas LIKE/ILIKE devem ser significativamente mais rápidas do que uma varredura completa da tabela. No entanto, quando o padrão corresponde à maioria dos tokens do dicionário, o desempenho pode ser pior do que em uma varredura completa da tabela. Felizmente, há um mecanismo de fallback para evitar isso.

A otimização é controlada por uma configuração:

• use_text_index_like_evaluation_by_dictionary_scan

O mecanismo de fallback é controlado por duas configurações:

• text_index_like_min_pattern_length
• text_index_like_max_postings_to_read

Essa otimização é compatível apenas com as funções like e ilike.

​

Cache

Há diferentes caches disponíveis para armazenar em buffer, na memória, partes do índice de texto (consulte a seção Detalhes de implementação): Atualmente, há caches para os cabeçalhos desserializados, tokens e lista de postings do índice de texto, para reduzir a E/S. Eles podem ser ativados por meio das configurações use_text_index_header_cache, use_text_index_tokens_cache e use_text_index_postings_cache.

Por padrão, todos os caches estão desabilitados. Para limpar os caches, use a instrução SYSTEM CLEAR TEXT INDEX CACHES

Consulte as configurações de servidor a seguir para configurar os caches.

​

Configurações do cache de tokens

Configuração	Descrição
text_index_tokens_cache_policy	Nome da política do cache de tokens do índice de texto.
text_index_tokens_cache_size	Tamanho máximo do cache em bytes.
text_index_tokens_cache_max_entries	Número máximo de tokens desserializados no cache.
text_index_tokens_cache_size_ratio	Tamanho da fila protegida no cache de tokens do índice de texto em relação ao tamanho total do cache.

​

Configurações de cache do cabeçalho

Configuração	Descrição
text_index_header_cache_policy	Nome da política do cache do cabeçalho do índice de texto.
text_index_header_cache_size	Tamanho máximo do cache em bytes.
text_index_header_cache_max_entries	Número máximo de cabeçalhos desserializados no cache.
text_index_header_cache_size_ratio	Tamanho da fila protegida no cache do cabeçalho do índice de texto em relação ao tamanho total do cache.

​

Configurações do cache de listas de postings

Configuração	Descrição
text_index_postings_cache_policy	Nome da política do cache de postings do índice de texto.
text_index_postings_cache_size	Tamanho máximo do cache em bytes.
text_index_postings_cache_max_entries	Número máximo de postings desserializados no cache.
text_index_postings_cache_size_ratio	Tamanho da fila protegida no cache de postings do índice de texto em relação ao tamanho total do cache.

​

Limitações

No momento, o índice de texto tem as seguintes limitações:

• A materialização de índices de texto com um grande número de tokens (por exemplo, 10 bilhões de tokens) pode consumir quantidades significativas de memória. A materialização de índices de texto pode ocorrer diretamente (ALTER TABLE <table> MATERIALIZE INDEX <index>) ou indiretamente durante mesclagens de partes.
• Não é possível materializar índices de texto em partes com mais de 4.294.967.296 (= 2^32 = aprox. 4,2 bilhões) linhas. Sem um índice de texto materializado, as consultas recorrem a uma busca lenta por força bruta dentro da parte. Como estimativa de pior caso, suponha que uma parte contenha uma única coluna do tipo String e que a configuração do MergeTree max_bytes_to_merge_at_max_space_in_pool (padrão: 150 GB) não tenha sido alterada. Nesse caso, isso ocorre se a coluna contiver, em média, menos de 29,5 caracteres por linha. Na prática, as tabelas também contêm outras colunas, e esse limite é várias vezes menor do que isso (dependendo do número, tipo e tamanho das outras colunas).

​

Índices de texto vs. índices baseados em filtro de Bloom

Predicados sobre strings podem ser acelerados com índices de texto e índices baseados em filtro de Bloom (tipos de índice bloom_filter, ngrambf_v1, tokenbf_v1, sparse_grams), mas eles diferem fundamentalmente em seu design e nos casos de uso a que se destinam:

Índices de filtro de Bloom

• Baseiam-se em estruturas de dados probabilísticas que podem produzir falsos positivos.
• Só conseguem responder a perguntas de pertinência a conjuntos, ou seja: a coluna pode conter o token X vs. definitivamente não contém X.
• Armazenam informações no nível de grânulo, o que permite ignorar intervalos mais amplos durante a execução da consulta.
• São difíceis de ajustar corretamente (veja aqui um exemplo).
• São relativamente compactos (alguns quilobytes ou megabytes por parte).

Índices de texto

• Constroem um índice invertido determinístico sobre tokens. O próprio índice não pode gerar falsos positivos.
• São especificamente otimizados para cargas de trabalho de pesquisa de texto.
• Armazenam informações no nível da linha, o que permite a busca eficiente de termos.
• São relativamente grandes (de dezenas a centenas de megabytes por parte).

Os índices baseados em filtro de Bloom oferecem suporte à pesquisa de texto completo apenas como um “efeito colateral”:

• Eles não oferecem suporte a tokenização e preprocessamento avançados.
• Eles não oferecem suporte à pesquisa por múltiplos tokens.
• Eles não fornecem as características de desempenho esperadas de um índice invertido.

Os índices de texto, em contraste, são criados especificamente para pesquisa de texto completo:

• Eles oferecem tokenização e preprocessamento
• Eles oferecem suporte eficiente a hasAllTokens, LIKE, match e funções semelhantes de pesquisa de texto.
• Eles têm escalabilidade significativamente melhor para grandes corpora de texto.

​

Detalhes de implementação

Cada índice de texto consiste em duas estruturas de dados (abstratas):

• um dicionário que mapeia cada token para uma lista de postings, e
• um conjunto de listas de postings, cada uma representando um conjunto de números de linha.

O índice de texto é construído para a parte inteira. Ao contrário de outros skip indexes, o índice de texto pode ser mesclado em vez de reconstruído durante a mesclagem das partes de dados (veja abaixo).

Durante a criação do índice, três arquivos são criados (por parte):

Arquivo de blocos do dicionário (.dct)

Os tokens no índice de texto são ordenados e armazenados em blocos de dicionário de 512 tokens cada (o tamanho do bloco é configurável pelo parâmetro dictionary_block_size). Um arquivo de blocos do dicionário (.dct) consiste em todos os blocos de dicionário de todos os grânulos de índice em uma parte.

Arquivo de cabeçalho do índice (.idx)

O arquivo de cabeçalho do índice contém, para cada bloco de dicionário, o primeiro token do bloco e seu deslocamento relativo no arquivo de blocos do dicionário.

Essa estrutura de índice esparso é semelhante ao índice primário esparso) do ClickHouse.

Arquivo de listas de postings (.pst)

As listas de postings de todos os tokens são organizadas sequencialmente no arquivo de listas de postings. Para economizar espaço e ainda permitir operações rápidas de interseção e união, as listas de postings são armazenadas como bitmaps Roaring. Se a lista de postings for maior que posting_list_block_size, ela será dividida em vários blocos, que são armazenados sequencialmente no arquivo de listas de postings.

Mesclagem de índices de texto

Quando partes de dados são mescladas, o índice de texto não precisa ser reconstruído do zero; em vez disso, ele pode ser mesclado com eficiência em uma etapa separada do processo de mesclagem. Durante essa etapa, os dicionários ordenados dos índices de texto de cada parte de entrada são lidos e combinados em um novo dicionário unificado. Os números de linha nas listas de postings também são recalculados para refletir suas novas posições na parte de dados mesclada, usando um mapeamento de números de linha antigos para novos criado durante a fase inicial da mesclagem. Esse método de mesclar índices de texto é semelhante à forma como projeções com a coluna _part_offset são mescladas. Se o índice não estiver materializado na parte de origem, ele será construído, gravado em um arquivo temporário e depois mesclado com os índices das outras partes e de outros arquivos de índice temporários.

Depuração

A função de tabela mergeTreeTextIndex pode ser usada para inspecionar índices de texto.

​

Exemplo: conjunto de dados do Hacker News

Vamos analisar as melhorias de desempenho dos índices de texto em um grande conjunto de dados com muito conteúdo textual. Usaremos 28,7 milhões de linhas de comentários do popular site Hacker News. Aqui está a tabela sem índice de texto:

CREATE TABLE hackernews (
    id UInt64,
    deleted UInt8,
    type String,
    author String,
    timestamp DateTime,
    comment String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    children Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);

As 28,7 milhões de linhas estão em um arquivo Parquet no S3 — vamos inseri-las na tabela hackernews:

INSERT INTO hackernews
    SELECT * FROM s3Cluster(
        'default',
        'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
        'Parquet',
        '
    id UInt64,
    deleted UInt8,
    type String,
    by String,
    time DateTime,
    text String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    kids Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32');

Usaremos ALTER TABLE e adicionaremos um índice de texto à coluna comment e, em seguida, o materializaremos:

-- Adicionar o índice
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- Materializar o índice para os dados existentes
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

Agora, vamos executar consultas usando as funções hasToken, hasAnyTokens e hasAllTokens. Os exemplos a seguir mostram a grande diferença de desempenho entre uma varredura de índice padrão e a otimização de leitura direta.

​

1. Usando hasToken

hasToken verifica se o texto contém um token específico. Vamos buscar o token que diferencia maiúsculas de minúsculas ‘ClickHouse’.

Leitura direta desabilitada (varredura padrão) Por padrão, o ClickHouse usa o skip index para filtrar grânulos e depois lê os dados da coluna desses grânulos. Podemos simular esse comportamento desabilitando a leitura direta.

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.362 sec. Processed 24.90 million rows, 9.51 GB

Leitura direta habilitada (Leitura rápida do índice) Agora executamos a mesma consulta com leitura direta habilitada (o padrão).

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.008 sec. Processed 3.15 million rows, 3.15 MB

A consulta com leitura direta é mais de 45 vezes mais rápida (0.362s vs 0.008s) e processa significativamente menos dados (9.51 GB vs 3.15 MB) ao ler apenas o índice.

​

2. Usando hasAnyTokens

hasAnyTokens verifica se o texto contém pelo menos um dos tokens informados. Vamos procurar comentários que contenham ‘love’ ou ‘ClickHouse’.

Leitura direta desativada (Varredura padrão)

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 1.329 sec. Processed 28.74 million rows, 9.72 GB

Leitura direta ativada (leitura rápida do índice)

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 0.015 sec. Processed 27.99 million rows, 27.99 MB

O ganho de velocidade é ainda mais impressionante para esta busca comum com “OR”. A consulta é quase 89 vezes mais rápida (1.329s vs 0.015s) ao evitar a varredura completa da coluna.

​

3. Usando hasAllTokens

hasAllTokens verifica se o texto contém todos os tokens fornecidos. Vamos buscar comentários que contenham tanto ‘love’ quanto ‘ClickHouse’.

Leitura direta desativada (varredura padrão) Mesmo com a leitura direta desativada, o skip index padrão continua eficaz. Ele reduz as 28.7M linhas para apenas 147.46K linhas, mas ainda precisa ler 57.03 MB da coluna.

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.184 sec. Processed 147.46 thousand rows, 57.03 MB

Leitura direta ativada (Leitura rápida do índice) A leitura direta responde à consulta usando os dados do índice e lê apenas 147.46 KB.

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.007 sec. Processed 147.46 thousand rows, 147.46 KB

Para esta busca com “AND”, a otimização de leitura direta é mais de 26 vezes mais rápida (0.184s vs 0.007s) do que a varredura padrão com skip index.

​

4. Busca composta: OR, AND, NOT, …

A otimização de leitura direta também se aplica a expressões booleanas compostas. Aqui, faremos uma busca sem diferenciar maiúsculas de minúsculas por ‘ClickHouse’ OR ‘clickhouse’.

Leitura direta desativada (varredura padrão)

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.450 sec. Processed 25.87 million rows, 9.58 GB

Leitura direta ativada (Leitura rápida do índice)

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.013 sec. Processed 25.87 million rows, 51.73 MB

Ao combinar os resultados do índice, a consulta com leitura direta é 34 vezes mais rápida (0,450s vs 0,013s) e evita a leitura de 9,58 GB de dados de colunas. Para este caso específico, hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) seria a sintaxe mais indicada e mais eficiente.

​

Conteúdo relacionado

• Apresentação: https://github.com/ClickHouse/clickhouse-presentations/blob/master/2025-tumuchdata-munich/ClickHouse&#95;%20full-text%20search%20-%2011.11.2025%20Munich%20Database%20Meetup.pdf
• Apresentação: https://presentations.clickhouse.com/2026-fosdem-inverted-index/Inverted&#95;indexes&#95;the&#95;what&#95;the&#95;why&#95;the&#95;how.pdf

Material desatualizado

• Blog: Introdução aos índices invertidos no ClickHouse
• Blog: Por dentro da busca de texto completo no ClickHouse: rápida, nativa e colunar
• Vídeo: Índices de texto completo: design e experimentos