Estamos preparando una búsqueda de texto completo en Postgres. Parte 1

UPD Parte 2

Este artículo es el primero de una pequeña serie de artículos sobre cómo configurar de forma óptima la búsqueda de texto completo en PostgreSQL. Recientemente tuve que resolver un problema similar en el trabajo, y me sorprendió mucho la ausencia de al menos algunos materiales sanos sobre este tema. Mi experiencia de pelear bajo el corte.

Empate

Apoyo un proyecto relativamente grande que tiene una búsqueda pública de documentos. La base de datos contiene ~ 500 mil documentos con un volumen total de ~ 3.6 GB. La esencia de la búsqueda es esta: el usuario llena un formulario en el que hay una consulta de texto completo y un filtro por una variedad de campos en la base de datos, incluso con join-s.

La búsqueda funciona (o mejor dicho, funcionó) a través de Sphinx, y no funcionó muy bien. Los principales problemas fueron los siguientes:

1. La indexación consumió alrededor de 8 GB de RAM. En un servidor con 8 GB de RAM, esto es un problema. Memoria intercambiada, condujo a un rendimiento terrible .
2. El índice se construyó en unos 40 minutos. No hubo dudas sobre la consistencia de los resultados de búsqueda; la indexación se lanzó una vez al día.
3. La búsqueda funcionó durante mucho tiempo . Se llevaron a cabo solicitudes por un tiempo particularmente largo, que correspondía a una gran cantidad de documentos: una gran cantidad de id-shniks tuvieron que ser transferidos de la esfinge a la base de datos, y ordenados por relevancia en el backend.

Debido a estos problemas, surgió la tarea: optimizar la búsqueda de texto completo. Esta tarea tiene dos soluciones:

1. Apriete Sphinx: configure un índice en tiempo real, almacene atributos para filtrar en el índice.
2. Utilice el FTS PostgreSQL incorporado.

Se decidió implementar la segunda solución: de esta manera, puede proporcionar de forma nativa la actualización automática del índice, deshacerse de la comunicación prolongada entre dos servicios y monitorear un servicio en lugar de dos.

Parecería ser una buena solución. Pero hay problemas por delante.

Comencemos desde el principio.

Usamos ingenuamente la búsqueda de texto completo

Como dice la documentación, las búsquedas de texto completo requieren el uso de los tsquery tsvector y tsquery . El primero almacena el texto del documento en un formulario optimizado para búsqueda, el segundo almacena la consulta de texto completo.

Para buscar PostgreSQL, hay funciones to_tsvector , plainto_tsquery , to_tsquery . Para clasificar los resultados hay ts_rank . Su uso es intuitivo y están bien descritos en la documentación , por lo que no nos detendremos en los detalles de su uso.

Una consulta de búsqueda tradicional que los use se verá así:

 SELECT id, ts_rank(to_tsvector("document_text"), plainto_tsquery('')) FROM documents_document WHERE to_tsvector("document_text") @@ plainto_tsquery('') ORDER BY ts_rank(to_tsvector("document_text"), plainto_tsquery('')) DESC; 

Deducimos los id-s de los documentos en el texto de los cuales existe la palabra "consulta", y los ordenamos en orden descendente de relevancia. ¿Todo parece estar bien? No

El enfoque anterior tiene muchas desventajas:

1. No utilizamos el índice para la búsqueda.
2. Se llama a la función ts_vector para cada fila de la tabla.
3. La función ts_rank se llama para cada fila de la tabla.

Todo esto lleva al hecho de que la búsqueda lleva mucho tiempo. EXPLAIN resultados en una base de combate:

 Gather Merge (actual time=420289.477..420313.969 rows=58742 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual time=420266.150..420267.935 rows=19581 loops=3) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 2278kB -> Parallel Seq Scan on documents_document (actual time=65.454..420235.446 rows=19581 loops=3) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 140636 Planning time: 3.706 ms Execution time: 420315.895 ms 

420 segundos! Por una solicitud!

La base también genera muchos vorings de la [54000] word is too long to be indexed forma [54000] word is too long to be indexed . No hay nada de qué preocuparse. La razón es que en mi base de datos hay documentos creados en el editor WYSIWYG. Inserta mucho   siempre que sea posible, y hay 54 mil seguidos. Postgres ignora palabras de esta longitud y escribe un vorning que no se puede deshabilitar.

Intentaremos solucionar todos los problemas observados y acelerar la búsqueda.

Ingenuamente optimizamos la búsqueda

No jugaremos con la base de combate, por supuesto, crearemos una base de prueba. Contiene ~ 12 mil documentos. La solicitud del ejemplo es de ~ 35 segundos. ¡Imperdonablemente largo!

 Sort (actual time=35431.874..35432.208 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Seq Scan on documents_document (actual time=8.470..35429.261 rows=3593 loops=1) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 9190 Planning time: 0.200 ms Execution time: 35432.294 ms 

Indice

En primer lugar, por supuesto, debe agregar un índice. La forma más fácil: un índice funcional.

 CREATE INDEX idx_gin_document ON documents_document USING gin (to_tsvector('russian', "document_text")); 

Tal índice se creará durante mucho tiempo: tomó ~ 26 segundos en la base de prueba. Necesita revisar la base de datos y llamar a la función to_tsvector para cada registro. Aunque todavía acelera la búsqueda a 12 segundos, ¡todavía es imperdonablemente largo!

 Sort (actual time=12213.943..12214.327 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector('russian'::regconfig, document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Bitmap Heap Scan on documents_document (actual time=3.849..12212.248 rows=3593 loops=1) Recheck Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Heap Blocks: exact=946 -> Bitmap Index Scan on idx_gin_document (actual time=0.427..0.427 rows=3593 loops=1) Index Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Planning time: 0.109 ms Execution time: 12214.452 ms 

Llamada repetida to_tsvector

Para resolver este problema, debe almacenar tsvector en la base de datos. Al cambiar los datos en una tabla con documentos, por supuesto, debe actualizarlos, a través de disparadores en la base de datos, utilizando el back-end.

Hay dos formas de hacer esto:

1. Agregue una columna de tipo tsvector a la tabla con documentos.
2. Cree una tabla separada con comunicación uno a uno con la tabla de documentos y almacene los vectores allí.

Las ventajas del primer enfoque: la falta de combinaciones en la búsqueda.
Las ventajas del segundo enfoque: la falta de datos adicionales en la tabla con los documentos, sigue siendo del mismo tamaño que antes. Con la copia de seguridad, no tiene que perder tiempo ni colocarla en tsvector , que no necesita hacer una copia de seguridad.

Ambos viajes conducen al hecho de que los datos en el disco se duplican: los textos de los documentos y sus vectores se almacenan.

Elegí el segundo enfoque para mí, sus ventajas son más significativas para mí.

 CREATE INDEX idx_gin_document ON documents_documentvector USING gin ("document_text"); 

 SELECT documents_document.id, ts_rank("text", plainto_tsquery('')) FROM documents_document LEFT JOIN documents_documentvector ON documents_document.id = documents_documentvector.document_id WHERE "text" @@ plainto_tsquery('') ORDER BY ts_rank("text", plainto_tsquery('')) DESC; 

Agregue datos a la tabla vinculada y cree un índice. Agregar datos tomó 24 segundos como prueba, y crear un índice solo tomó 2.7 segundos . La actualización del índice y los datos, como vemos, no se aceleró significativamente, pero el índice en sí mismo ahora se puede actualizar muy rápidamente.

¿Y cuántas veces se ha acelerado la búsqueda?

 Sort (actual time=48.147..48.432 rows=3593 loops=1) Sort Key: (ts_rank(documents_documentvector.text, plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Hash Join (actual time=2.281..47.389 rows=3593 loops=1) Hash Cond: (documents_document.id = documents_documentvector.document_id) -> Seq Scan on documents_document (actual time=0.003..2.190 rows=12783 loops=1) -> Hash (actual time=2.252..2.252 rows=3593 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 543kB -> Bitmap Heap Scan on documents_documentvector (actual time=0.465..1.641 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.404..0.404 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.410 ms Execution time: 48.573 ms 

 SELECT id, ts_rank("text", plainto_tsquery('')) AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

  Ordenar (tiempo real = 44.339..44.487 filas = 3593 bucles = 1)
   Clave de clasificación: (ts_rank (text, plainto_tsquery ('query' :: text)))
   Método de clasificación: memoria rápida: 265kB
   -> Escaneo de montón de mapa de bits en documentos_documentovector (tiempo real = 0.692..43.682 filas = 3593 bucles = 1)
         Vuelva a verificar Cond: (text @@ plainto_tsquery ('query' :: text))
         Bloques de montón: exactos = 577
         -> Escaneo de índice de mapa de bits en idx_gin_document (tiempo real = 0.577..0.577 filas = 3593 bucles = 1)
               Índice Cond: (text @@ plainto_tsquery ('query' :: text))
 Tiempo de planificación: 0.182 ms
 Tiempo de ejecución: 44.610 ms

Increíble! Y esto a pesar de unirse y ts_rank . Ya es un resultado bastante aceptable, la mayor parte del tiempo se ts_rank no por la búsqueda, sino por el cálculo de ts_rank para cada una de las líneas.

ts_rank llamada múltiple

Parece que hemos resuelto con éxito todos nuestros problemas, excepto este. 44 milisegundos es un tiempo de entrega decente. ¿Feliz final parece cercano? Ahí estaba!

Ejecute la misma consulta sin ts_rank y compare los resultados.

 SELECT document_id, 1 AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

 Bitmap Heap Scan on documents_documentvector (actual time=0.503..1.609 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.439..0.439 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.147 ms Execution time: 1.715 ms 

1,7 ms! ¡Treinta veces más rápido! Para una base de combate, los resultados son ~ 150 ms y 1.5 segundos. La diferencia en cualquier caso es un orden de magnitud, y 1.5 segundos no es el tiempo que desea esperar una respuesta de la base. Que hacer

No puede desactivar la clasificación por relevancia; no puede reducir el número de líneas para contar (la base de datos debe calcular ts_rank para todos ts_rank documentos ts_rank , de lo contrario no se pueden clasificar).

En algunos lugares de Internet, se recomienda almacenar en caché las solicitudes más frecuentes (y, en consecuencia, llamar a ts_rank). Pero no me gusta este enfoque: es bastante difícil seleccionar las consultas correctas correctamente, y la búsqueda aún se ralentizará en las consultas incorrectas.

Me gustaría mucho que después de revisar el índice, los datos vinieran en una forma ya ordenada, como lo hace Sphinx. Desafortunadamente, no se puede hacer nada desde el cuadro en PostgreSQL.

Pero tuvimos suerte: el índice RUM puede hacer esto. Se pueden encontrar detalles al respecto, por ejemplo, en la presentación de sus autores . Almacena información adicional sobre la solicitud, lo que le permite evaluar directamente la llamada. "distancia" entre tsvector y tsquery y producir un resultado ordenado inmediatamente después de escanear el índice.

Pero lanzar un GIN e instalar RUM no vale la pena de inmediato. Tiene desventajas, ventajas y límites de aplicación. Escribiré sobre esto en el próximo artículo.