varios - PostgreSQL: Búsqueda de texto completo-¿Cómo buscar palabras parciales?

porque no está indizado por texto completo.

Question

Siguiendo una pregunta publicada aquí sobre cómo puedo aumentar la velocidad en uno de mis métodos de búsqueda SQL, se me recomendó actualizar mi tabla para hacer uso de la búsqueda de texto completo. Esto es lo que he hecho ahora, usando los índices Gist para hacer que la búsqueda sea más rápida. En algunas de las consultas "simples" he notado un aumento marcado que me alegra mucho.

Sin embargo, estoy teniendo dificultades para buscar palabras parciales. Por ejemplo, tengo varios registros que contienen la palabra Squire (454) y varios registros que contienen Squirrel (173). Ahora, si busco Squire, solo devuelve los 454 registros, pero también quiero que devuelva los registros de Squirrel.

Mi consulta se ve así

SELECT title 
FROM movies 
WHERE vectors @@ to_tsoquery(''squire'');

Pensé que podría hacer to_tsquery(''squire%'') pero eso no funciona.
¿Cómo lo consigo para buscar coincidencias parciales?

Además, en mi base de datos tengo registros que son películas y otros que son solo programas de televisión. Estos se diferencian por el "" sobre el nombre, así como "Munsters" es un programa de televisión, mientras que The Munsters es la película del programa. Lo que quiero hacer es buscar solo el programa de TV Y solo las películas. ¿Alguna idea de cómo puedo lograr esto?

Saludos Anthoni

Answer 1

¡La solución de alexander-mera funciona muy bien!

Nota : También asegúrese de convertir los espacios a + . Por ejemplo, si estás buscando el squire knight .

SELECT title FROM movies WHERE to_tsvector(title) @@ to_tsquery(''squire+knight:*'')

Answer 2

Anthoni,

Suponiendo que planea utilizar solo la codificación ASCII (podría ser difícil, estoy consciente), una opción muy viable puede ser el módulo Trigram (pg_trgm): http://www.postgresql.org/docs/9.0/interactive/pgtrgm.html

Trigram utiliza métodos de indexación incorporados como Gist y Gin. La única modificación que debe realizar es al definir su índice, especifique una Clase de Operador de gist_trgm_ops o gin_trgm_ops .

Si los módulos contrib no están instalados, en Ubuntu es tan fácil como ejecutar el siguiente comando desde el shell:

# sudo apt-get install postgresql-contrib

Una vez que los módulos contrib estén disponibles, debe instalar la extensión pg_trgm en la base de datos en cuestión. Para ello, ejecute la siguiente consulta de PostgreSQL en la base de datos en la que desea instalar el módulo:

CREATE EXTENSION pg_trgm;

Después de que se haya instalado la extensión pg_trgm, ¡estamos listos para divertirnos un poco!

-- Create a test table.
CREATE TABLE test (my_column text)
-- Create a Trigram index.
CREATE INDEX test_my_colun_trgm_idx ON test USING gist (my_column gist_trgm_ops);
-- Add a couple records
INSERT INTO test (my_Column) VALUES (''First Entry''), (''Second Entry''), (''Third Entry'')
-- Query using our new index --
SELECT my_column, similarity(my_column, ''Frist Entry'') AS similarity FROM test WHERE my_column % ''Frist Entry'' ORDER BY similarity DESC

Answer 3

Incluso usando LIKE no podrás obtener ''squirrel'' de squire% porque ''squirrel'' tiene dos ''r''. Para obtener Squire y Squirrel puedes ejecutar la siguiente consulta:

SELECT title FROM movies WHERE vectors @@ to_tsquery(''squire|squirrel'');

Para diferenciar entre películas y programas de televisión, debe agregar una columna a su base de datos. Sin embargo, hay muchas maneras de pelar a este gato. Podría usar una subconsulta para forzar a los postgres a buscar primero las películas que coincidan con ''squire'' y ''squirrel'' y luego buscar en ese subconjunto para encontrar títulos que comiencen con una "". Es posible crear índices para usar en LIKE ''"%...'' búsquedas.

Sin explorar otras posibilidades de indexación, también puede ejecutarlas: juegue con ellas para encontrar cuál es la más rápida:

SELECT title 
FROM (
   SELECT * 
   FROM movies 
   WHERE vectors @@ to_tsquery(''squire|squirrel'')
) t
WHERE title ILIKE ''"%'';

o

SELECT title 
FROM movies 
WHERE vectors @@ to_tsquery(''squire|squirrel'') 
  AND title ILIKE ''"%'';

Answer 4

La solución general para esto es usar la función ts_rewrite de PG para configurar una tabla de alias que funcione para coincidencias alternativas (consulte Reescritura de consultas ). Esto cubre casos como el tuyo de arriba, mientras que también maneja casos completamente diferentes como buscar tree rat y obtener resultados para squirrel , etc.

Todos los detalles y la explicación en ese enlace, pero lo esencial es que puede configurar una tabla de alias con 2 columnas ts_query y pasar una consulta de esa tabla a su búsqueda, así:

CREATE TABLE aliases (t tsquery primary key, s tsquery);
INSERT INTO aliases VALUES(to_tsquery(''supernovae''), to_tsquery(''supernovae|sn''));

SELECT ts_rewrite(to_tsquery(''supernovae & crab''), ''SELECT * FROM aliases'');

Resultando en una consulta final que se parece más a:

WHERE vectors @@ ts_rewrite(to_tsquery(''supernovae & crab''), ''SELECT * FROM aliases'')

Esto es similar a la configuración del tesauro en PG pero funciona sin requerir un reindexado completo cada vez que agregue algo. A medida que encuentra pequeñas variaciones ortográficas y los casos de "cuando busco esto, espero resultados como este", es muy fácil simplemente agregarlos a la tabla rápidamente. Puede agregar más columnas a esa tabla, siempre y cuando la consulta basada en ts_rewrite devuelva las 2 columnas de to_tsquery esperadas.

Cuando profundice en esa documentación, también verá ejemplos sugeridos para la optimización del rendimiento. Hay un equilibrio entre el uso de trigram para la velocidad pura y el uso de vector / query / rewrite para la solidez.

Answer 5

Tratar,

SELECT title FROM movies WHERE to_tsvector(title) @@ to_tsquery(''squire:*'')

Esto funciona en PostgreSQL 8.4+

Answer 6

Una cosa que puede funcionar es dividir la palabra que está buscando en partes más pequeñas. Así que puedes buscar cosas que tengan squi o quir o escudero o etc ... No estoy seguro de qué tan eficiente sería, pero podría ayudar.

Cuando busca la película o la película, puede intentar colocar el texto en la comilla simple. por lo que sería ''show'' o ''"show"''. Creo que eso también podría funcionar.