GIN на JSONB не помогает условию ->>: выбираем индекс под запрос

В событиях хранится payload JSONB. Создан GIN по всему документу, но фильтр payload ->> type = payment всё равно читает много строк. Возникает желание пересоздать индекс или поменять настройки сервера, хотя сначала нужно сравнить операторы запроса и индекса.

GIN по JSONB поддерживает определённые операции над документом, например containment. Текстовое равенство по извлечённому полю — другое выражение. Под него можно создать B-tree на выражение или переписать фильтр на подходящий JSONB-оператор с сохранением смысла.

Как изменить решение

CREATE INDEX events_type_idx ON events ((payload ->> 'type'));
EXPLAIN
SELECT id FROM events WHERE payload ->> 'type' = 'payment';

-- Вариант для поиска по структуре документа:
CREATE INDEX events_payload_gin ON events USING gin (payload);
EXPLAIN
SELECT id FROM events WHERE payload @> '{"type":"payment"}'::jsonb;

Первый вариант выражает поиск по текстовому значению поля. Второй ищет документ, содержащий указанную пару ключ–значение. Для строкового type они решают близкую задачу, но произвольные преобразования типов нельзя переносить между ними без проверки.

Если type обязателен и часто участвует в фильтрах, отдельный типизированный столбец может быть удобнее JSONB. У него понятные ограничения, статистика и модель индексов. JSONB полезен для изменяемой структуры события, но не требует прятать внутрь каждый устойчивый ключ.

Что проверить на своих данных

Проверьте события со строковым type, отсутствующим ключом, JSON null и числом. Затем сравните результаты двух SELECT и только после этого их планы. Для измерений нужны реалистичные доли payment: поиск половины таблицы может не выиграть от индекса.

Два индекса в примере показывают альтернативы, а не обязательный набор для каждой таблицы. Поддержка большого GIN требует места и работы при записи. Выберите реальные запросы, частоту изменения payload и допустимую стоимость импорта. Если нужен только один скалярный атрибут, узкий индекс часто проще объяснить и сопровождать.

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: GIN на JSONB не помогает условию ->> — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.