Похожий текст в PostgreSQL: триграммы и расстояние

Если поиск по каталогу возвращает пустой результат из-за одной опечатки, в PostgreSQL можно искать строки по сходству триграмм. Для массового отбора кандидатов подойдёт GIN с пороговым оператором, для выдачи ближайших совпадений — GiST с сортировкой по расстоянию. Ниже — оба варианта на синтаксисе PostgreSQL 16 и критерии выбора.

Что именно сравнивает pg_trgm

Расширение pg_trgm разбивает слова на триграммы — последовательности из трёх символов. Например, слово анализируется не только целиком: учитываются соседние группы символов, поэтому небольшая опечатка часто меняет лишь часть набора. Мера сходства лежит между 0 и 1: единица означает полное совпадение триграмм, ноль — отсутствие общих триграмм.

Термин — триграмма. Это группа из трёх последовательных символов, используемая как единица сравнения. Для поиска это не то же самое, что сравнение словаря или вычисление числа вставок, удалений и замен символов.

Важно различать похожесть и расстояние. Оператор <-> возвращает триграммное расстояние, равное единице минус сходство: меньшее значение — ближе строки. Это не расстояние Левенштейна. Поэтому результат может отличаться от того, который даст алгоритм подсчёта редакторских правок.

GIN отвечает на вопрос «какие строки проходят порог сходства?», GiST — «какие строки ближе всего к запросу?» Выбирать индекс стоит по форме запроса, а не по привычке.

Подготовка: расширение и индекс

Примеры ниже рассчитаны на PostgreSQL 16. Расширение нужно включить в базе, где выполняется поиск. Создайте таблицу и индекс на поле, которое будете сравнивать:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE catalog_item (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE INDEX catalog_item_name_trgm_gin
    ON catalog_item USING gin (name gin_trgm_ops);

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

Вариант 1: порог сходства с GIN

Оператор % проверяет, превышает ли сходство порог pg_trgm.similarity_threshold. В PostgreSQL 16 значение по умолчанию — 0,3. Для конкретного запроса его можно изменить в текущем сеансе:

SET pg_trgm.similarity_threshold = 0.35;

SELECT id,
       name,
       similarity(name, 'беспроводные наушники') AS score
FROM catalog_item
WHERE name % 'беспроводные наушники'
ORDER BY score DESC, id
LIMIT 20;

Чем выше порог, тем строже отбор и тем меньше результатов. Слишком низкий порог даст больше слабых совпадений; слишком высокий может отсеять нужную строку с опечаткой. Значение 0,35 в примере — стартовая настройка для проверки, а не универсальная рекомендация.

Функция similarity() полезна для показа оценки в выдаче, но условие с % важно для использования GIN-индекса. Если просто отсортировать всю таблицу по вычисленной функции, база может просмотреть и ранжировать много строк. Проверьте план на своих данных через EXPLAIN (ANALYZE, BUFFERS): маленькая таблица может быстрее обработаться последовательным сканированием, и это не обязательно признак ошибки.

Вариант 2: ближайшие строки с GiST

Если интерфейсу нужны, например, десять ближайших названий, используйте GiST и оператор расстояния. Для того же поля создайте индекс с операторным классом gist_trgm_ops:

CREATE INDEX catalog_item_name_trgm_gist
    ON catalog_item USING gist (name gist_trgm_ops);

SELECT id,
       name,
       name <-> 'беспроводные наушники' AS distance
FROM catalog_item
ORDER BY name <-> 'беспроводные наушники'
LIMIT 10;

GiST поддерживает поиск ближайших соседей: индекс может отдавать строки в порядке расстояния. При большом наборе это полезно для top-N выдачи, но само по себе не задаёт минимально приемлемое качество. Запрос вернёт до десяти ближайших строк, даже если все они плохие совпадения. Добавьте отдельное правило отсечения или проверьте сходство перед показом результатов.

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

Сходство с частью названия

Обычный % сравнивает строки целиком. Если поисковая фраза должна совпасть с фрагментом длинного поля, в pg_trgm есть оператор <% и функция word_similarity(). Порядок аргументов важен: слева передают короткий запрос, справа — строку-кандидат.

SET pg_trgm.word_similarity_threshold = 0.6;

SELECT id,
       name,
       word_similarity('наушники', name) AS score
FROM catalog_item
WHERE 'наушники' <% name
ORDER BY score DESC, id
LIMIT 20;

Такой поиск может найти нужный фрагмент внутри более длинного названия. Но «совпадение с частью» не всегда соответствует смыслу товара: короткий запрос способен дать неожиданные кандидаты. Порог для <% настраивается отдельно от порога обычного сходства. Для сравнения целых словесных фрагментов, ограниченных границами слов, есть <<% и strict_word_similarity(); выбирайте их только если такое поведение соответствует поиску в вашем интерфейсе.

Какой вариант выбрать

Для каталога с небольшими короткими названиями начните с GIN и порогового отбора, если важно не показывать случайные варианты. Если продукт должен всегда возвращать несколько ближайших результатов, испытайте GiST и задайте отдельную бизнес-проверку минимального качества. Когда ищете фрагмент в длинном поле, сравните обычное и словесное сходство на реальных запросах.

Что обычно ломается

• Порог скопировали из примера. На своих данных он может давать слишком много или слишком мало совпадений. Соберите набор реальных поисковых фраз, включая опечатки, и оцените выдачу при нескольких значениях.
• Индекс есть, но план его не выбирает. Планировщик учитывает размер таблицы и ожидаемую долю подходящих строк. Смотрите фактический план и время, а не только наличие индекса в схеме.
• Ожидают точное расстояние правок. <-> ранжирует по триграммному расстоянию. Если продуктовые требования говорят о числе замен или пропусков символов, нужно отдельно выбрать и проверить соответствующий алгоритм.
• Запрос слишком короткий. У короткой строки мало триграмм, поэтому различение кандидатов может быть слабым. Проверьте такие запросы отдельно и предусмотрите другой путь поиска, например точное совпадение или обычный полнотекстовый поиск.
• Не проверили язык и данные. Регистр, знаки препинания, дефисы, раскладка и написание названий влияют на практическую выдачу. Прогоните тесты на тех же строках, которые реально хранит база; не предполагайте, что нормализация совпадёт с правилами приложения.

Что делать дальше

Перед выбором индекса подготовьте небольшой тестовый набор из реальных запросов: точные названия, типичные опечатки, короткие слова и запросы по фрагменту. Для каждого варианта сравните качество первых результатов, время выполнения и план через EXPLAIN (ANALYZE, BUFFERS). После запуска следите за временем поиска и долей запросов без полезного совпадения. Версию PostgreSQL и доступные параметры сверяйте с документацией именно вашей основной версии: примеры здесь относятся к PostgreSQL 16.