LIKE '%texto%' es lento en PostgreSQL: ¿puede pg_trgm hacer que use un índice?

En el mundo de las bases de datos, PostgreSQL destaca por su adaptabilidad y por la cantidad de funcionalidades que ofrece, sobre todo cuando se trata de manejar datos de texto. Una joya a menudo infravalorada en este contexto es la extensión pg_trgm.

¿Qué es pg_trgm?

pg_trgm es una extensión de PostgreSQL que permite calcular la similitud entre cadenas de texto y mejorar las búsquedas de texto. Funciona descomponiendo las cadenas en trigramas, es decir, grupos de tres caracteres consecutivos extraídos del texto. Por ejemplo, la palabra “Daniele” se descompondría en los trigramas: d, da,dan,ani,nie,iel,ele,le .

Este enfoque facilita las búsquedas difusas (fuzzy search): encuentra resultados que “se parecen” al término buscado incluso cuando hay erratas o pequeñas variaciones.

¿Cómo se usa?

Antes que nada, hay que habilitar la extensión en la base de datos con el comando:

CREATE EXTENSION pg_trgm;

Una vez habilitada, puedes aprovechar las funciones y los operadores de pg_trgm para mejorar las búsquedas. Por ejemplo, para encontrar registros parecidos a una cadena de búsqueda puedes usar el operador %:

SELECT * FROM your_table WHERE your_text_column % 'word';

Esto devuelve todos los registros en los que your_text_column es parecido a ‘word’.

Si tienes curiosidad por ver cómo se divide el texto, usa show_trgm como se muestra a continuación.

SELECT show_trgm('Daniele');


--- SALIDA 
--- {"  d"," da","ani","dan","ele","iel","le ","nie"}

Ejemplos prácticos

Supongamos que tenemos una tabla songs con una columna title. Para encontrar títulos parecidos a “Sultans of swing” podríamos escribir:

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

Esta consulta puede encontrar el “Sultans of swing” correcto a pesar de las erratas.

Para entender cómo funciona, hablemos del operador %.

El operador % se aplica a dos text y devuelve un boolean. Citando la documentación de PG: “Devuelve true si sus argumentos tienen una similitud mayor que el umbral de similitud actual fijado por pg_trgm.similarity_threshold.”

El parámetro similarity_threshold vale 0.3 por defecto. Es decir, considera “parecidas” dos palabras con una similitud mayor o igual al 30%. Puede que te sirva para tu caso o puede que no. Si lo necesitas, cambiar este parámetro es muy sencillo.

Para leer el valor actual de pg_trgm.similarity_threshold puedes usar la siguiente sentencia:

show pg_trgm.similarity_threshold;

Supongamos que queremos fijar pg_trgm.similarity_threshold en 0.5.

Para optimizar el rendimiento en tablas grandes, conviene crear un índice GIST o GIN sobre los trigramas:

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

Ahora, después de esta pequeña introducción a pg_trgm y al operador %, vamos al tema de este artículo.

Más información sobre los operadores disponibles en pg_trgm aquí.

Mejorar las consultas LIKE con pg_trgm

Además de gestionar búsquedas difusas, la extensión pg_trgm guarda un as en la manga que puede acelerar mucho las búsquedas con los operadores LIKE e ILIKE en PostgreSQL, sobre todo las que llevan el comodín % en los dos extremos de la cadena de búsqueda. Esto es especialmente útil porque, sin pg_trgm, esas búsquedas pueden ser muy lentas en conjuntos de datos grandes, ya que exigen un recorrido secuencial de toda la tabla.

Optimizar las consultas LIKE/ILIKE

Con pg_trgm instalada y bien configurada, PostgreSQL puede usar índices GIN y GIST basados en trigramas para acelerar las consultas LIKE e ILIKE. Este método aprovecha la representación en trigramas de los datos para reducir drásticamente el conjunto de datos que hay que examinar durante la búsqueda.

Por ejemplo, con la misma tabla songs de antes, si queremos encontrar todos los títulos que contienen la cadena “love”, una consulta típica sería:

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

Sin pg_trgm, esta consulta puede ser lenta en un conjunto de datos grande. En cambio, con un índice GIN o GIST de trigramas sobre la columna title, PostgreSQL puede acotar rápidamente la búsqueda a los registros con más probabilidades de coincidir con el patrón, lo que reduce mucho el tiempo de ejecución de la consulta.

Crear un índice para optimizar LIKE/ILIKE

Para aprovechar esta optimización, podemos crear un índice GIN sobre la columna que nos interesa así:

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

Con este índice, las consultas LIKE e ILIKE con patrones que llevan comodines en los dos extremos se ejecutan mucho más rápido, gracias a la preselección de candidatos basada en trigramas.

Hagamos una prueba real

Para hacer una prueba real, crea una tabla de medio millón de registros con el siguiente script.

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   

EXPLAIN ANALYZE dice que la consulta tarda unos 160ms en ejecutarse.

explain analyze select * from random_people where last_name ilike '%tom%';  -- aprox. 160 ms en mi máquina 

Ahora crea un índice GIN sobre el campo last_name usando la clase de operadores 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);

Y ahora, la prueba final. Vuelve a ejecutar la consulta anterior.

explain analyze select * from random_people where last_name ilike '%tom%'; -- ¡aprox. 0.025 ms en mi máquina!

¡Estupendo! De 160ms a 0.025ms (en mi máquina).

Reflexiones finales

Usar pg_trgm para optimizar las consultas LIKE e ILIKE es un ejemplo perfecto de cómo las extensiones de PostgreSQL pueden mejorar el rendimiento y la flexibilidad de las aplicaciones. Conviene recordar que, aunque los índices de trigramas pueden acelerar mucho estas búsquedas, también ocupan espacio adicional y pueden afectar a los tiempos de inserción de datos. Por eso, como siempre en ingeniería, hay que valorar bien su uso según los requisitos concretos de la aplicación.

En resumen, integrar pg_trgm en tus estrategias de indexación y búsqueda puede cambiar radicalmente el rendimiento de las consultas de texto. “Sapere aude” (atrévete a saber): esta extensión te invita a explorar y exprimir a fondo las capacidades avanzadas de PostgreSQL para gestionar datos de texto.

Cursos de PostgreSQL

Si quieres dominar PostgreSQL, mi empresa ofrece algunos cursos muy solicitados:

• PostgreSQL for Developers, en inglés
• PostgreSQL per Sviluppatori, en italiano
• PostgreSQL per Amministratori, en italiano

Si tu empresa necesita un curso a medida, escríbenos.

Los cursos están disponibles en remoto y en presencial.

Enlaces

• Documentación oficial de pg_trgm
• Todas las funciones que ofrece la extensión pg_trgm