PostgreSQL LIKE e ILIKE: por qué `'abc%'` no es lo mismo que `'%abc%'`

PostgreSQL LIKE e ILIKE: por qué 'abc%' y '%abc%' se comportan distinto#

Supongamos una tabla users con varios millones de filas y una columna username.

Al principio necesitas encontrar usuarios cuyo nombre empiece por "bodan":

SELECT *
FROM users
WHERE username LIKE 'bodan%';

La consulta responde bien con un índice adecuado.

Tiempo después cambia el requisito. Ahora "bodan" puede aparecer en cualquier posición:

SELECT *
FROM users
WHERE username LIKE '%bodan%';

La tabla es la misma. El texto buscado también. Pero el patrón de acceso acaba de cambiar por completo.

La tentación es concluir que LIKE es lento.

La pregunta más útil es otra:

¿qué parte del patrón puede utilizar PostgreSQL para reducir el espacio de búsqueda antes de empezar a comprobar valores?

Esa diferencia explica buena parte del rendimiento de LIKE, ILIKE, los índices B-tree y pg_trgm.

Cómo puede usar PostgreSQL un B-tree con LIKE#

Un índice B-tree mantiene sus claves ordenadas.

Eso permite localizar rangos sin recorrer todos los valores almacenados. Una búsqueda por prefijo puede aprovechar esa propiedad porque existe una parte inicial fija antes del primer comodín.

Por ejemplo:

SELECT *
FROM users
WHERE username LIKE 'bodan%';

Conceptualmente, PostgreSQL puede restringir la búsqueda a la zona del índice donde podrían existir valores que empiezan por bodan, en lugar de inspeccionar todo el árbol.

Pero hay una condición importante: la posibilidad de utilizar eficientemente un B-tree para pattern matching depende también de la collation y de la operator class del índice.

Con una collation compatible, como C, un B-tree normal puede ser suficiente para búsquedas por prefijo.

Con otras configuraciones regionales puede ser necesario un índice orientado específicamente a pattern matching.

Si username es text:

CREATE INDEX idx_users_username_pattern
ON users (username text_pattern_ops);

Si username es varchar, utiliza la operator class correspondiente:

CREATE INDEX idx_users_username_pattern
ON users (username varchar_pattern_ops);

La idea sigue siendo la misma: PostgreSQL necesita poder convertir el prefijo fijo en un rango útil dentro del índice.

No deberíamos interpretar esto como:

LIKE 'texto%' siempre usa un B-tree.

La formulación correcta es:

un patrón anclado al inicio puede proporcionar un rango utilizable por un B-tree cuando la collation y la operator class permiten ese acceso.

El prefijo termina en el primer comodín#

LIKE utiliza principalmente dos comodines:

• % representa cualquier secuencia de caracteres;
• _ representa un único carácter.

PostgreSQL puede aprovechar la parte fija situada antes del primer comodín.

Por ejemplo:

WHERE username LIKE 'bodan%'

tiene un prefijo fijo claro:

bodan

En cambio:

WHERE username LIKE 'bodan%t'

sigue teniendo bodan como parte fija inicial.

La t final puede comprobarse después sobre los candidatos encontrados, pero no permite construir un único rango continuo que salte directamente a todos los valores que empiezan por bodan y terminan en t.

Lo mismo ocurre con _:

WHERE username LIKE 'bod_n%'

El prefijo fijo termina antes de _.

La idea práctica es:

cuanto más útil sea la parte fija inicial del patrón, mayor oportunidad existe de reducir el conjunto de búsqueda con un índice ordenado.

Por qué LIKE '%bodan%' es otro problema#

Ahora consideremos:

SELECT *
FROM users
WHERE username LIKE '%bodan%';

El primer carácter del patrón ya es un comodín.

No existe un prefijo fijo como:

bodan...

que permita entrar directamente en una zona concreta del B-tree.

Un árbol ordenado por el valor completo de username no sabe si la cadena bodan aparecerá al principio, en el centro o cerca del final.

Por eso un B-tree convencional pierde gran parte de su utilidad para este patrón.

PostgreSQL puede terminar recorriendo una gran cantidad de entradas o elegir directamente un Seq Scan y aplicar el patrón como filtro.

Esta es la diferencia central del artículo.

No es:

LIKE rápido frente a LIKE lento.

Es:

patrón con un prefijo aprovechable frente a patrón sin un punto de entrada útil para un índice ordenado.

ILIKE añade el problema de la comparación case-insensitive#

ILIKE permite realizar pattern matching sin distinguir mayúsculas de minúsculas:

SELECT *
FROM users
WHERE username ILIKE 'bodan%';

No conviene asumir que este patrón podrá utilizar un B-tree exactamente de la misma forma que:

LIKE 'bodan%'

Una estrategia habitual cuando necesitamos búsquedas case-insensitive por prefijo consiste en normalizar la expresión consultada.

Por ejemplo:

CREATE INDEX idx_users_lower_username
ON users (LOWER(username));

y consultar:

SELECT *
FROM users
WHERE LOWER(username) LIKE 'bodan%';

Ahora la expresión del índice coincide con la expresión utilizada en el filtro.

Cuando la collation requiere una operator class específica para pattern matching, conviene expresarlo de forma explícita.

Para una columna text:

CREATE INDEX idx_users_lower_username_pattern
ON users ((LOWER(username)) text_pattern_ops);

Si trabajas con otro tipo, comprueba la operator class correspondiente.

La doble pareja de paréntesis deja claro que LOWER(username) es la expresión indexada y text_pattern_ops la operator class aplicada a esa expresión.

La decisión importante aquí no es memorizar ese índice.

Es entender que la expresión de la consulta y la expresión indexada deben ser compatibles con el acceso que esperamos obtener.

Un índice funcional también tiene un coste: debe mantenerse cuando cambia username, y las consultas que quieran aprovecharlo deben utilizar una expresión compatible.

Cómo comprobar qué está ocurriendo con EXPLAIN ANALYZE#

No deberíamos decidir si un patrón está bien indexado únicamente mirando el SQL.

Hay que mirar el plan.

Los siguientes planes y tiempos son hipotéticos e ilustrativos. Sirven para mostrar cómo cambia el tipo de trabajo; no representan un benchmark reproducible para cualquier servidor. El resultado real depende del hardware, los datos, la selectividad, las estadísticas, la cache, la collation y la configuración.

Supongamos una tabla users con un millón de filas y un índice preparado correctamente para búsquedas por prefijo.

Analizamos:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE 'bodan%';

Un plan simplificado podría parecerse a:

Index Scan using idx_users_username_pattern on users
  Index Cond: (rango compatible con el prefijo 'bodan')
  Filter: (username ~~ 'bodan%'::text)
  Buffers: shared hit=3

Execution Time: 0.040 ms

Lo importante no son los 0.040 ms.

Es que existe una condición de índice que reduce el rango de valores que PostgreSQL necesita examinar.

La representación exacta del Index Cond puede variar según versión, collation y operator class.

Ahora cambiamos únicamente el patrón:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE '%bodan%';

Un posible plan sería:

Seq Scan on users
  Filter: (username ~~ '%bodan%'::text)
  Rows Removed by Filter: 999999
  Buffers: shared hit=4425

Execution Time: 45.705 ms

De nuevo, el tiempo es ilustrativo.

La información importante es:

Rows Removed by Filter: 999999

PostgreSQL tuvo que inspeccionar una cantidad enorme de candidatos para encontrar una fila.

Ahí está el coste.

pg_trgm para búsquedas en cualquier posición#

Si el requisito real es buscar texto en cualquier posición:

WHERE username LIKE '%bodan%'

un B-tree deja de encajar bien con el patrón de acceso.

Aquí pg_trgm ofrece otra estrategia.

pg_trgm es una extensión oficial de PostgreSQL que representa cadenas mediante trigramas: grupos de tres caracteres.

Para un texto como:

bodan

existen fragmentos útiles como:

bod
oda
dan

Esto permite construir un índice basado en fragmentos del texto en lugar de depender únicamente del comienzo del valor completo.

Primero habilitamos la extensión:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Este comando requiere que la extensión esté disponible en la instalación y que el usuario tenga los permisos necesarios.

Después podemos crear un índice GIN:

CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);

Ahora analizamos:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE '%bodan%';

Un plan hipotético podría ser:

Bitmap Heap Scan on users
  Recheck Cond: (username ~~ '%bodan%'::text)

  -> Bitmap Index Scan on idx_users_username_trgm
       Index Cond: (username ~~ '%bodan%'::text)

Execution Time: 0.150 ms

La parte importante es el cambio de estrategia:

Seq Scan

frente a una estrategia basada en:

Bitmap Index Scan

El índice de trigramas puede producir un conjunto de candidatos mucho más reducido antes de comprobar el patrón completo.

Otra vez: el objetivo no es conseguir exactamente ese nodo ni ese tiempo.

El objetivo es reducir el número de valores que PostgreSQL necesita comprobar.

pg_trgm también puede ayudar con ILIKE#

Una ventaja importante de pg_trgm es que sus operator classes pueden utilizarse con búsquedas LIKE e ILIKE.

Por ejemplo:

SELECT *
FROM users
WHERE username ILIKE '%bodan%';

puede beneficiarse de un índice trigram adecuado sin necesidad de que la búsqueda esté anclada al principio.

Eso hace que pg_trgm resulte especialmente útil cuando el requisito funcional es realmente:

buscar una secuencia de caracteres en cualquier posición y sin distinguir mayúsculas de minúsculas.

Pero eso no significa que deba utilizarse para cualquier búsqueda textual.

Si el único requisito es:

LIKE 'bodan%'

un índice B-tree apropiado puede resultar más simple.

La estructura debe responder al patrón real de consultas.

El límite de pg_trgm: el patrón tiene que aportar información#

Un índice de trigramas tampoco puede inventar selectividad.

Consideremos:

WHERE username LIKE '%bodan%'

El patrón contiene varios fragmentos que pueden ayudar a reducir candidatos.

Ahora comparemos con:

WHERE username LIKE '%a%'

El segundo patrón aporta mucha menos información.

Los patrones muy cortos pueden proporcionar pocos o ningún trigrama útil para reducir el espacio de búsqueda. Cuando no pueden extraerse trigramas utilizables, una búsqueda indexada puede terminar degradándose hacia una exploración muy amplia.

La regla práctica no debería ser:

tres caracteres = rápido.

La idea correcta es:

cuantos más trigramas útiles y selectivos proporciona el patrón, mayor oportunidad tiene el índice de descartar candidatos antes de comprobar el valor completo.

GIN o GiST con pg_trgm#

pg_trgm dispone de operator classes para índices GIN y GiST.

Ambas permiten acelerar búsquedas basadas en trigramas, pero no son estructuras equivalentes.

Para cargas centradas principalmente en búsquedas LIKE e ILIKE, un índice GIN suele ser una opción razonable para evaluar:

CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);

GiST tiene características diferentes y también puede ser útil para determinadas operaciones de similitud, incluidas búsquedas donde interesa trabajar con distancia.

En lugar de establecer una regla universal como:

GIN siempre es más rápido.

conviene comparar la elección con el workload real:

• frecuencia de búsquedas;
• frecuencia de escrituras;
• tamaño del índice;
• tipos de operadores utilizados;
• necesidad de búsquedas por similitud;
• distribución real de los textos.

El índice correcto depende del problema que queremos resolver.

La selectividad sigue importando con pg_trgm#

Que exista un índice trigram tampoco obliga a PostgreSQL a utilizarlo.

Supongamos:

WHERE username LIKE '%bodan%'

pero "bodan" aparece en una parte muy grande de la tabla.

El índice puede generar tantos candidatos que el planner estime más barato recorrer directamente la relación.

En cambio, un patrón raro:

WHERE username LIKE '%xyz123%'

puede reducir drásticamente el conjunto de candidatos y hacer mucho más atractivo un acceso mediante índice.

La misma pregunta que utilizamos para B-tree vuelve a aparecer:

¿cuánto trabajo elimina realmente el índice?

La presencia del índice no es suficiente.

La selectividad del patrón sigue condicionando su utilidad.

El coste de mantener un índice de trigramas#

Un índice con pg_trgm añade una estructura adicional que debe almacenarse y mantenerse.

Eso introduce varios costes:

• más espacio de almacenamiento;
• más trabajo durante INSERT;
• más trabajo durante UPDATE de la columna indexada;
• mantenimiento adicional;
• mayor complejidad del esquema.

No existe un tamaño universal para un índice trigram.

Depende del volumen, longitud y distribución de los textos, entre otros factores.

Por eso no tiene sentido crear uno únicamente porque una aplicación utilice LIKE.

Antes conviene responder:

• ¿Las búsquedas realmente contienen comodines iniciales?
• ¿Se ejecutan con suficiente frecuencia?
• ¿Los patrones suelen ser selectivos?
• ¿La tabla recibe muchas escrituras?
• ¿Una búsqueda por prefijo resolvería realmente el requisito?

Si el producto solo necesita:

LIKE 'abc%'

un B-tree configurado correctamente para pattern matching puede ser una solución más simple.

Si el requisito real es:

LIKE '%abc%'

o:

ILIKE '%abc%'

entonces pg_trgm empieza a responder a un problema distinto.

Qué revisar cuando LIKE o ILIKE es lento#

Cuando una consulta de búsqueda empieza a degradarse, este es un proceso más útil que añadir índices al azar:

1. Mira el patrón real. ¿Empieza por texto fijo o por %/_?
2. Ejecuta EXPLAIN (ANALYZE, BUFFERS).
3. Comprueba el tipo de scan.
4. Revisa cuántas filas se procesan y cuántas se descartan.
5. Comprueba la collation y la operator class del índice.
6. Para búsquedas por prefijo, evalúa B-tree y text_pattern_ops o varchar_pattern_ops cuando corresponda.
7. Para búsquedas case-insensitive por prefijo, evalúa un índice funcional compatible con LOWER().
8. Para búsquedas con comodín inicial o en cualquier posición, evalúa pg_trgm.
9. Mide la selectividad del patrón.
10. Compara el nuevo plan con el anterior antes de considerar terminada la optimización.

El objetivo no es conseguir que PostgreSQL utilice un índice.

El objetivo es que el acceso elegido reduzca una cantidad significativa de trabajo.

Conclusión#

LIKE 'abc%' y LIKE '%abc%' parecen pequeñas variaciones de la misma consulta, pero plantean problemas de acceso diferentes.

Cuando existe un prefijo fijo, un B-tree correctamente configurado puede utilizar el orden de sus claves para reducir la zona que PostgreSQL necesita explorar.

Cuando el patrón empieza con un comodín, ese punto de entrada desaparece. Si la aplicación necesita buscar texto en cualquier posición, pg_trgm ofrece una estructura más adecuada porque trabaja con fragmentos del texto en lugar de depender únicamente del comienzo del valor.

ILIKE, la collation, las operator classes y la selectividad añaden más condiciones al problema.

Por eso la pregunta útil no es:

¿qué índice hace rápido LIKE?

Es:

¿qué información aporta mi patrón para descartar datos y qué estructura permite a PostgreSQL aprovecharla?

La optimización empieza entendiendo esa diferencia.

Para un proceso más amplio de diagnóstico, consulta nuestra guía de optimización de consultas PostgreSQL, donde explicamos cómo interpretar planes de ejecución, estadísticas e índices antes de modificar una consulta.