В продолжение темы о полнотекстовом поиске в 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
