similar - Variaciones de rendimiento de la consulta LIKE de PostgreSQL

similar text postgresql

Question

He visto una variación bastante grande en los tiempos de respuesta con respecto a las consultas LIKE a una tabla en particular en mi base de datos. A veces obtengo resultados dentro de 200-400 ms (muy aceptable) pero otras veces puede tardar hasta 30 segundos para devolver los resultados.

Entiendo que las consultas LIKE consumen muchos recursos, pero no entiendo por qué habría una diferencia tan grande en los tiempos de respuesta. Construí un índice btree en el campo owner1 pero no creo que sea útil con las consultas LIKE . ¿Alguien tiene alguna idea?

Ejemplo de SQL:

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE ''%someones name%'' LIMIT 10

También lo intenté:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower(''%someones name%'') LIMIT 10

Y:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower(''someones name%'') LIMIT 10

Con resultados similares.
Recuento de filas de tabla: aproximadamente 95,000.

Answer 1

FTS no es compatible con LIKE

La respuesta previamente aceptada fue incorrecta. La búsqueda de texto completo con sus índices de texto completo no es para el operador LIKE en absoluto, tiene sus propios operadores y no funciona para cadenas arbitrarias. Opera en palabras basadas en diccionarios y derivaciones. Admite la coincidencia de prefijos para palabras , pero no con el operador LIKE :

• Obtenga una coincidencia parcial de la columna GIN indexada de TSVECTOR

Índices de trigrama para LIKE

Instale el módulo adicional pg_trgm que proporciona clases de operador para los índices pg_trgm GIN y GiST para admitir todos los patrones LIKE e ILIKE , no solo los anclados a la izquierda:

Índice de ejemplo:

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

O:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

• Diferencia entre el índice GiST y GIN

Consulta de ejemplo:

SELECT * FROM tbl WHERE col LIKE ''%foo%'';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE ''%foo%'';  -- works case insensitively as well

Trigrams? ¿Qué hay de las cuerdas más cortas?

Las palabras con menos de 3 letras en valores indexados aún funcionan. El manual:

Se considera que cada palabra tiene dos espacios prefijados y un espacio con sufijo al determinar el conjunto de trigramas contenidos en la cadena.

¿Y patrones de búsqueda con menos de 3 letras? El manual:

Para las búsquedas LIKE y de expresión regular, tenga en cuenta que un patrón sin trigramas extraíbles se degenerará en un escaneo de índice completo.

Es decir, que los escaneos índice / mapa de bits todavía funcionan (los planes de consulta para la declaración preparada no se romperán), simplemente no le comprará un mejor rendimiento. Normalmente no es una gran pérdida, ya que las cadenas de 1 o 2 letras son poco selectivas (más de un pequeño porcentaje de las coincidencias de tabla subyacentes) y el soporte de índice no mejoraría el rendimiento, porque una exploración de tabla completa es más rápida.

text_pattern_ops para la coincidencia de prefijos

Para los patrones simplemente anclados a la izquierda (sin comodín principal) se obtiene el óptimo con una clase de operador adecuada para un índice btree: text_pattern_ops o varchar_pattern_ops . Ambas funciones integradas de Postgres estándar, no se necesita ningún módulo adicional. Rendimiento similar, pero índice mucho más pequeño.

Índice de ejemplo:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

Consulta de ejemplo:

SELECT * FROM tbl WHERE col LIKE ''foo%'';  -- no leading wildcard

O bien , si debe ejecutar su base de datos con la configuración regional "C" (en realidad no hay configuración regional), todo se ordena de acuerdo con el orden de bytes de todos modos y un simple índice btree con clase de operador predeterminada hace el trabajo.

Más detalles, explicación, ejemplos y enlaces en estas respuestas relacionadas en dba.SE:

• Patrón coincidente con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL
• ¿Cómo se implementa LIKE?
• Encontrar cadenas similares con PostgreSQL rápidamente

Answer 2

Ejecute la consulta mencionada a continuación para mejorar el rendimiento de la consulta LIKE en postgresql. crea un índice como este para tablas más grandes:

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)

Answer 3

Es probable que sus consultas similares no puedan usar los índices que creó porque:

1) su criterio LIKE comienza con un comodín.

2) has usado una función con tu criterio LIKE.

Answer 4

Posiblemente los rápidos son patrones anclados con mayúsculas y minúsculas que pueden usar índices. es decir, no hay una carta comodín al comienzo de la cadena de partido, por lo que el ejecutor puede usar una exploración de rango de índice. ( el comentario relevante en los documentos está aquí ) Lower and ilike también perderá su capacidad de usar el índice a menos que cree específicamente un índice para ese propósito (vea índices funcionales ).

Si desea buscar una cadena en el medio del campo, debe buscar texto completo o índices de trigrama . El primero de ellos está en el núcleo de Postgres, el otro está disponible en los módulos contrib.

Answer 5

Puede instalar Wildspeed , un tipo diferente de índice en PostgreSQL. Wildspeed funciona con% word% wildcards, no hay problema. La desventaja es el tamaño del índice, puede ser grande, muy grande.