Какие индексы использовать для JSON по атрибутам в PostgreSQL

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

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

Как отвечать

Сначала оговорите тип: индексы по содержимому работают для jsonb, у json остаются только индексы по выражению. Дальше три инструмента:

• GIN с jsonb_ops (класс по умолчанию): USING gin (data). Обслуживает @>, ?, ?|, ?& и jsonpath-операторы @?, @@. Подходит, когда фильтруют по разным, заранее неизвестным ключам;
• GIN с jsonb_path_ops: USING gin (data jsonb_path_ops). Меньше и быстрее, но умеет только @>, @?, @@, проверку ключа через ? не поддерживает;
• B-tree по выражению: CREATE INDEX ON orders ((data->>'status')). Лучший вариант для одного известного атрибута: равенство, диапазоны, сортировка. Для чисел нужен каст в индексе, ((data->>'total')::numeric), и тот же каст в запросе.

К ним добавляются частичные индексы и сгенерированная колонка GENERATED ALWAYS AS (...) STORED с обычным индексом, если атрибут стал постоянным фильтром.

Со стороны Java: в Hibernate 6 jsonb маппится через @JdbcTypeCode(SqlTypes.JSON), но JPQL таких операторов не знает, запрос пишут нативным. Оператор ? в PgJDBC конфликтует с плейсхолдером, его экранируют как ?? или переписывают через @>.

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

Строят GIN на всю колонку и фильтруют через data->>'status' = 'paid'. Оператор ->> в GIN не индексируется, будет последовательное чтение. Нужен либо @>, либо индекс по выражению.

Пишут выражение в запросе не так, как в индексе: без каста или с -> вместо ->>. Планировщик сравнивает выражения буквально.

Забывают цену записи. GIN на большом, часто обновляемом документе заметно тормозит UPDATE, и горячий атрибут правильнее вынести в обычную колонку.