Как совместить текстовый поиск PostgreSQL с fuzzystrmatch
Я хотел бы иметь возможность запрашивать слова из столбца типа ts_vector, но все, что имеет расстояние Левенштейна ниже X, должно считаться совпадением.
Что-то вроде этого, где my_table:
id | my_ts_vector_colum | sentence_as_text
------------------------------------------------------
1 | 'bananna':3 'tasty':2 'very':1 | Very tasty bananna
2 | 'banaana':2 'yellow':1 | Yellow banaana
3 | 'banana':2 'usual':1 | Usual banana
4 | 'baaaanaaaanaaa':2 'black':1 | Black baaaanaaaanaaa
Я хочу запросить что-то вроде «Дайте мне идентификаторы всех строк, которые содержат слово банан или слова, похожие на банан, где подобное означает, что его расстояние Левенштейна меньше 4». Таким образом, результат должен быть 1, 2 и 3.
Я знаю, что могу сделать что-то вроде select id from my_table where my_ts_vector_column @@ to_tsquery('banana');, но это даст мне только точные совпадения.
Я также знаю, что могу сделать что-то вроде select id from my_table where levenshtein(sentence_as_text, 'banana') < 4;, но это будет работать только в текстовом столбце и будет работать, только если совпадение будет содержать только слово банан.
Но я не знаю, смогу ли я их совместить и как.
P.S. Таблица, в которой я хочу выполнить это, содержит около 2 миллионов записей, и запрос должен быть быстрым (менее 100 мс точно).
P.P.S. У меня есть полный контроль над схемой таблицы, поэтому изменение типов данных, создание новых столбцов и т. д. вполне возможно.
«Таблица, в которой я хочу выполнить это, содержит около 2 миллионов записей, и запрос должен быть молниеносным» Функция расстояния Левенштейна не индексируется, поэтому это кажется почти безнадежной задачей.
Ответы 1
2 миллиона коротких предложений, по-видимому, содержат гораздо меньше отдельных слов, чем это. Но если все ваши предложения имеют «творческое» написание, возможно, нет.
Таким образом, вы, возможно, можете создать таблицу отдельных слов для относительно быстрого поиска с помощью функции неиндексированного расстояния:
create materialized view words as
select distinct unnest(string_to_array(lower(sentence_as_text),' ')) word from my_table;
И создайте точный индекс в большую таблицу:
create index on my_table using gin (string_to_array(lower(sentence_as_text),' '));
А затем присоединяйтесь к вместе:
select * from my_table join words
ON (ARRAY[word] <@ string_to_array(lower(sentence_as_text),' '))
WHERE levenshtein(word,'banana')<4;
Хотя эта стратегия не работает для меня в моей конкретной проблеме, я все же приму ее, поскольку это правильный ответ на исходный вопрос. Спасибо. К настоящему времени я понял, что для решения моей проблемы Postgres, вероятно, не является инструментом, и мне следует больше смотреть на Elasticsearch.
Вы не можете сделать это. Лучший вариант — использовать расширение
pg_trgmи его операторы подобия (и индекс GIN).