Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

Su consulta es prácticamente la óptima. La sintaxis no será mucho más corta, la consulta no será mucho más rápida:

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; 

Si y Realmente desea acortar la sintaxis , use una expresión regular con ramas :

... WHERE name ~ "^(B|D).*" 

O ligeramente más rápido, con una clase de caracteres :

... WHERE name ~ "^[BD].*" 

Una prueba rápida sin índice produce resultados más rápidos que para SIMILAR TO en cualquier caso para mí.
Con un índice B-Tree apropiado en su lugar, LIKE gana esta carrera por órdenes de magnitud.

Lea los conceptos básicos sobre coincidencia de patrones en el manual .

Índice para un rendimiento superior

Si está preocupado con rendimiento, cree un índice como este para tablas más grandes:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Hace que este tipo de consulta sea más rápida en órdenes de magnitud. Se aplican consideraciones especiales para el orden de clasificación específico de la configuración regional. Obtenga más información sobre las clases de operador en el manual . Si está utilizando la configuración regional » C » estándar (la mayoría de la gente no lo hace), un índice simple (con la clase de operador predeterminada) hacer.

Este índice solo es bueno para patrones anclados a la izquierda (que coinciden desde el inicio de la cadena).

SIMILAR TO o Las expresiones regulares con expresiones básicas ancladas a la izquierda también pueden usar este índice. Pero no con ramas (B|D) o clases de caracteres [BD] (al menos en mis pruebas en PostgreSQL 9.0).

Las coincidencias de trigram o la búsqueda de texto utilizan índices especiales GIN o GiST.

Descripción general de los operadores de coincidencia de patrones

• LIKE ( ~~ ) es simple y rápido pero limitado en sus capacidades.
  ILIKE ( ~~* ) la variante que no distingue entre mayúsculas y minúsculas.
  pg_trgm amplía la compatibilidad con índices para ambos.

• ~ (coincidencia de expresión regular) es poderoso pero más complejo y puede ser lento para cualquier cosa más que expresiones básicas.

• SIMILAR TO es simplemente inútil . Un mestizo peculiar de LIKE y expresiones regulares. Yo nunca lo uso. Consulte a continuación.

• % es el operador » similitud «, proporcionado por el módulo adicional pg_trgm. Consulte a continuación.

• @@ es el operador de búsqueda de texto. Consulte a continuación.

pg_trgm – coincidencia de trigram

Comenzando con PostgreSQL 9.1 puede facilitar la extensión pg_trgm para proporcionar compatibilidad con índices para cualquier LIKE / ILIKE patrón (y patrones simples de expresiones regulares con ~) usando un Índice GIN o GiST.

Detalles, ejemplo y enlaces:

• ¿Cómo se implementa LIKE?

pg_trgm también proporciona estos operadores :

• % – el » similarity » operador
• <% (conmutador: %>) – la » word_similarity » operador en Postgres 9.6 o posterior
• <<% (conmutador: %>>) – la » estricta_word_similarity » operador en Postgres 11 o posterior

Búsqueda de texto

Es un tipo especial de coincidencia de patrones con tipos de índices e infraestructura separados. Utiliza diccionarios y lematización y es una gran herramienta para encontrar palabras en documentos, especialmente para lenguajes naturales.

Prefijo coincidente también es compatible:

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

Además de la búsqueda de frases desde Postgres 9.6:

• ¿Cómo buscar palabras con guiones en la búsqueda de texto completo de PostgreSQL?

Considere la introducción en el manual y la descripción general de operadores y funciones .

Herramientas adicionales para la coincidencia aproximada de cadenas

El módulo adicional fuzzystrmatch ofrece algunas opciones más, pero el rendimiento es generalmente inferior a todas las anteriores.

En particular, varias Las implementaciones de la función levenshtein() pueden ser fundamentales.

¿Por qué las expresiones regulares (~) siempre son más rápidas que SIMILAR TO?

La respuesta es simple. SIMILAR TO Las expresiones se reescriben internamente en expresiones regulares. Entonces, por cada SIMILAR TO expresión, hay al menos una expresión regular más rápida (que ahorra la sobrecarga de reescribir la expresión). No hay ganancia de rendimiento al usar SIMILAR TO alguna vez .

Y las expresiones simples que se pueden hacer con LIKE (~~) son más rápidas con LIKE de todos modos.

SIMILAR TO solo es compatible con PostgreSQL porque terminó en los primeros borradores del estándar SQL. Todavía no se han deshecho de él. Pero hay planes para eliminarlo e incluir coincidencias de expresiones regulares en su lugar, o eso he oído.

EXPLAIN ANALYZE lo revela. ¡Pruébelo usted mismo con cualquier tabla!

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 

Revela:

... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) 

SIMILAR TO se ha reescrito con una expresión regular (~).

Rendimiento máximo para este caso particular

Pero EXPLAIN ANALYZE revela más. Intente, con el índice mencionado anteriormente en su lugar:

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; 

Revela:

... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text)) 

Internamente, con un índice que no reconoce la configuración regional (text_pattern_ops o utiliza la configuración regional C) las expresiones simples ancladas a la izquierda se reescriben con estos operadores de patrón de texto: ~>=~, ~<=~, ~>~, ~<~. Este es el caso de ~, ~~ o SIMILAR TO por igual.

Lo mismo ocurre con los índices en los tipos varchar con varchar_pattern_ops o char con bpchar_pattern_ops.

Entonces, aplicado a la pregunta original, esta es la forma más rápida posible :

SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 

Por supuesto, si busca iniciales adyacentes , puede simplificar aún más:

WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C 

La ganancia sobre el uso simple de ~ o ~~ es pequeña. Si el rendimiento no es su requisito primordial, debe seguir con los operadores estándar y llegar a lo que ya tiene en la pregunta.

Comentarios

• El OP no ‘ no tiene un índice de nombre, pero ¿sabe usted que, si lo tuviera, su consulta original implicaría 2 búsquedas de rango y similar ¿un escaneo?
• @MartinSmith: Una prueba rápida con EXPLAIN ANALYZE muestra 2 escaneos de índice de mapa de bits.Se pueden combinar varios escaneos de índice de mapa de bits con bastante rapidez.
• Gracias. Entonces, ¿habría algún kilometraje al reemplazar el OR con UNION ALL o al reemplazar name LIKE 'B%' con name >= 'B' AND name <'C' en Postgres?
• @MartinSmith: UNION ganó ‘ t pero sí, la combinación de los rangos en una sola cláusula WHERE acelerará la consulta. He agregado más a mi respuesta. Por supuesto, debe tener en cuenta su configuración regional. La búsqueda basada en la configuración regional es siempre más lenta.
• @a_horse_with_no_name: Espero que no. Las nuevas capacidades de pg_tgrm con índices GIN son un placer para la búsqueda de texto genérico. Una búsqueda anclada al principio ya es más rápida que eso.

¿Qué tal si agregas una columna al mesa. Dependiendo de sus requisitos reales:

person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) 

PostgreSQL no «admite columnas calculadas en tablas base a la SQL Server , pero la nueva columna se puede mantener a través del activador. Obviamente, esta nueva columna se indexaría.

Alternativamente, un índice en una expresión le daría lo mismo, más barato. Por ejemplo:

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Las consultas que coinciden con la expresión en sus condiciones pueden utilizar este índice.

De esta manera, el impacto en el rendimiento se toma cuando se crean o modifican los datos, por lo que solo puede ser apropiado para un entorno de baja actividad (es decir, muchas menos escrituras que lecturas).

Podría probar

SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 

Sin embargo, no tengo idea de si lo anterior o su expresión original se pueden comparar en Postgres.

Si crea el índice sugerido, también estaría interesado en saber cómo esto se compara con las otras opciones.

SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 

Comentarios

• Funcionó y obtuve un costo de 1,19 donde tenía 1,25. ¡Gracias!

Lo que he hecho en el pasado, ante un problema de rendimiento similar, es Incrementa el carácter ASCII de la última letra y haz un BETWEEN. A continuación, obtiene el mejor rendimiento para un subconjunto de la funcionalidad LIKE. Por supuesto, solo funciona en ciertas situaciones, pero para conjuntos de datos ultra grandes en los que, por ejemplo, estás buscando un nombre, hace que el rendimiento pase de abismal a aceptable.

Pregunta muy antigua, pero encontré otra solución rápida a este problema:

SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 

Dado que la función ascii ( ) mira solo el primer carácter de la cadena.

Comentarios

• ¿Esto usa un índice en (name)?

Para comprobar las iniciales, suelo utilizar la conversión a "char" (con las comillas dobles). No es portátil, pero es muy rápido. Internamente, simplemente desintegra el texto y devuelve el primer carácter, y las operaciones de comparación «char» son muy rápidas porque el tipo tiene una longitud fija de 1 byte:

SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 

Tenga en cuenta que la conversión a "char" es más rápida que la ascii() de @ Sole021, pero no es compatible con UTF8 (o cualquier otra codificación para eso importa), devolviendo simplemente el primer byte, por lo que solo debe usarse en los casos en que la comparación sea con caracteres ASCII de 7 bits antiguos.