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

PostgreSQL умеет искать по тексту умнее, чем LIKE '%слово%'. Полнотекстовый поиск понимает формы слов, убирает стоп-слова и умеет ранжировать результаты по релевантности. Всё это — без внешних систем вроде Elasticsearch.

Основы: tsvector и tsquery

tsvector — нормализованное представление документа: список лексем (корней слов) с позициями.

tsquery — поисковый запрос: лексемы с логическими операторами.

-- Преобразуем текст в вектор
SELECT to_tsvector('russian', 'Пользователи создают заказы в магазине');
-- 'магазин':5 'создава':3 'заказ':4 'пользовател':1

-- Поисковый запрос
SELECT to_tsquery('russian', 'заказ & пользователь');
-- 'заказ' & 'пользовател'

-- Проверяем совпадение
SELECT to_tsvector('russian', 'Пользователи создают заказы в магазине')
    @@ to_tsquery('russian', 'заказ & пользователь');
-- true

Оператор @@ возвращает true если вектор содержит все лексемы запроса. PostgreSQL сам нормализует «заказы» → «заказ» и «пользователи» → «пользовател» — это и есть сила полнотекстового поиска.

Конфигурации языка

PostgreSQL поставляется с конфигурациями для множества языков. Для русского:

-- Посмотреть доступные конфигурации
SELECT cfgname FROM pg_ts_config;

-- Конфигурация russian учитывает русские стоп-слова (и, в, на, с...)
-- и использует русские правила стемминга
SELECT to_tsvector('russian', 'и в на по с со за до');
-- '' (пустой вектор — все слова являются стоп-словами)

SELECT to_tsvector('russian', 'Быстрая коричневая лиса');
-- 'быстр':1 'коричнев':2 'лис':3

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

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT
);

INSERT INTO articles (title, body) VALUES
('Введение в PostgreSQL', 'PostgreSQL — мощная реляционная база данных с открытым кодом'),
('Оптимизация запросов', 'Индексы и EXPLAIN помогают ускорить медленные запросы'),
('Полнотекстовый поиск', 'tsvector и tsquery позволяют искать по тексту эффективно');

-- Простой поиск
SELECT title
FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'запрос & индекс');
-- "Оптимизация запросов"

Варианты tsquery

-- AND: оба слова должны присутствовать
to_tsquery('russian', 'индекс & запрос')

-- OR: хотя бы одно
to_tsquery('russian', 'индекс | производительность')

-- NOT: слово должно отсутствовать
to_tsquery('russian', 'запрос & !медленный')

-- Фраза (слова рядом): оператор <->
to_tsquery('russian', 'полнотекстовый <-> поиск')

-- plainto_tsquery — принимает обычный текст, строит AND-запрос
plainto_tsquery('russian', 'быстрый поиск по тексту')
-- 'быстр' & 'поиск' & 'текст'

-- phraseto_tsquery — строит фразовый запрос
phraseto_tsquery('russian', 'полнотекстовый поиск')
-- 'полнотекстов' <-> 'поиск'

-- websearch_to_tsquery — синтаксис как в Google
websearch_to_tsquery('russian', 'индекс -медленный "полнотекстовый поиск"')

plainto_tsquery и websearch_to_tsquery удобны для пользовательского ввода — они не бросают ошибку при некорректном синтаксисе.

GIN-индекс для производительности

Без индекса каждый запрос пересчитывает to_tsvector для каждой строки. На большой таблице это катастрофа.

-- Вариант 1: индекс по выражению (тратит время на пересчёт при вставке)
CREATE INDEX idx_articles_search
ON articles USING GIN (to_tsvector('russian', body));

-- Запрос должен точно совпадать с выражением в индексе
SELECT title FROM articles
WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'поиск');

Лучший подход — хранить tsvector в отдельной колонке и обновлять через триггер:

-- Вариант 2: сохранённая колонка (быстрее при поиске)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Заполнить сразу по нескольким полям с разными весами
UPDATE articles SET search_vector =
    setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(body, '')), 'B');

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- Триггер для автообновления
CREATE OR REPLACE FUNCTION articles_search_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('russian', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_trigger
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_update();

Веса A, B, C, D влияют на ранжирование — слова из заголовка (A) считаются важнее слов из тела (B).

Ранжирование: ts_rank

SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM articles,
     plainto_tsquery('russian', 'полнотекстовый поиск') query
WHERE search_vector @@ query
ORDER BY rank DESC;

title                    | rank
-------------------------|--------
Полнотекстовый поиск     | 0.0985
Оптимизация запросов     | 0.0303

ts_rank_cd — альтернатива, учитывает плотность и близость слов:

SELECT title, ts_rank_cd(search_vector, query) AS rank
FROM articles, plainto_tsquery('russian', 'запрос') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Подсветка найденных слов: ts_headline

SELECT
    title,
    ts_headline(
        'russian',
        body,
        plainto_tsquery('russian', 'запрос индекс'),
        'MaxWords=15, MinWords=5, StartSel=<b>, StopSel=</b>'
    ) AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('russian', 'запрос индекс');

title                  | snippet
-----------------------|----------------------------------------------
Оптимизация запросов   | Индексы и EXPLAIN помогают ускорить медленные <b>запросы</b>

Итог

Полнотекстовый поиск PostgreSQL подходит для большинства задач без установки отдельных поисковых движков. Отлично масштабируется до миллионов документов при правильных GIN-индексах.

Отработай полнотекстовый поиск на практике в нашем тренажёре.