13.04.2026 09:35
badcasedaily1 0 6100 Источник

Привет, Хабр!

В PostgreSQL есть два оператор‑класса для GIN‑индексов на JSONB‑столбцах: дефолтный jsonb_ops и альтернативный jsonb_path_ops. Документация сухо объясняет разницу в паре абзацев, на оверфлоу видим какие‑то противоречивые советы, в итоге большинство просто пишут CREATE INDEX ... USING GIN (data),то есть используют jsonb_ops по умолчанию, не задумываясь.

Между тем, на таблице в 10 миллионов строк неправильный выбор оператор‑класса может означать индекс в 4 раза больше нужного и запросы в 2–3 раза медленнее. Разберём, чем эти два класса отличаются на уровне внутренней структуры, какие операторы поддерживает каждый, и по каким критериям выбирать.

GIN (Generalized Inverted Index) — это инвертированный индекс. Та же идея, что в полнотекстовом поиске: вместо «документ содержит слова [a, b, c]» хранится «слово a встречается в документах [1, 5, 17]». Для JSONB это работает аналогично, но индексируются не слова, а элементы JSON‑структуры.

Ключевой вопрос — что именно считать «элементом». И вот тут‑то два оператор‑класса расходятся принципиально.

jsonb_ops: индексируем ключи и значения отдельно

CREATE INDEX idx_data ON products USING GIN (data);
-- Эквивалентно:
CREATE INDEX idx_data ON products USING GIN (data jsonb_ops);

jsonb_ops разбирает JSON‑документ на отдельные элементы и индексирует каждый по отдельности. Для документа:

{"brand": "Apple", "specs": {"ram": 16, "storage": 512}}

В GIN‑индекс попадут отдельные записи: ключ brand, значение "Apple", ключ specs, ключ ram, значение 16, ключ storage, значение 512. Каждый ключ и каждое значение — отдельная запись в инвертированном индексе, без информации о пути.

Это даёт широкий набор поддерживаемых операторов:

-- Containment (@>) — основной
SELECT * FROM products WHERE data @> '{"brand": "Apple"}';

-- Existence (?) — есть ли ключ
SELECT * FROM products WHERE data ? 'brand';

-- Any existence (?|) — есть ли хотя бы один из ключей
SELECT * FROM products WHERE data ?| array['brand', 'category'];

-- All existence (?&) — есть ли все ключи
SELECT * FROM products WHERE data ?& array['brand', 'specs'];

Все четыре оператора используют GIN‑индекс. Это главное преимущество jsonb_ops — универсальность. Если запросы разнообразные и заранее непредсказуемые (пользователь может фильтровать по любому ключу), jsonb_ops покроет все случаи.

Но за универсальность приходится платить. Поскольку каждый ключ и значение индексируются отдельно, индекс не знает о структуре документа. Когда вы ищете data @> '{"brand": "Apple"}', GIN находит все строки, где есть ключ brand И значение "Apple", но — внимание — не знает, что "Apple" должен быть именно значением ключа brand. Он находит пересечение двух множеств. Для большинства данных это пересечение совпадает с правильным ответом, но в индексе хранится больше информации, чем нужно, и размер его соответствующий.

jsonb_path_ops: индексируем пути целиком

CREATE INDEX idx_data_path ON products USING GIN (data jsonb_path_ops);

jsonb_path_ops работает уже иначе. Вместо разбиения на отдельные ключи и значения он строит хеш полного пути от корня до значения. Для того же документа:

{"brand": "Apple", "specs": {"ram": 16, "storage": 512}}

В индекс попадут хеши путей: hash(brand, "Apple"), hash(specs, ram, 16), hash(specs, storage, 512). Каждая запись — полный путь от корня, а не отдельный фрагмент.

Это даёт две фичи.

Первая — размер. Поскольку путь хешируется в одно значение (а не раскладывается на N отдельных записей для N уровней вложенности), индекс занимает значительно меньше места. На реальных данных с глубоко вложенными JSON‑документами разница может быть 2–4 раза.

Второе — точность поиска. Когда вы ищете data @> '{"brand": "Apple"}', индекс находит строки по хешу пути hash(brand, "Apple"). Он не может ошибочно найти строку, где "Apple" — значение другого ключа. Меньше перепроверок, следовательно быстрее запрос.

Но есть ограничение. jsonb_path_ops поддерживает только оператор @> (containment). Операторы ?, ?|, ?& не работают, индекс просто не может ответить на вопрос «есть ли в документе ключ X», потому что он хранит хеши путей, а не сами ключи.

-- Работает с jsonb_path_ops
SELECT * FROM products WHERE data @> '{"brand": "Apple"}';

-- НЕ работает с jsonb_path_ops (будет seq scan)
SELECT * FROM products WHERE data ? 'brand';

Бенчмарк на данных

Возьмём сценарий: таблица events с 10 миллионов строк, JSONB‑столбец payload содержит событие аналитики с 15–20 ключами, вложенность до 3 уровней.

-- Создаём оба индекса
CREATE INDEX idx_events_ops  ON events USING GIN (payload);
CREATE INDEX idx_events_path ON events USING GIN (payload jsonb_path_ops);

Размер индексов (конкретные цифры зависят от данных):

idx_events_ops:  1.8 GB
idx_events_path: 620 MB

Разница почти в 3 раза. Для jsonb_path_ops меньше места на диске, меньше страниц в shared_buffers, быстрее VACUUM.

Запрос с оператором @>:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE payload @> '{"event_type": "purchase", "source": "mobile"}';

С jsonb_ops: Bitmap Index Scan, 1200 страниц, 45 мс. С jsonb_path_ops: Bitmap Index Scan, 380 страниц, 18 мс.

На простом запросе jsonb_path_ops быстрее в 2–2.5 раза. Причина в том, что у нас меньше объектов которые нужно перепроверять на основной таблице.

Partial GIN index: ещё точнее

Если запросы всегда фильтруют по определённому подмножеству данных, partial index ужимает индекс ещё сильнее:

-- Индексируем только активные товары категории electronics
CREATE INDEX idx_products_electronics
ON products USING GIN (data jsonb_path_ops)
WHERE (data->>'status') = 'active'
  AND (data->>'category') = 'electronics';

Этот индекс будет использован только для запросов, в WHERE которых есть те же условия. Зато он в десятки раз меньше полного индекса.

Индекс на выражение: когда не нужен весь документ

Если запросы всегда обращаются к одному ключу, GIN‑индекс на весь JSONB уже какой‑то перегруз. Проще и эффективнее индексировать конкретное выражение:

-- Вместо GIN на всё
CREATE INDEX idx_brand ON products USING GIN (data);

-- B-tree на конкретный ключ
CREATE INDEX idx_brand ON products ((data->>'brand'));

-- Для запросов вида:
SELECT * FROM products WHERE data->>'brand' = 'Apple';

B‑tree‑индекс на data->>'brand' будет в 10–50 раз меньше GIN‑индекса на весь data, и для равенства по одному ключу быстрее. GIN нужен, когда запросы разнообразные или используют containment на вложенных структурах.

Для поиска по диапазону во вложенном числовом поле:

CREATE INDEX idx_price ON products ((((data->'specs'->>'price')::numeric)));

SELECT * FROM products WHERE (data->'specs'->>'price')::numeric BETWEEN 1000 AND 2000;

Комбинирование с jsonpath (PostgreSQL 12+)

С PostgreSQL 12 появились jsonpath‑операторы @? и @@. Они дают более выразительные запросы, но с GIN‑индексами работают только через jsonb_ops:

-- jsonpath: найти товары, где цена > 1000
SELECT * FROM products
WHERE data @? '$.specs ? (@.price > 1000)';

-- Этот запрос может использовать GIN (jsonb_ops), но НЕ jsonb_path_ops

Если вам нужны jsonpath‑запросы с GIN‑индексом, jsonb_ops уже единственный вариант. Впрочем, для таких запросов часто эффективнее B‑tree или BRIN‑индекс на конкретное выражение.

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

JSON_TABLE: влияние на стратегию индексирования

PostgreSQL 17 добавил JSON_TABLE — функцию, которая разворачивает JSON‑массивы и объекты в реляционные строки. С тех пор вместо хранения всего в одном JSON‑столбце и хитрых индексов, можно развернуть данные в виртуальную таблицу и применить стандартные реляционные методы.

SELECT jt.*
FROM orders,
     JSON_TABLE(
         order_data,
         '$.items[*]' COLUMNS (
             product_name VARCHAR(100) PATH '$.name',
             quantity INT PATH '$.qty',
             price NUMERIC PATH '$.price'
         )
     ) AS jt
WHERE jt.price > 1000;

JSON_TABLE часто проще и понятнее, чем jsonb_array_elements + кастинг. Индексирование здесь работает на уровне внешних условий, а не на уровне содержимого JSON.

Как выбирать: алгоритм решения

  • Вопрос первый: запросы используют только @> (containment)? Если да — jsonb_path_ops. Он меньше, быстрее, да и точнее. Нет причин использовать jsonb_ops, если вам не нужны операторы ?, ?|, ?&.

  • Вопрос второй: нужны ли проверки наличия ключей (?, ?|, ?&)? Если да — только jsonb_ops. jsonb_path_ops их не поддерживает.

  • Вопрос третий: запросы всегда обращаются к одному‑двум конкретным ключам? Тогда, возможно, GIN не нужен вообще. B‑tree на (data->>'field') будет компактнее и быстрее для equality и range запросов.

  • Вопрос четвёртый: данные большие, вложенность глубокая, запросы разнообразные? Рассмотрите два индекса: jsonb_path_ops для containment‑запросов (основная нагрузка) и B‑tree‑индексы на конкретные поля для точечных запросов. Это дешевле одного раздутого jsonb_ops.

И в любом случае — EXPLAIN (ANALYZE, BUFFERS) перед тем как решать.

А если хочется посмотреть смежные практические разборы, можно обратить внимание на открытые уроки:

  • 5 мая, 20:00. «Postgres + JSON: реляционная мощь, документная гибкость». Записаться

  • 21 мая, 20:00. «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто». Записаться

Если вам в целом интересна аналитика данных, SQL и прикладная работа с хранилищами, можно посмотреть и другие курсы из каталога по аналитике — там собраны программы для разных уровней и задач, от работы с запросами и данными до более широких аналитических инструментов. ☛ [Перейти к каталогу]

Комментарии (0)