Solución:
Tu 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 de verdad quieres acortar la sintaxis, usa una expresión regular con sucursales:
...
WHERE name ~ '^(B|D).*'
O un poco más rápido, con un clase de personaje:
...
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 la coincidencia de patrones en el manual.
Índice de rendimiento superior
Si le preocupa el 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 por órdenes de magnitud. Se aplican consideraciones especiales para el orden de clasificación específico de la configuración regional. Lea más sobre las clases de operadores 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) será suficiente.
Dicho índice solo es bueno para patrones anclados a la izquierda (coincidencia desde el inicio de la string).
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 personajes [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 el soporte de índice para ambos. -
~(coincidencia de expresión regular) es potente pero más complejo y puede ser lento para cualquier cosa más que expresiones básicas. -
SIMILAR TOes solo inútil. Un peculiar mestizo deLIKEy expresiones regulares. Yo nunca lo uso. Vea abajo. -
% es el operador de “similitud”, proporcionado por el módulo adicional
pg_trgm. Vea abajo. -
@@es el operador de búsqueda de texto. Vea abajo.
pg_trgm – coincidencia de trigram
Empezando con PostgreSQL 9.1 puedes facilitar la extensión pg_trgm para proporcionar soporte de índice para algunaLIKE / ILIKE patrón (y patrones simples de expresiones regulares con ~) utilizando un índice GIN o GiST.
Detalles, ejemplo y enlaces:
- ¿Cómo se implementa LIKE?
pg_trgm también proporciona estos operadores:
%– el operador de “similitud”<%(conmutador:%>) - el operador "word_similarity" en Postgres 9.6 o posterior<<%(conmutador:%>>) - el operador "strict_word_similarity" en Postgres 11 o posterior
Búsqueda de texto
Es un tipo especial de coincidencia de patrones con tipos de índices e infraestructura independientes. Utiliza diccionarios y lematización y es una gran herramienta para encontrar palabras en documentos, especialmente para lenguajes naturales.
Coincidencia de prefijo también es compatible:
- Obtenga una coincidencia parcial de la columna TSVECTOR indexada por GIN
Al igual que 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 fuzzy string pareo
El módulo adicional fuzzystrmatch ofrece algunas opciones más, pero el rendimiento es generalmente inferior a todos los anteriores.
En particular, varias implementaciones del levenshtein() la función puede ser instrumental.
¿Por qué las expresiones regulares (~) siempre más rápido que SIMILAR TO?
La respuesta es simple. SIMILAR TO las expresiones se reescriben internamente en expresiones regulares. Entonces, para cada SIMILAR TO expresión, hay por lo 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 TOsiempre.
Y expresiones simples que se pueden hacer con LIKE (~~) son más rápidos con LIKE de todas formas.
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 escuché.
EXPLAIN ANALYZE lo revela. ¡Pruébelo usted mismo con cualquier mesa!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';
Revela:
...
Seq Scan on spelers (cost= ...
Filter: (name ~ '^(?:B.*)$'::text)
SIMILAR TO ha sido reescrito con una expresión regular (~).
Máximo rendimiento 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 usando locale C) las expresiones simples ancladas a la izquierda se reescriben con estos operadores de patrones de texto: ~>=~, ~<=~, ~>~, ~<~. Este es el caso de ~, ~~ o SIMILAR TO similar.
Lo mismo es true para índices en varchar tipos con varchar_pattern_ops o char con bpchar_pattern_ops.
Entonces, aplicado a la pregunta original, esta es la manera 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 buscara iniciales adyacentes, puede simplificar aún más:
WHERE name ~>=~ 'B' AND name ~<~ 'D' -- strings starting with B or C
La ganancia sobre el simple uso de ~ o ~~ es diminuto. 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.
¿Qué tal agregar una columna a la tabla? Dependiendo de sus necesidades 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 como SQL Server, pero la nueva columna se puede mantener mediante un disparador. Obviamente, esta nueva columna estaría indexada.
Alternativamente, un índice en una expresión le daría lo mismo, más barato. P.ej:
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).
Tu podrías intentar
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 le interesaría saber cómo 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
Si crees que ha resultado de ayuda nuestro artículo, agradeceríamos que lo compartas con otros entusiastas de la programación y nos ayudes a dar difusión a nuestro contenido.