Обычный LIKE и оператор ILIKE годятся, чтобы найти подстроку, но они не понимают язык. Запрос ILIKE '%самолет%' не найдет строку со словом самолеты, а уж тем более со словом самолетов или самолетным, потому что для базы это просто разные наборы байт. Полнотекстовый поиск (full text search) в postgresql решает другую задачу: найти документы по смыслу слов, с учетом морфологии, без оглядки на падежи и окончания. В этом уроке разберем, как PostgreSQL превращает текст в нормализованные лексемы, как строится поисковый запрос, как ранжировать результаты по релевантности, как ускорить все это GIN-индексом и генерируемым столбцом, и чем добить опечатки через нечеткий поиск pg_trgm. Поиск по-русски в фокусе.
Как это работает
В основе лежат два типа данных. Первый - tsvector: это документ, разобранный на лексемы (нормальные формы слов) с позициями. Второй - tsquery: это сам поисковый запрос, тоже в виде лексем, но связанных логическими операторами И, ИЛИ, НЕ. Поиск - это проверка оператором @@, попадает ли tsquery в tsvector.
Чтобы превратить сырой текст в tsvector, PostgreSQL прогоняет его через конфигурацию поиска. Конфигурация определяет, какой парсер режет текст на токены и какие словари приводят токены к нормальной форме. Для русского языка нужна конфигурация russian: она знает стоп-слова (и, в, на - их выкидывают, они не несут смысла) и подключает словарь снежного стеммера, который отрезает окончания. Поэтому самолет, самолеты, самолетам сворачиваются в одну лексему самолет, и все три формы становятся взаимозаменяемыми при поиске.
Функция to_tsvector(config, text) делает документ, а для запроса есть три входа. Функция to_tsquery принимает уже размеченный синтаксис с операторами и амперсандами, она строгая и кидает ошибку на кривой ввод. Функция plainto_tsquery берет простую фразу от пользователя и сама склеивает слова через И. Функция phraseto_tsquery дополнительно сохраняет порядок слов (оператор расстояния), то есть ищет фразу как последовательность. С PostgreSQL 11 есть еще websearch_to_tsquery - она понимает кавычки для фраз и минус для исключения, как в поисковой строке, и не падает на любом мусорном вводе, поэтому для пользовательского поля ввода берите именно ее.
Найденное надо отсортировать. Функция ts_rank считает релевантность по частоте и положению лексем, а ts_rank_cd учитывает плотность совпадений (cover density) - насколько кучно искомые слова стоят рядом. Это не абсолютная истина, а эвристика, но для сортировки выдачи ее обычно хватает.
SQL и примеры
Сначала посмотрим, что делает конфигурация russian с обычной фразой. Видно, как слова нормализуются, а предлог выкидывается.
Код: Выделить всё
SELECT to_tsvector('russian', 'Рейсы из Москвы во Владивосток');
-- 'владивосток':5 'москв':3 'рейс':1
-- предлоги из/во отброшены, Москвы -> москв, Рейсы -> рейс
Код: Выделить всё
SELECT airport_code, airport_name, city
FROM bookings.airports
WHERE to_tsvector('russian', airport_name || ' ' || city)
@@ plainto_tsquery('russian', 'морской');
Код: Выделить всё
SELECT ticket_no, passenger_name,
ts_rank(to_tsvector('simple', passenger_name),
plainto_tsquery('simple', 'IVAN')) AS rank
FROM bookings.tickets
WHERE to_tsvector('simple', passenger_name)
@@ plainto_tsquery('simple', 'IVAN')
ORDER BY rank DESC
LIMIT 10;
-- для латинских имен берем конфигурацию simple: без стемминга, просто регистр и токены
Код: Выделить всё
ALTER TABLE bookings.aircrafts_data
ADD COLUMN model_tsv tsvector
GENERATED ALWAYS AS
(to_tsvector('russian', model ->> 'ru')) STORED;
CREATE INDEX aircrafts_tsv_gin ON bookings.aircrafts_data
USING gin (model_tsv);
SELECT aircraft_code, model ->> 'ru' AS model
FROM bookings.aircrafts_data
WHERE model_tsv @@ to_tsquery('russian', 'боинг | аэробус');
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT city, similarity(city, 'Влодивасток') AS sim
FROM bookings.airports
WHERE city % 'Влодивасток'
ORDER BY sim DESC
LIMIT 5;
-- найдет Владивосток несмотря на две опечатки
Код: Выделить всё
CREATE INDEX airports_city_trgm
ON bookings.airports USING gin (city gin_trgm_ops);
- Поиск ничего не находит, потому что конфигурация по умолчанию english, а текст русский. Всегда указывайте 'russian' явно в to_tsvector и to_tsquery, либо задайте default_text_search_config.
- to_tsquery падает с ошибкой syntax error на пользовательском вводе вроде пробела или скобки. Для сырого ввода используйте plainto_tsquery или websearch_to_tsquery, они не ломаются.
- Конфигурация документа и запроса разные. Если документ собран как 'russian', а запрос как 'simple', лексемы не совпадут и поиск промахнется. Конфигурация должна быть одна и та же с обеих сторон.
- Индекс GIN построен по одному выражению, а в WHERE другое (другой порядок конкатенации, другой cast). Планировщик не сможет применить индекс - выражение должно совпадать байт в байт. Генерируемый столбец снимает эту проблему.
- Забыли, что GIN-индекс не помогает ts_rank сортировать. Индекс ускоряет отбор по @@, но ранжирование все равно считается по отобранным строкам, поэтому LIMIT и хороший фильтр важны.
- pg_trgm и оператор % зависят от порога pg_trgm.similarity_threshold (по умолчанию 0.3). Слишком высокий порог теряет совпадения с опечатками, слишком низкий тащит мусор.
- Триграммный индекс gin_trgm_ops большой и медленнее на запись. На горячих таблицах с частыми вставками взвешивайте стоимость.
- Создайте таблицу docs(id serial primary key, body text) и вставьте 5-6 строк с русскими предложениями, где встречаются разные формы одного слова (рейс, рейсы, рейсов).
- Выполните SELECT to_tsvector('russian', body) FROM docs и убедитесь, что формы свернулись в одну лексему.
- Найдите строки через WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'рейс') - проверьте, что нашлись все формы.
- Добавьте генерируемый столбец body_tsv типа tsvector через GENERATED ALWAYS AS ... STORED и навесьте GIN-индекс.
- Сравните планы EXPLAIN ANALYZE для поиска по выражению и по индексированному столбцу.
- Установите pg_trgm, добавьте строку с опечаткой и найдите ее через оператор % и similarity.
- Поиграйте с set pg_trgm.similarity_threshold и посмотрите, как меняется набор результатов.
- Чем tsvector отличается от tsquery и что делает оператор @@?
- Зачем нужна конфигурация russian и что произойдет при поиске по русскому тексту с конфигурацией english?
- В каких случаях вы возьмете plainto_tsquery, а в каких to_tsquery или websearch_to_tsquery?
- Почему генерируемый tsvector-столбец с GIN-индексом быстрее, чем to_tsvector в WHERE на каждой строке?
- Что считают ts_rank и ts_rank_cd и почему результат поиска все равно нужно сортировать отдельно?
- Когда полнотекстовый поиск бессилен и чем тут помогает pg_trgm?
