Hogar › Ajustes › Búsqueda de texto completo en Postgresql. H Uso de indexación de texto completo y búsqueda en PostgreSQL en borradores. Uso de indexación y búsqueda de texto completo en PostgreSQL

Búsqueda de texto completo en Postgresql. H Uso de indexación de texto completo y búsqueda en PostgreSQL en borradores. Uso de indexación y búsqueda de texto completo en PostgreSQL

Hola a todos,

Descrito capacidades básicas Pros y contras de utilizar la búsqueda de texto completo incorporada del DBMS de Postgre según su experiencia. uso práctico.

A la hora de desarrollar aplicaciones, especialmente aplicaciones web, en el 95% de los casos surge la tarea de elegir sistemas para gestionar tanto contenidos estructurados como no estructurados ( información de texto estructura arbitraria), así como datos multimedia (fuera del alcance de este artículo).
El arquitecto de la aplicación hace la pregunta: combine estos datos bajo el control de un DBMS o tome una herramienta especializada separada para cada tipo de información.

Existen herramientas probadas para indexar y buscar datos de texto no estructurados: Django, Sphinx, Lucene; hay buenos artículos originales sobre este tema en Habré.
La ventaja es que sistema separado y está lo mejor adaptado posible a su tarea.
Pero también existe una desventaja arquitectónica de esta solución: después de todo, las partes estructurales y descriptivas de los datos suelen estar interconectadas y, por lo tanto, Tendrás que construir consultas combinadas..

Veamos un ejemplo

Existe la tarea de registrar a los solicitantes que envían sus currículums a formato de texto. La tarea inicial fue encontrar información sobre los candidatos en función de sus habilidades y experiencia practica dominio de estas habilidades.
nosotros creamos modelo relacional(simplificado):

los reclutadores aceptan currículums, los estudian, completan nuestros formularios, después de lo cual se solicita una solicitud como "dame una lista de candidatos con al menos 2 años de experiencia en C (pero no C++) y no más de 100 años" aproximadamente de la siguiente manera :

SELECCIONE candidato.nombre, habilidad.nombre, candidato_habilidad.experiencia, candidato.teléfono, candidato.correo electrónico DE candidato, candidato_habilidad, habilidad
DONDE (año(candidato.fecha de nacimiento) >
candidato_skill.experiencia > 2 Y candidato_skill.skill_id = habilidad.skill_id Y habilidad.nombre = "C" ORDENAR POR candidato_skill.experiencia DESC;

Encuentra a todos, todos están felices.

Entonces ocurre una sorpresa: un cliente de Tmutarakan necesita un especialista con experiencia en C, pero la experiencia debe ser en Tmutarakan.

Los textos de nuestro currículum están indexados en el sistema Shpinx, por ejemplo, por lo que podemos encontrar rápidamente a todos los candidatos con experiencia laboral en Tmutarakan, pero cuando buscamos en C, surgen dificultades (aparecerán C++, C-Sharp y todo tipo de C). . Un reclutador junior tendrá que seleccionar manualmente miles de candidatos con experiencia laboral en Tmutarakan para encontrar aquellos que tengan 2 años de experiencia allí en C (yo mismo lo he visto más de una vez).

Pero no necesariamente: si inicialmente se eligió sabiamente el DBMS de Postgre, ayudará columna de texto Currículum vitae: texto en la tabla de candidatos: los reclutadores copiaron estúpidamente y pegaron el texto del currículum allí desde el principio, por si acaso.

Necesitamos forzar al sistema a buscar por campo de texto. Qué hacer:

1. Postgre se instala sin diccionarios del idioma ruso, por lo que deberá descargarlos por separado, por ejemplo. Si la base de datos (que es lo más probable) está en UTF-8, también tendrás que convertirla a utf-8, así:
iconv -f koi8-r -t utf-8< ru_RU.aff >afijo.ruso
iconv -f koi8-r -t utf-8< ru_RU.dic >ruso.dict
Copie los archivos resultantes a la subcarpeta tsearch_data de la carpeta donde tiene instalado Postgre.

2. crear un diccionario y una configuración para el idioma ruso:
CREAR DICCIONARIO DE BÚSQUEDA DE TEXTO russian_ispell (Template = ispell, DictFile = ruso, AffFile = ruso, StopWords = ruso);
CREAR CONFIGURACIÓN DE BÚSQUEDA DE TEXTO ru (Copia = ruso);
Para completar
ALTERAR LA CONFIGURACIÓN DE BÚSQUEDA DE TEXTO ru ALTERAR EL MAPEO PARA hword, hword_part, word CON russian_ispell, russian_stem;
ALTERAR LA CONFIGURACIÓN DE BÚSQUEDA DE TEXTO en ALTERAR EL MAPEO PARA asciihword, asciiword, hword_asciipart CON english_ispell, english_stem;

Después de esto, ya puedes obtener el resultado modificando nuestra solicitud.

SELECCIONE Candidato.Nombre, Habilidad.Nombre, Candidato_Habilidad.Experiencia, Candidato.Teléfono, Candidato.Correo electrónico DESDE Candidato, Candidato_Habilidad, Habilidad
DONDE (año(Candidato.DoB) > (año(ahora()) - 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidato_Habilidad.Experiencia >
to_tsvector("ru",Candidato.Resume) @@ to_tsquery("ru",,"Tmutarakan")

3. La operación to_tsvector requiere mucha mano de obra y cada vez que se realiza una solicitud, es irracional convertir el currículum completo para cada línea; crear un índice de tipo GIN en el campo Currículum, convertido al tipo de datos tsvector, resolverá este problema. asunto.
Para hacer esto, primero cree el campo texto completo: tsvector en la tabla Candidato, luego cree un activador que completará este campo según el valor del campo Reanudar al crear o cambiar un registro de Candidato; no saturaremos el código. todo es estándar: asigne texto completo:= to_tsvector(" en el disparador ru",NEW.resume)

Luego creamos un índice en el campo Texto completo:
CREAR ÍNDICE candidato_texto completo EN el candidato USANDO gin (texto completo);

GINEBRA- tipo especial index, para datos tsvector y para arrays (en Postgre 9.3 ya hay 5 diferentes tiposíndices, puede averiguarlo en detalle estudiando el documento de Postgre).

La solicitud entonces toma la forma:
SELECCIONE Candidato.Nombre, Habilidad.Nombre, Candidato_Habilidad.Experiencia, Candidato.Teléfono, Candidato.Correo electrónico DESDE Candidato, Candidato_Habilidad, Habilidad
DONDE (año(Candidato.DoB) > (año(ahora()) - 100)) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience > 2 AND Candidate_Skill.Skill_id = Skill.Skill_id AND Skill.Name = "C" AND
Candidato.texto completo @@ to_tsquery("ru","Tmutarakan")
ORDEN POR Candidato_Skill.Experiencia DESC;

Eso es todo: la consulta arrojará el resultado más preciso posible según el criterio dado.

Pros y contras

La principal ventaja es obvia: la compacidad y precisión de las consultas debido a la combinación de criterios estructurales y de texto completo. Por él, todo el alboroto.

Otra ventaja es que ya no es necesario instalar ni dar soporte a Sphinx.

La búsqueda utilizando el índice GIN ya preparado es muy rápida, consultas complejas Postgre puede "pegarlo" con otros índices (aunque GIN en sí no puede ser compuesto).

Las desventajas también son obvias.
- necesita contener un campo tsvector adicional (no pequeño), necesita un disparador, necesita índice adicional. La pérdida de rendimiento al insertar y actualizar registros será bastante notoria.

De un lado de la balanza tenemos 1) sencillez, precisión y rapidez consultas SQL 2) menos 1 sistema en el paisaje,
por el otro, 1) varios objetos adicionales en el esquema de la base de datos, 2) un rendimiento más lento de las consultas DML.

Si hay dudas de que esto funcionará bien, entonces no debes demoler Sphinx de inmediato. Pero el nuestro lo derribamos hace mucho tiempo, todo funciona bien sin fallos bajo carga.

A menudo, para necesidades auxiliares, es necesario utilizar al menos una búsqueda pobre pero de texto completo en la base de datos. Está claro que los "tipos serios" usan todo tipo de Sphinx y ElasticSearch para esto, pero estas cosas tienen una característica desagradable: deben configurarse, ingresar datos en ellas y, en general, monitorear sus necesidades. Y si sólo tienes cincuenta mil registros en tu base de datos, utilizar estas “máquinas” no es muy conveniente. Especialmente si no quieres ir más allá de Django.

Hay una salida y es bastante sencilla. Cualquier base de datos moderna (Mongo, sí, jajaja) tiene una búsqueda de texto completo incorporada. No muy inteligente, pero listo para usar. Como MySQL no ha funcionado en los últimos años (y gracias a Dios), les contaré Ejemplo de PostgreSQL. Empecemos por la versión. Generalmente para uso postgres últimas versiones no es un “capricho del administrador”, sino una necesidad real. Esto se debe a una actividad bastante activa destinada a mejorar el trabajo e introducir nuevas funciones en el motor. Por eso le aconsejo que realice experimentos y, en general, trabaje con la última versión del DBMS.

Así que comencemos.

Digamos que tenemos algún tipo de modelo Django:

Elemento de clase (modelos.Model): clase Meta: db_table = "productos" padre = modelos.ForeignKey(Section, null=True, en blanco=True) título = models.CharField(max_length=200) descripción = models.TextField(null= Verdadero, en blanco=Verdadero) artículo = modelos.CharField(max_length=200, nulo=Verdadero, en blanco=Verdadero) def __unicode__(self): devuelve "%s/%s" % (self.parent.title, self.title)

Y digamos que nos gustaría asegurarnos de que se realice una búsqueda de texto completo en los campos de título y descripción.

Primero, veamos cómo se hace esto en SQL puro:

SELECCIONE *, ts_rank(to_tsvector("ruso", título||desctripción), to_tsquery("ruso", "champú|anticaspa")) como rango DESDE "Productos" DONDE to_tsvector("ruso", título||desctripción) @@ to_tsquery("ruso", "champú|anti|caspa") ORDENAR POR rango DESC LIMIT 20 OFFSET 0;

Los comandos principales de esta consulta son to_tsvector y to_tsquery. El primer comando traduce las cadenas que están en la base de datos a un dictado de la forma: palabra:peso,...

Visualmente:

La función to_tsquery normaliza las palabras ingresadas y las convierte al tipo tsquery. También existe una función Plainto_tsquery, que toma solo una cadena como entrada y la convierte de la misma manera que to_tsquery, pero sin la necesidad de dividir la frase por adelantado. Cabe señalar que si en el caso de to_tsquery puedes especificar condición lógica, I en este caso OR (|), luego Plainto_tsquery divide la cadena con la condición AND (&).

La consulta también involucra al operador @@, que compara tsvector y tsquery. En general, hay bastantes funciones y operadores para la búsqueda de texto completo, así que fumemos.

Todo esto funcionará de inmediato, incluso con índices no configurados (más sobre ellos a continuación). Pero. Somos vagos, no queremos escribir consultas sin procesar y, en general, lo consideramos un "mal del sistema bajo" (sarcasmo).

El buen tío Jangonaft facilitó la tarea de integrar FTS en Django. Para hacer esto, instale el módulo.

Instalación de pip djorm-ext-pgfulltext

y cambiar ligeramente el código de nuestro modelo:

Desde djorm_pgfulltext.models importe SearchManager desde djorm_pgfulltext.fields importe VectorField desde django.db importe modelos clase Elemento(modelos.Modelo): clase Meta: db_table = "productos" padre = modelos.ForeignKey(GroupSection, null=True, en blanco=True) título = modelos.CharField(max_length=200) descripción = modelos.TextField(null=True, en blanco=True) artículo = models.CharField(max_length=200, null=True, en blanco=True) search_index = VectorField() def __unicode__( self): return "%s/%s" % (self.parent.title, self.title) search_manager = SearchManager(fields=("title", "description"), config="pg_catalog.russian", search_field=" índice_búsqueda", auto_update_search_field=True)

Básicamente, todo el cambio es que agregamos search_index, que es el mismo tsvector para escribir en la base de datos y agregamos nuevo gerente solicitudes al constructor a las que se les pasaron los siguientes parámetros:

campos: una serie de campos a partir de los cuales se construirá el tsvector,
config: le dice a postgresql con qué diccionario queremos trabajar,
campo_búsqueda: el campo en el que tenemos datos que ya están preparados en el vector, recopilados de los campos especificados en los campos,
auto_update_search_field es un indicador que obliga a recrear el campo de búsqueda cuando cambia una entrada.

Si observamos la estructura de la tabla, veremos un campo adicional: search_index, que ya contiene tsvector. Esto se hace para optimización; Postgres puede trabajar con vectores ya preparados y no desperdiciar recursos ejecutando to_tsvector('russian', title||desctription) para cada fila de la base de datos.

Queda por entender cómo realizar realmente esta solicitud de texto completo en nuestro código. No podría ser más sencillo aquí.

Hola a todos,

Describe las capacidades básicas, los pros y los contras de utilizar la búsqueda de texto completo integrada. SGBD PostgreSQL basado en la experiencia de su uso práctico.

Al desarrollar aplicaciones, especialmente aplicaciones web, en el 95% de los casos surge la tarea de elegir sistemas para gestionar tanto contenido estructurado como contenido no estructurado (información textual de estructura arbitraria), así como datos multimedia (fuera del alcance de este artículo).
El arquitecto de la aplicación hace la pregunta: combine estos datos bajo el control de un DBMS o tome una herramienta especializada separada para cada tipo de información.

Existen herramientas probadas para indexar y buscar datos de texto no estructurados: Sphinx, Lucene, hay buenos artículos originales sobre este tema en Habré.
La ventaja es que se trata de un sistema independiente y está lo mejor adaptado posible a su tarea.
Pero también existe una desventaja arquitectónica de esta solución: después de todo, las partes estructurales y descriptivas de los datos suelen estar interconectadas y, por lo tanto, Tendrás que construir consultas combinadas..

Veamos un ejemplo

Existe la tarea de registrar a los solicitantes que envían sus currículums en formato de texto. Inicialmente, la tarea era encontrar información sobre los candidatos en función de sus habilidades y experiencia práctica en el dominio de dichas habilidades.
Creamos un modelo relacional (simplificado):

Los reclutadores aceptan currículums, los estudian, completan nuestros formularios, después de lo cual se solicita una solicitud como "dame una lista de candidatos con al menos 2 años de experiencia en C (pero no en C++) y no más de 100 años" aproximadamente de la siguiente manera:

DONDE (año(candidato.fecha de nacimiento) >
candidato_skill.experiencia > 2 Y candidato_skill.skill_id = habilidad.skill_id Y habilidad.nombre = "C" ORDENAR POR candidato_skill.experiencia DESC;

Encuentra a todos, todos están felices.

Entonces ocurre una sorpresa: un cliente de Tmutarakan necesita un especialista con experiencia en C, pero la experiencia debe ser en Tmutarakan.

Pero no necesariamente: si el DBMS PostgreSQL se eligió sabiamente inicialmente, la columna de texto Currículum: el texto en la tabla Candidato ayudará; los reclutadores copiaron y pegaron estúpidamente el texto del currículum allí desde el principio, por si acaso.

Necesitamos forzar al sistema a buscar por campo de texto. Qué hacer:

1. PostgreSQL se instala sin diccionarios del idioma ruso, por lo que deberá descargarlos por separado, por ejemplo. Si la base de datos (que es lo más probable) está en uft-8, también tendrás que convertir los diccionarios de koi8-r a utf-8, así:
iconv -f koi8-r -t utf-8< ru_RU.aff >afijo.ruso
iconv -f koi8-r -t utf-8< ru_RU.dic >ruso.dict
Copie los archivos resultantes a la subcarpeta tsearch_data de la carpeta donde tiene instalado PostgreSQL.

Después de esto, ya puedes obtener el resultado modificando nuestra solicitud.

3. La operación to_tsvector consume muchos recursos y cada vez que se realiza una solicitud, es irracional convertir el currículum completo para cada línea; crear un índice de tipo GIN en el campo Currículum, convertido al tipo de datos tsvector, resolverá este problema. asunto.
Para hacer esto, primero creamos el campo texto completo: tsvector en la tabla Candidato, luego creamos un activador que llenará este campo según el valor del campo Reanudar al crear o cambiar un registro de Candidato; no saturaremos el código. , todo es estándar: asigne texto completo:= to_tsvector(" en el disparador ru",NEW.resume)

Luego creamos un índice en el campo Texto completo:
CREAR ÍNDICE candidato_texto completo EN el candidato USANDO gin (texto completo);

GIN es un tipo especial de índice para datos del tipo tsvector y para matrices (en PostgreSQL 9.3 ya existen 5 tipos diferentes de índices, puedes aprender más estudiando el documento de PostgreSQL).

Eso es todo: la consulta arrojará el resultado más preciso posible según el criterio dado.

Pros y contras

La principal ventaja es obvia: la compacidad y precisión de las consultas debido a la combinación de criterios estructurales y de texto completo. Por él, todo el alboroto.

Otra ventaja es que ya no es necesario instalar ni dar soporte a Sphinx.

La búsqueda de un índice GIN ya preparado es muy rápida; en consultas complejas, PostgreSQL puede "pegarlo" con otros índices (aunque el GIN en sí no puede ser compuesto).

Las desventajas también son obvias.
— necesita contener un campo tsvector adicional (no pequeño), necesita un disparador, necesita un índice adicional. La pérdida de rendimiento al insertar y actualizar registros será bastante notoria.
— carga adicional en el servidor PostgreSQL
— Postgre no posiciona la búsqueda de texto completo como su característica principal; después de todo, fue, es y (hasta donde podemos ver) será un buen RDBMS+, por lo que si su aplicación necesita capacidades de indexación y búsqueda de texto completo del A la vanguardia de la ciencia, todavía prestamos atención a productos especializados.

En un lado de la escala tenemos 1) simplicidad, precisión y velocidad de las consultas SQL 2) menos 1 sistema en el panorama,
por el otro, 1) varios objetos adicionales en el esquema de la base de datos, 2) un rendimiento más lento de las consultas DML 3) un motor no especializado.

Si hay dudas de que esto funcionará bien, entonces no debes demoler Sphinx de inmediato. Pero el nuestro lo derribamos hace mucho tiempo, todo funciona bien sin fallos bajo carga.
El resultado estará perfectamente justificado si las solicitudes de búsquedas de texto completo y combinadas no representan más del 30% del número total de solicitudes. Estudie detenidamente las capacidades de búsqueda de texto completo de PostgreSQL; si potencialmente las funciones descritas en la documentación no son suficientes para usted, entonces, por supuesto, debería considerar otros productos, porque, vuelvo a señalar, PostgreSQL no es un sistema especializado para texto completo. indexación y búsqueda y pueden tener capacidades inferiores a productos más especializados.

Popular en la categoría: