Как работает полнотекстовый индекс GIN в PostgreSQL

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

Понимаете ли вы, как устроен инвертированный индекс и чем за него платите. Следом обычно спрашивают, почему индекс есть, а запрос из Go-сервиса его не использует, и чем GIN отличается от GiST.

Как отвечать

• структура: GIN (Generalized Inverted Index) это B-дерево по ключам, где каждый ключ ведёт к списку строк (TID), которые его содержат. Для полнотекстового поиска ключ это лексема из tsvector, для массивов элемент, для jsonb ключи и значения. Длинные списки хранятся отдельным деревом и сжимаются;
• поиск: запрос tsv @@ to_tsquery(...) раскладывается на лексемы, их списки пересекаются или объединяются по логике запроса, дальше Bitmap Index Scan и Bitmap Heap Scan по таблице;
• чего в индексе нет: позиций и весов лексем GIN не хранит, поэтому фразовый поиск <-> и ts_rank читают строки из таблицы;
• цена записи: одна строка порождает десятки ключей. Чтобы не тормозить вставку, по умолчанию включён fastupdate: новые записи копятся в pending list и вливаются в индекс при vacuum или при превышении gin_pending_list_limit (4 МБ). Пока список большой, поиск читает его целиком;
• схема: удобнее всего генерируемая колонка tsv tsvector GENERATED ALWAYS AS (to_tsvector('russian', coalesce(body, ''))) STORED и индекс USING gin (tsv).

Со стороны Go (pgx) строку пользователя передавайте параметром в websearch_to_tsquery('russian', $1): она понимает кавычки и минус и не падает на синтаксисе, в отличие от to_tsquery. Использование индекса проверяйте через EXPLAIN (ANALYZE, BUFFERS).

Про GiST: он хранит сигнатуры с потерями и перепроверяет строки, зато дешевле обновляется. Для текстового поиска документация PostgreSQL рекомендует GIN.

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

Строят индекс по to_tsvector('russian', body), а в запросе пишут to_tsvector(body) без конфигурации. Выражение другое, индекс не используется.

Ждут, что GIN по tsvector ускорит LIKE '%текст%'. Для подстрок нужен отдельный индекс с gin_trgm_ops из расширения pg_trgm.

Массово заливают данные в таблицу с уже созданным GIN-индексом. Быстрее загрузить данные, потом построить индекс, подняв maintenance_work_mem на время сборки.