Нечеткий поиск по тексту в PostgreSQL с помощью pg_trgm
5 июня 2017
В продолжение темы о полнотекстовом поиске в PostgreSQL хотелось бы также рассказать о расширении под названием pg_trgm. Данное расширение предназначено для поиска текстовых документов по триграммам, то есть, всем подпоследовательностям из трех букв, входящих в индексируемый текст. На практике такой поиск интересен, помимо прочего, тем, что позволяет находить документы по запросам, содержащим опечатки.
Итак, пример создания индекса:
CREATE EXTENSION pg_trgm;
-- также можно использовать gist
CREATE INDEX articles_trgm_idx ON articles
USING gin (title gin_trgm_ops);
С помощью процедуры show_trgm можно посмотреть получившиеся триграммы:
Пример ответа:
show_trgm | {" a"," ac",acc,ble,cce,ces,com,eco,ess,ibl,ing,lec,mp...
-[ RECORD 2 ]---------------------------------------------------------
show_trgm | {" a"," an",ana,arc,chi,his,ism,nar,rch,"sm "}
-[ RECORD 3 ]---------------------------------------------------------
show_trgm | {" a"," af",afg,anh,ani,fgh,gha,han,his,ist,nhi,nis,or...
Поиск с использованием построенного индекса осуществляется так:
WHERE title % 'Straustrup';
Результат:
title | Bjarne Stroustrup
similarity | 0.35
Как видите, документ был найден, невзирая на опечатку в поисковом запросе. Точно так же, к примеру, по запросу «phone» могут быть найдены документы, содержащие слово «iPhone», что при использовании обычного полнотекстового поиска не будет работать.
При поиске с использованием pg_trgm возвращаются документы, чей уровень similarity запросу выше определенного значения. По умолчанию это значение равно 0.3. Узнать текущее пороговое значение можно при помощи процедуры show_limit, а изменить его в рамках сессии — с помощью процедуры set_limit:
Результат:
------------+-----------
0.3 | 0.4
Помимо возможности поиска по запросам с опечатками pg_trgm также может быть использован для ускорения LIKE/ILIKE-запросов, а также поиска по регулярным выражениям:
-- или:
EXPLAIN SELECT title FROM articles WHERE title ~* 'Stroustrup';
Пример плана запроса:
Recheck Cond: ((title)::text ~~ '%Stroustrup%'::text)
-> Bitmap Index Scan on articles_trgm_idx (cost=0.00..60.02 rows...
Index Cond: ((title)::text ~~ '%Stroustrup%'::text)
Такое вот полезное расширение.
Дополнение: Практический пример использования pgvector
Метки: PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.