PostgreSQL pg_trgm: GIN-индекс для поиска по подстроке

Расширение pg_trgm строит GIN-индекс по триграммам и ускоряет поиск по шаблону LIKE или ILIKE, например по части названия. Это полезно, когда обычный B-tree не помогает с шаблоном, начинающимся с процента. Для коротких поисковых строк планировщик может выбрать последовательное чтение вместо индекса.

Создать индекс и выполнить поиск

Замените products и name на таблицу и текстовый столбец проекта. CREATE INDEX CONCURRENTLY запускайте отдельно от транзакции:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY IF NOT EXISTS products_name_trgm_idx
    ON products USING gin (name gin_trgm_ops);

PREPARE product_search(text) AS
    SELECT id, name
    FROM products
    WHERE name ILIKE '%' || $1 || '%';

EXECUTE product_search('keyboard');
DEALLOCATE product_search;

Перед установкой проверьте, что pg_trgm доступно в сборке PostgreSQL и у роли есть право CREATE в текущей базе. Построение индекса с CONCURRENTLY уменьшает блокировку обычных записей, но всё равно нагружает базу; проверьте план на реальных данных. Параметр запроса передаётся отдельно от SQL. Символы процента и подчёркивания в нём остаются wildcard-символами, поэтому экранируйте их, если нужен буквальный поиск.

GIN подходит для фильтрации ILIKE и поиска по триграммному сходству. Для ближайших строк с сортировкой по расстоянию <-> документация рекомендует рассмотреть GiST: GIN такой поиск эффективно не обслуживает.

Документация: pg_trgm.