Полнотекстовый поиск в PostgreSQL

1. Типы данных

Для полнотекстового поиска в PostgreSQL предусмотрены специальные типы данных: tsvector и tsquery.

Тип tsvector — представляет документ в виде оптимизированном для текстового поиска. По сути это нормализованная строка по которой будет производиться поиск.

Под нормализацией следует понимать такие процедуры как выкидывание стоп-слов, вырезание окончаний слов, приведение к нижнему регистру и т.д.

Для нормализации строки используется процедура to_tsvector.

Например так будет выглядеть нормализованная строка:

SELECT to_tsvector('I find the system really useful'); 
'find':2 'realli':5 'system':4 'use':6

Тип tsquery – используется для представления запросов поиска. Для преобразования поисковых запросов используется процедура plainto_tsquery:

SELECT plainto_tsquery('help us to save Jupiter'); 
'help' & 'us' & 'save' & 'jupit'

2. Поиск в таблице

Поиск должен найти документы tsvector соответствующие запросу tsquery.
Для сопоставления используется оператор @@.

Для таблицы news следующего формата:

id | title | content
.. | ... | .....
34 | Article title | Content text.

поисковый запрос по колонкам title и content будет таким:

SELECT * FROM news WHERE to_tsvector(title) || to_tsvector(content) 
@@ plainto_tsquery('user search text');

Оператор || используется для конкатенации tsvector.

3. Ранжирование результатов поиска

Оценка релевантности документа относительно запроса происходит с учетом весов элементов tsvector.

Для установки весов предусмотрена функция setweight, значения задаются буквами A B C D.

Например установим важность слов в заголовке больше чем в теле документа. Тогда наш запрос будет выглядеть так:

SELECT * FROM news WHERE setweight(to_tsvector(title), 'A') || setweight(to_tsvector(content), 'B') @@ plainto_tsquery('user search text') ORDER BY ts_rank(setweight(to_tsvector(title), 'A') || setweight(to_tsvector(content), 'B'), plainto_tsquery('user search text')) DESC;

Функция ts_rank ранжирует результаты по частоте найденных лексем.

4. Создание индексов

Для полнотекстового поиска более предпочтительным является индекс GIN (Generalized Inverted Index).

Он содержит записи всех ключей (лексем) со списком мест их вхождений.

Индекс GIN для поиска ключей использует бинарное дерево, поэтому он слабо зависит от количества ключей и хорошо масштабируется.

Создаем индекс:

CREATE INDEX search_index news USING GIN(setweight(to_tsvector('title'), 'A')
|| setweight(to_tsvector(content), 'B'));

Но будьте осторожны и не используйте индекс GIN для документов которые постоянно изменяются. Так как изменения приводят к большому количеству обновлений индекса.

TL;DR

Полнотекстовый поиск в PostgreSQL достаточно гибкий инструмент. Он имеет много конфигураций, что позволяет настроить поиск “под себя”.

Перед тем как использовать технологию убедитесь, что она вам подходит и вы используете ее по назначению.