LIKE '%texto%' no PostgreSQL é lento: o pg_trgm consegue fazê-lo usar um índice?

No mundo dos bancos de dados, o PostgreSQL se destaca pela adaptabilidade e pela enorme quantidade de recursos que oferece, principalmente quando se trata de lidar com dados textuais. Uma joia muitas vezes subestimada nesse contexto é a extensão pg_trgm.

O que é o pg_trgm?

pg_trgm é uma extensão do PostgreSQL que oferece funções para medir a similaridade entre strings de texto e melhorar as buscas textuais. Ela funciona quebrando as strings em trigramas, que são grupos de três caracteres consecutivos extraídos do texto. Por exemplo, a palavra “Daniele” seria quebrada nos trigramas: d, da,dan,ani,nie,iel,ele,le .

Essa abordagem permite buscas fuzzy, encontrando resultados que “se parecem” com o termo buscado mesmo quando há erros de digitação ou pequenas variações.

Como usar?

Antes de tudo, a extensão precisa ser habilitada no seu banco de dados com o comando:

CREATE EXTENSION pg_trgm;

Depois de habilitada, você pode aproveitar as funções e os operadores do pg_trgm para melhorar as buscas. Por exemplo, para encontrar registros parecidos com uma string de busca, você pode usar o operador %:

SELECT * FROM your_table WHERE your_text_column % 'word';

Isso retorna todos os registros em que your_text_column é parecido com ‘word’.

Se você ficou curioso e quer ver como os textos são divididos, use show_trgm como mostrado abaixo.

SELECT show_trgm('Daniele');


--- SAÍDA 
--- {"  d"," da","ani","dan","ele","iel","le ","nie"}

Exemplos práticos

Suponha que temos uma tabela songs com uma coluna title. Para encontrar títulos parecidos com “Sultans of swing”, poderíamos escrever:

SELECT title FROM songs WHERE title % 'Sultens of swing';

Esse comando consegue encontrar o “Sultans of swing” correto apesar dos erros de digitação.

Para entender como funciona, vamos falar do operador %.

O operador % pode ser aplicado a dois text e retorna um boolean. Citando a documentação do PG: “Returns true if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold.”

O parâmetro similarity_threshold vale 0.3 por padrão. Ou seja, ele considera “parecidas” duas palavras com similaridade maior ou igual a 30%. Isso pode servir ou não para o seu cenário. Se precisar, mudar esse parâmetro é bem simples.

Para ler o valor atual de pg_trgm.similarity_threshold você pode usar a seguinte instrução:

show pg_trgm.similarity_threshold;

Digamos que queremos definir pg_trgm.similarity_threshold como 0.5.

Para otimizar o desempenho em tabelas grandes, é recomendável criar um índice GIST ou GIN sobre os trigramas:

CREATE INDEX idx_title_trgm ON songs USING gist (title gist_trgm_ops);

Agora, depois desta pequena introdução ao pg_trgm e ao operador %, vamos ao assunto deste artigo.

Outras informações sobre os operadores disponíveis no pg_trgm aqui.

Melhorando as queries com LIKE usando o pg_trgm

Além de lidar com buscas fuzzy, a extensão pg_trgm tem uma carta na manga que pode acelerar bastante as buscas com os operadores LIKE e ILIKE no PostgreSQL, principalmente as que têm o caractere curinga % nas duas pontas da string de busca. Isso é especialmente útil porque, sem o pg_trgm, essas buscas podem ser muito lentas em conjuntos de dados grandes, já que exigem uma varredura sequencial da tabela inteira.

Otimizando queries com LIKE/ILIKE

Com o pg_trgm instalado e configurado corretamente, o PostgreSQL consegue usar índices GIN e GIST baseados em trigramas para acelerar as queries com LIKE e ILIKE. Esse método aproveita a representação dos dados em trigramas para reduzir drasticamente o conjunto de dados a examinar durante a busca.

Por exemplo, usando a mesma tabela songs citada antes, se quisermos encontrar todos os títulos que contêm a string “love”, uma query típica seria:

SELECT title FROM songs WHERE title LIKE '%love%';

Sem o pg_trgm, essa query pode ser lenta em um conjunto de dados grande. Já com um índice de trigramas GIN ou GIST na coluna title, o PostgreSQL consegue restringir rapidamente a busca aos registros com maior probabilidade de casar com o padrão, reduzindo bastante o tempo de execução da query.

Criando um índice para otimizar LIKE/ILIKE

Para aproveitar essa otimização, podemos criar um índice GIN na coluna de interesse assim:

CREATE INDEX idx_title_like ON songs USING gin (title gin_trgm_ops);

Com esse índice no lugar, as queries com LIKE e ILIKE que usam padrões com curingas nas duas pontas rodam muito mais rápido, graças à pré-seleção dos candidatos baseada em trigramas.

Vamos fazer um teste real

Para um teste real, crie uma tabela com meio milhão de registros usando o script a seguir.

drop table if exists random_people;
create table random_people as
  with rnd_people as (
    select distinct  
	    ('[0:13]={"Daniele","Debora","Mattia","Jake","Amy","Samuel","Jacopo","Martina","Sofia","Henry","Neil","Tim","John","George"}'::text[])
		    [floor(random()*14)] || ' ' || floor(random()*100000+1)::text || '°'  first_name,
	    ('[0:13]={"Bianchi","Lamborghini","Ferrari","De Tommaso","Rossi","Verdi","Gialli","Caponi","Gallini","Gatti","Ford","Daniel","Harrison","Macdonald"}'::text[])
		    [floor(random()*14)] || ' ' || floor(random()*100000+1)::text || '°' last_name
    from 
      generate_series(1, 10 * 1000 * 50)
  )
  select 
    row_number() over() id, first_name, last_name 
  from 
    rnd_people;

select count(*) from random_people; -- 500.000 registros   

O EXPLAIN ANALYZE diz que a query leva cerca de 160ms para executar.

explain analyze select * from random_people where last_name ilike '%tom%';  -- cerca de 160 ms na minha máquina 

Agora crie um índice GIN no campo last_name usando a operator class gin_trgm_ops.

CREATE EXTENSION if not exists pg_trgm;
drop index if exists index_last_name_on_random_people_trigram;
CREATE INDEX CONCURRENTLY index_last_name_on_random_people_trigram
ON random_people
USING gin (last_name gin_trgm_ops);

Agora, o teste final. Execute de novo a query anterior.

explain analyze select * from random_people where last_name ilike '%tom%'; -- cerca de 0,025 ms na minha máquina!

Ótimo! De 160ms para 0,025ms (na minha máquina).

Considerações finais

Usar o pg_trgm para otimizar as queries com LIKE e ILIKE é um ótimo exemplo de como as extensões do PostgreSQL podem melhorar o desempenho e a flexibilidade de uma aplicação. É importante lembrar que, embora os índices baseados em trigramas acelerem bastante essas buscas, eles também ocupam espaço extra e podem pesar nos tempos de inserção dos dados. Por isso, como sempre na engenharia, a adoção deve ser avaliada com cuidado de acordo com os requisitos específicos da aplicação.

Resumindo, integrar o pg_trgm às suas estratégias de indexação e busca pode transformar profundamente o desempenho das queries textuais. “Sapere aude” (Ouse saber): esta extensão convida você a explorar e aproveitar ao máximo os recursos avançados do PostgreSQL para o tratamento de dados textuais.

Treinamentos de PostgreSQL

Se você quer dominar o PostgreSQL, a minha empresa oferece alguns treinamentos bem procurados:

• PostgreSQL for Developers, em inglês
• PostgreSQL per Sviluppatori, em italiano
• PostgreSQL per Amministratori, em italiano

Se a sua empresa precisa de um treinamento sob medida, fale com a gente.

Os treinamentos estão disponíveis remotamente e presencialmente.

Links

• Documentação oficial do pg_trgm
• Todas as funções oferecidas pela extensão pg_trgm