Какие индексы использовать для полнотекстового поиска по нескольким полям

Что проверяют

Отличаете ли вы полнотекстовый поиск от поиска подстроки и от составного B-tree индекса. Поиск по заголовку и описанию сразу обычно строят как одну поисковую структуру, а не как несколько индексов через OR. Следом часто спрашивают про морфологию русского языка и ранжирование.

Как отвечать

В PostgreSQL рабочая схема такая: собрать поля в один tsvector с весами и повесить на него GIN.

• колонка: search tsvector GENERATED ALWAYS AS (setweight(to_tsvector('russian', coalesce(title, '')), 'A') || setweight(to_tsvector('russian', coalesce(body, '')), 'B')) STORED;
• индекс: CREATE INDEX ON articles USING gin (search);
• запрос: WHERE search @@ websearch_to_tsquery('russian', :q), сортировка по ts_rank. Веса поднимают совпадение в заголовке выше совпадения в тексте;
• подстроки и опечатки: другая задача, её закрывает расширение pg_trgm с классом gin_trgm_ops, он ускоряет ILIKE '%...%' и поиск по похожести.

В MySQL это FULLTEXT (title, body) и MATCH(title, body) AGAINST (...), причём список колонок в MATCH должен совпадать с индексом.

Когда нужны синонимы, подсветка, фасеты и сложная релевантность, поиск выносят в Elasticsearch или OpenSearch: запрос multi_match с бустами вроде title^3. В Java для этого есть Hibernate Search с @FullTextField и Spring Data Elasticsearch. Продумайте синхронизацию базы и индекса: outbox или CDC через Debezium вместо записи в два места из кода.

Где спотыкаются

Забывают coalesce. Если одно поле NULL, конкатенация даёт NULL, и строка пропадает из поиска целиком.

Вызывают to_tsvector без явной конфигурации. Такая функция не считается IMMUTABLE, и PostgreSQL откажется строить по ней индекс или сгенерированную колонку.

Надеются, что LIKE '%слово%' подхватит B-tree или полнотекстовый индекс. Не подхватит: для подстроки нужны триграммы, для слов нужен tsvector.