performance - how - postgresql indexes best practices

¿Por qué los índices GiST de búsqueda de texto de PostgreSQL son mucho más lentos que los índices GIN?

Question

Estoy probando las funciones de búsqueda de texto de PostgreSQL, utilizando el volcado de datos de septiembre de StackOverflow como datos de muestra. :-)

El enfoque ingenuo de usar predicados LIKE o la coincidencia de expresiones regulares POSIX para buscar 1.2 millones de filas toma alrededor de 90-105 segundos (en mi Macbook) para hacer una búsqueda completa de tablas en busca de una palabra clave.

SELECT * FROM Posts WHERE body LIKE ''%postgresql%'';
SELECT * FROM Posts WHERE body ~ ''postgresql'';

Una consulta de búsqueda de texto ad hoc sin indexar toma aproximadamente 8 minutos :

SELECT * FROM Posts WHERE to_tsvector(body) @@ to_tsquery(''postgresql''); 

La creación de un índice GIN tarda unos 40 minutos :

ALTER TABLE Posts ADD COLUMN PostText TSVECTOR;
UPDATE Posts SET PostText = to_tsvector(body);
CREATE INDEX PostText_GIN ON Posts USING GIN(PostText);

(Comprendo que también podría hacer esto en un solo paso definiéndolo como un índice de expresión).

Posteriormente, una consulta asistida por un índice GIN se ejecuta mucho más rápido: esto lleva aproximadamente 40 milisegundos :

SELECT * FROM Posts WHERE PostText @@ ''postgresql''; 

Sin embargo, cuando creo un índice GiST, los resultados son bastante diferentes. Se tarda menos de 2 minutos en crear el índice:

CREATE INDEX PostText_GIN ON Posts USING GIST(PostText);

Luego, una consulta que utiliza el operador de búsqueda @@ textual toma 90-100 segundos . Así que los índices GiST mejoran una consulta de TS no indexada de 8 minutos a 1,5 minutos. Pero eso no es una mejora con respecto a hacer un escaneo completo de la tabla con LIKE . Es inútil en un entorno de programación web.

¿Me estoy perdiendo algo crucial para usar los índices GiST? ¿Es necesario que los índices se almacenen previamente en la memoria caché o algo así? Estoy usando una instalación simple de PostgreSQL desde MacPorts, sin ajuste.

¿Cuál es la forma recomendada de usar los índices GiST? ¿O todos los que hacen TS con PostgreSQL omiten los índices GiST y usan solo los índices GIN?

PD: Sé de alternativas como Sphinx Search y Lucene. Solo estoy tratando de aprender acerca de las características proporcionadas por el propio PostgreSQL.

Answer 1

Los documentos tienen una buena descripción de las diferencias de rendimiento entre los índices GiST y GIN si está interesado: los tipos de índice GiST y GIN .

Answer 2

por cierto: si esto aún no ha sido respondido satisfactoriamente, la parte donde lo hizo

SELECT * FROM Posts WHERE PostText @@ ''postgresql'';

debería haber sido

SELECT * FROM Posts WHERE PostText @@ to_tsquery(''postgresql'');

Answer 3

tratar

CREATE INDEX PostText_GIST ON Posts USING GIST(PostText varchar_pattern_ops);

que crea un índice adecuado para consultas de prefijo. Consulte la documentación de PostgreSQL sobre Clases de operadores y Familias de operadores . El operador @@ solo es sensible en los vectores de términos; el índice GiST (con varchar_pattern_ops) dará excelentes resultados con LIKE.