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 для надежности.