PostgreSQL: Полнотекстовый поиск - как искать частичные слова?

Следуя заданному вопросу о том, как я могу увеличить скорость в одном из методов поиска SQL, мне было рекомендовано обновить мою таблицу, чтобы использовать полнотекстовый поиск. Это то, что я сейчас сделал, используя индексы Gist, чтобы ускорить поиск. По некоторым "простым" запросам я заметил заметное увеличение, которым я очень доволен.

Однако мне трудно найти частичные слова. Например, у меня есть несколько записей, содержащих слово Squire (454), и у меня есть несколько записей, содержащих Squirrel (173). Теперь, если я ищу Squire, он возвращает только 454 записи, но я также хочу, чтобы он также возвращал записи Squirrel.

Мой запрос выглядит следующим образом

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

Я думал, что могу сделать to_tsquery('squire%'), но это не работает.
Как получить его для поиска частичных совпадений?

Кроме того, в моей базе данных у меня есть записи, которые являются фильмами и другими, которые являются просто телешоу. Они дифференцируются по названию, так что "Munsters" - это телешоу, в то время как The Munsters - это фильм шоу. То, что я хочу сделать, это поиск только телешоу и только фильмов. Любая идея о том, как я могу это достичь?

Отношения Anthoni

Ответ 1

Даже используя LIKE, вы не сможете получить "белку" от squire%, потому что "белка" имеет два "r". Чтобы получить Squire и Squirrel, вы можете запустить следующий запрос:

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

Чтобы различать фильмы и телевизионные шоу, вы должны добавить столбец в свою базу данных. Тем не менее, есть много способов обмануть этого кота. Вы можете использовать подзапрос, чтобы заставить postgres сначала найти фильмы, соответствующие "squire" и "squirrel", а затем выполнить поиск этого подмножества, чтобы найти заголовки, начинающиеся с "". Можно создать индексы для использования в LIKE '"%...' поиска.

Без изучения других возможностей индексирования вы также можете запустить их - беспорядок вокруг них, чтобы найти наиболее быстрый:

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

или

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

Ответ 2

Попробуйте,

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

Это работает на PostgreSQL 8.4+

Ответ 3

Anthoni,

Предполагая, что вы планируете использовать только кодировку ASCII (может быть, это сложно, я знаю), очень жизнеспособным вариантом может быть модуль Trigram (pg_trgm): http://www.postgresql.org/docs/9.0/interactive/pgtrgm.html

Trigram использует встроенные методы индексирования, такие как Gist и Gin. Единственное, что вам нужно сделать, это определить индекс, указать класс оператора либо gist_trgm_ops, либо gin_trgm_ops.

Если модули Contrib еще не установлены, в Ubuntu это легко и работает с командной оболочкой:

# sudo apt-get install postgresql-contrib

После того, как модули Contrib были доступны, вы должны установить расширение pg_trgm в соответствующую базу данных. Вы делаете это, выполнив следующий запрос PostgreSQL в базе данных, в которую вы хотите установить модуль:

CREATE EXTENSION pg_trgm;

После того, как расширение pg_trgm было установлено, мы готовы повеселиться!

-- 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

Ответ 4

@Решение alexander-mera отлично работает!

Примечание. Также необходимо преобразовать пробелы в +. Например, если вы ищете squire knight.

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

Ответ 5

Одна вещь, которая может работать, - сломать слово, которое вы ищете, на более мелкие части. Таким образом, вы можете искать вещи, в которых есть squi, quir или squire и т.д.... Я не уверен, насколько это было бы эффективно, но это может помочь.

При поиске фильма или фильма вы можете попробовать разместить текст в одиночной кавычки. так что это будет либо "шоу", либо "шоу". Я думаю, что это тоже может работать.

Ответ 6

Широкое решение этого - использовать функцию PG ts_rewrite для настройки таблицы псевдонимов, которая работает для альтернативных совпадений (см. Query Rewriting). Это касается таких случаев, как ваше, а также обработки совершенно разных случаев, таких как поиск tree rat и получение результатов для squirrel и т.д.

Полные детали и пояснения по этой ссылке, но суть в том, что вы можете настроить таблицу псевдонимов с 2 столбцами ts_query и передать запрос этой таблицы в свой поиск, например:

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');

Результат в конечном запросе, который больше похож:

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

Это похоже на установку тезауруса в PG, но работает, не требуя полного reindex каждый раз, когда вы что-то добавляете. Когда вы сталкиваетесь с небольшими вариантами написания и случаями "когда я ищу это, я ожидаю таких результатов", очень легко просто добавить их в таблицу очень быстро. Вы можете добавить больше столбцов в эту таблицу, а также запрос, основанный на ts_rewrite, возвращает 2 ожидаемых столбца to_tsquery.

Когда вы вникнете в эту документацию, вы также увидите примеры для настройки производительности. Там баланс между использованием триграмм для чистой скорости и использования vector/query/rewrite для надежности.