Поиск по подстроке без учета порядка слов в PostgreSQL

При разработке поиска по комбинированному полю в базе данных часто возникает задача: найти запись, даже если слова введены в произвольном порядке, и при этом поддерживать поиск по части слова (подстроке). Стандартный оператор ILIKE с заменой пробелов на % не справляется с изменением порядка. Рассмотрим эффективные решения для PostgreSQL.

Проблема порядка слов при ILIKE

Допустим, в таблице есть поле с данными: Самара Зубчаниновка улица Ленина. Если пользователь ищет Зубчаниновка Самара, запрос ILIKE '%Зубчаниновка%Самара%' не найдёт запись, так как порядок слов не совпадает. При этом отказываться от поиска по подстроке нельзя - пользователи часто вводят части слов.

Решение: триграммы и pg_trgm

Модуль pg_trgm (триграммы) позволяет выполнять нечёткий поиск без учёта порядка. Он разбивает текст на последовательности из трёх символов и ищет совпадения. Для включения выполните:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Затем создайте GIN-индекс на поле:

CREATE INDEX idx_table_field_trgm ON table_name USING gin (field gin_trgm_ops);

Теперь поиск по подстроке в любом порядке выполняется через ILIKE с маской, но без фиксации последовательности:

SELECT * FROM table_name WHERE field ILIKE '%' || 'Зубчаниновка' || '%' AND field ILIKE '%' || 'Самара' || '%';

Такой запрос использует триграммный индекс и находит записи, содержащие оба слова в любом порядке.

Альтернатива: полнотекстовый поиск с tsquery

Если нужен полнотекстовый поиск с учётом морфологии, используйте to_tsvector и to_tsquery. Однако по умолчанию tsquery ищет только по целым словам или префиксам (:*). Чтобы включить поиск по подстроке, комбинируйте его с триграммами:

SELECT * FROM table_name WHERE to_tsvector('russian', field) @@ to_tsquery('russian', 'зубчанинов:* & самар:*') OR field ILIKE '%зубчанинов%' OR field ILIKE '%самар%';

Этот подход даёт высокую релевантность, но сложнее в реализации.

Сравнение методов

• ILIKE с несколькими условиями - просто и быстро, подходит для большинства сценариев с триграммами.
• Полнотекстовый поиск - лучше для ранжирования и морфологии, но требует дополнительной настройки для подстрок.
• pg_trgm - универсален, поддерживает как части слов, так и произвольный порядок.

Рекомендации по оптимизации

Для максимальной производительности:

• Всегда создавайте GIN-индекс с gin_trgm_ops.
• Избегайте использования LIKE '%...%' без индекса - это приводит к полному сканированию таблицы.
• При высоких нагрузках рассмотрите разделение поля на отдельные колонки (адрес, название, комментарий) для более точного поиска.

Таким образом, комбинация ILIKE с несколькими условиями и индекса на триграммах полностью решает задачу поиска по части слова без учёта порядка.