GIN, BTREE и expression indexes для JSONB: сравнение и выбор

Разработчики, активно использующие PostgreSQL и JSONB, регулярно сталкиваются с одним и тем же вопросом: какой индекс построить, чтобы запросы к документам оставались быстрыми, а объём хранилища не раздувался без причины? Механика JSONB-индексации не сводится к одному «правильному» варианту — выбор зависит от того, какие операторы вы применяете и какую именно часть документа хотите искать. Ниже мы разберём три ключевых подхода: классический GIN, BTREE и индексы по выражению, и на практических примерах покажем, что когда применять.

Почему выбор индекса для JSONB имеет значение

JSONB-столбец хранит данные в бинарном формате, и без индекса любой поиск внутри документа приводит к последовательному сканированию всей таблицы. Когда количество строк переваливает за десятки тысяч, даже простой фильтр data @> '{"status":"active"}' превращается в узкое место. Индекс не просто ускоряет чтение — он определяет, какие классы запросов вообще будут выполняться за приемлемое время. Ошибка здесь стоит дорого: слишком широкий индекс жрёт место и замедляет вставку, а слишком узкий не поможет там, где ожидалась производительность.

GIN — основной инструмент для поиска внутри JSONB

GIN (Generalized Inverted Index) специально спроектирован для индексации составных типов, таких как массивы и JSONB. В отличие от BTREE, который сравнивает значения целиком, GIN раскладывает документ на элементы — ключи и пары «ключ‑значение» — и строит для них внутренний B‑Tree, связывая каждый элемент со списком ссылок на строки.

Как работает GIN с jsonb_ops и jsonb_path_ops

Для JSONB PostgreSQL предлагает два операторных класса GIN:

jsonb_ops — класс по умолчанию, покрывающий самый широкий набор операторов;
jsonb_path_ops — специальный класс, оптимизированный под запросы на вхождение (@>).

Внутренне оба варианта создают обратный индекс по элементам JSONB, но jsonb_path_ops не генерирует отдельные записи для ключей как сущностей. Результат: более компактный индекс и более быстрая работа с containment-операторами, но без поддержки поиска просто по существованию ключа.

Какие операторы поддерживаются и в каких случаях индекс используется

Индекс, созданный командой

CREATE INDEX idx_data_gin ON my_table USING gin (data);

(по умолчанию jsonb_ops) активируется для следующих операторов:

? — существует ли указанный ключ на верхнем уровне;
?| — существует ли хотя бы один из перечисленных ключей;
?& — существуют ли все перечисленные ключи;
@> — проверка вхождения (содержит ли документ указанный фрагмент);
@? и @@ — проверка условия JSON Path.

Если вы укажете jsonb_path_ops:

CREATE INDEX idx_data_path_ops ON my_table USING gin (data jsonb_path_ops);

то индекс будет работать только с операторами @>, @? и @@. Запросы с ?, ?| и ?& такой индекс использовать не смогут и уйдут в полное сканирование.

Нюансы размера и производительности

По сравнению с jsonb_ops, индекс на jsonb_path_ops обычно получается значительно меньше, потому что в него не попадают отдельные записи «ключ существует». Это сокращает как дисковое пространство, так и время обновления индекса при вставке. Однако он отсекает целый пласт полезных запросов, так что применять его стоит осознанно.

Вставка и обновление строк с GIN-индексом на JSONB всегда дороже, чем без него: PostgreSQL вынужден разбирать новый документ на элементы и обновлять множество внутренних записей. Это плата за скорость чтения, и она должна быть оправдана реальной нагрузкой.

BTREE и JSONB — союз по расчёту

BTREE-индекс знаком каждому, кто работал с PostgreSQL, однако его прямое применение к колонке JSONB почти не встречается в реальных проектах.

Почему BTREE на всей JSONB-колонке редко имеет смысл

BTREE сравнивает значения целиком и оптимизирован для скалярных типов: чисел, строк, дат. JSONB-документ — это сложная структура, и операции сравнения вроде WHERE data > '{"id":100}' ничего не говорят бизнес-логике. Индекс построится, физически он будет существовать, но практически ни одному осмысленному запросу он не поможет, а место займёт. Поэтому документация PostgreSQL подталкивает использовать GIN, когда нужно искать что-то внутри JSONB.

Когда BTREE всё же может применяться

Ситуация меняется, когда мы достаём конкретное скалярное поле из JSONB и превращаем его в обычное значение. Например:

CREATE INDEX idx_price_btree ON my_table ((data->>'price')::numeric);

Такой индекс — уже не на JSONB-колонку, а на выражение — является BTREE по числу. Он позволит эффективно фильтровать WHERE (data->>'price')::numeric BETWEEN 100 AND 500, сортировать ORDER BY (data->>'price')::numeric и т.п. Это и есть основная ниша BTREE в мире JSONB: точечная работа с одним стабильно заполненным полем.

Expression indexes — точечная индексация полей JSONB

Индексы по выражению — ключевой инструмент, когда не нужно индексировать весь документ, а важна только часть информации.

Создание индекса на конкретном ключе или пути

Синтаксис работает, даже если целевая колонка — jsonb:

-- GIN-индекс по конкретному полю
CREATE INDEX idx_tags_gin ON my_table USING gin ((data->'tags'));

-- BTREE-индекс по извлечённому значению
CREATE INDEX idx_status_btree ON my_table ((data->>'status'));

В первом случае выражение data->'tags' возвращает сам JSONB-фрагмент, и к нему можно применить GIN с jsonb_ops или jsonb_path_ops. Во втором случае data->>'status' даёт текст, и для него естественно строится BTREE. Все такие индексы автоматически перестраиваются при изменении data, оставаясь актуальными.

Использование BTREE и GIN в expression indexes

Ключевой выбор: хотите вы искать контейнеры внутри поля или работать со скалярным значением? Если нужен поиск по тегам data->'tags' @> '["sql"]', то expression index с GIN на (data->'tags') будет лучшим решением. Если же поле однотипное — например, "price": 199.99 — и по нему часто сортируют, BTREE на приведённом к нужному типу выражении даст и фильтрацию, и сортировку.

Практическое сравнение по ключевым критериям

Покрытие поисковых сценариев

Поиск по существованию ключа. Оператор ? и его друзья требуют jsonb_ops. Ни jsonb_path_ops, ни BTREE здесь не помогут. Выбираем GIN на всю колонку либо GIN на выражение, если ключ может быть где угодно, или только на конкретный подраздел.
Проверка на вхождение (containment). С этим работают обе разновидности GIN. Если единственный частый предикат — это @>, лучше взять jsonb_path_ops и получить выигрыш в размере индекса.
Доступ к скалярному значению с фильтрацией и сортировкой. Здесь лидируют expression indexes с BTREE: запрос вроде WHERE data->>'status' = 'active' ORDER BY data->>'created_date' можно обслуживать BTREE-индексами по каждому участвующему полю. GIN на всю колонку пригоден для поиска data @> '{"status":"active"}', но сортировку всё равно не обеспечит.

Размер индекса и накладные расходы на запись

jsonb_path_ops всегда компактнее jsonb_ops на одном и том же наборе данных — это прямое следствие отсутствия отдельных записей о ключах. Expression indexes ещё более избирательны: индексируя только data->'payload', а не весь документ, вы радикально сокращаете объём.

Накладные расходы на запись растут вместе с размером индекса и количеством индексируемых элементов. GIN по умолчанию обновляется при каждой вставке и изменении, и этот процесс тем тяжелее, чем больше ключей и значений в документе. BTREE по выражению обычно дешевле в обслуживании, так как индексирует ровно одно значение в строке, но это значение приходится извлекать и, возможно, приводить.

Влияние на скорость чтения в типовых запросах

GIN-индекс превращает полное сканирование таблицы в быстрый поиск по инвертированному отображению — это особенно заметно на миллионах записей. При этом дополнительное приведение типов внутри запроса может помешать планировщику использовать expression index, поэтому выражения в индексе и запросе должны точно совпадать.

BTREE по выражению даёт молниеносный доступ по точному совпадению и возможность быстрой сортировки, что недоступно GIN. Для смешанных сценариев часто держат два индекса: один GIN для гибких поисков внутри документа, второй BTREE — на числовое или текстовое поле для сортировок и диапазонных фильтров.

Алгоритм выбора: что когда применять

Если нужен поиск по ключам и вложенным структурам

Основной кандидат — GIN с jsonb_ops на всём JSONB-столбце. Он перекроет операторы ?, ?|, ?&, @> и JSON Path. Когда нужны только проверки на вхождение и важен минимальный размер, смело берите jsonb_path_ops.

Если нужна фильтрация по конкретному полю с сортировкой

Здесь на первый план выходит expression index. Определите тип извлекаемого поля и постройте BTREE на выражении:

CREATE INDEX ON orders ((data->>'created_at') timestamptz);

Это позволит быстро фильтровать по дате и сортировать записи. Если поле используется и в containment-запросах, можно добавить отдельный GIN-индекс на это же подвыражение и получить лучшее из двух миров.

Если важен минимальный размер индекса

Рассмотрите два варианта:

GIN с jsonb_path_ops вместо jsonb_ops — выигрыш достигается отказом от избыточной для вас функциональности;
expression indexes только на те ключи, которые реально участвуют в запросах — вы сознательно вырезаете из индекса всё, что не используется.

Часто задаваемые вопросы

Какой индекс лучше для поиска по тегам внутри JSONB? Если теги лежат в массиве и запрос выглядит как data->'tags' @> '["sql"]', используйте GIN на выражении (data->'tags') — либо jsonb_ops, либо jsonb_path_ops, если операторы существования ключа не нужны.

Можно ли использовать BTREE для сортировки по полю внутри JSONB? Да, при условии, что поле извлечено и приведено к скалярному типу. Создаёте BTREE на выражении (data->>'field')::numeric и получаете сортировку без полного сканирования.

Чем отличается jsonb_ops от jsonb_path_ops и какой выбрать? jsonb_ops поддерживает операторы ?, ?|, ?&, @>, @?, @@. jsonb_path_ops поддерживает только @>, @? и @@, но строит более компактный индекс. Если вам не нужен поиск по существованию ключа, jsonb_path_ops предпочтительнее.

Expression index автоматически обновляется при изменении JSONB-поля? Да, в PostgreSQL любые индексы, включая expression indexes, автоматически поддерживаются в актуальном состоянии при вставке, обновлении и удалении строк.

Сильно ли GIN-индекс замедляет вставку и обновление? Заметно сильнее, чем BTREE по простому полю, потому что GIN должен разложить документ на элементы и модифицировать несколько внутренних записей. Насколько критично — зависит от объёма записей и сложности JSONB, но при высокой интенсивности записи рекомендуется тестировать на реальных данных.

Можно ли создать один индекс, который будет работать и для @>, и для ? одновременно? Да, GIN с операторным классом jsonb_ops покрывает оба оператора. Если вы создаёте индекс без явного указания класса, он по умолчанию будет jsonb_ops и сможет обслуживать и containment, и поиск по ключам.

Краткие итоги и рекомендации

Выбор индекса для JSONB сводится к точному пониманию своих запросов. GIN-индекс — тяжёлый, но универсальный инструмент для поиска внутри структуры документа; он незаменим для @>, ? и JSON Path. BTREE сам по себе JSONB не индексирует, но через индексы по выражению становится мощным двигателем сортировок и точной фильтрации по извлечённым полям. При дефиците места и работе только с containment-запросами jsonb_path_ops даст более лёгкий индекс без потери нужной функциональности.

Лучшая стратегия — не пытаться покрыть всё одним индексом, а честно завести два-три разных, ровно под типовые запросы. Тогда JSONB раскроет свою гибкость без неприятных сюрпризов по производительности на проде.

GIN, BTREE и expression indexes для JSONB: практическое сравнение