GIN для jsonb и массивов

В Brew у каждого напитка в админке висел блок «характеристики» — гибкий jsonb ({"milk": "oat", "size": "L"}) и массив тегов ({coffee, seasonal}). Пока меню было маленьким, фильтр «покажи всё с подарком» (attrs @> '{"gift": true}') работал нормально. На двухстах тысячах позиций он встал: каждый запрос — полный Seq Scan. Разработчик попробовал привычное CREATE INDEX ON drink_specs (attrs) — и EXPLAIN всё равно показал Seq Scan. B-tree, который мы строили во всех прошлых юнитах, тут бесполезен в принципе: он индексирует значение целиком, а нам надо заглянуть внутрь — есть ли в jsonb такой ключ, есть ли в массиве такой элемент.

Цель юнита — понять, почему «поиск внутри» jsonb и массива требует другого типа индекса, и познакомиться с GIN (Generalized Inverted Index), который для этого и сделан. Это последний кирпич перед модулем 07, где jsonb, массивы и полнотекстовый поиск разбираются подробно — здесь мы только ставим под них правильный индекс.

Почему B-tree не годится, а GIN годится

B-tree хранит отсортированные целые значения столбца. Он отвечает на «равно/больше/меньше/начинается с» — то есть на вопросы про значение как единое целое. Но attrs @> '{"gift": true}' спрашивает не «равен ли весь jsonb вот этому», а «содержит ли он эту пару ключ-значение». Целиком-значение тут не сравнить: внутри attrs может быть сколько угодно других ключей. Тот же случай с массивом: tags @> ARRAY['limited'] — «входит ли элемент в набор», а не «равен ли весь массив».

GIN устроен наоборот — это инвертированный индекс. Он раскладывает каждое значение на составляющие (ключи и значения jsonb, элементы массива, позже — слова текста) и для каждой составляющей держит список строк, где она встречается. Спросить «у кого есть gift: true» — это взять готовый список из индекса, как предметный указатель в конце книги. Оператор containment @> (и для jsonb, и для массивов) умеет читать этот указатель — отсюда Bitmap Index Scan по GIN вместо Seq Scan.

Containment и опклассы

GIN по jsonb обслуживает целое семейство операторов: @> (содержит), ? (есть ли ключ), ?|/?& (любой/все ключи из списка). У GIN для jsonb есть два опкласса:

jsonb_ops (по умолчанию) — индексирует и ключи, и значения; поддерживает все операторы выше.
jsonb_path_ops — индексирует только пути «ключ→значение»; меньше и быстрее на @>, но не умеет ? (проверку существования ключа). Берут его, если в коде только containment-запросы:

sql

CREATE INDEX ... USING gin (attrs jsonb_path_ops);

Для массивов GIN обслуживает @> (содержит все), <@ (содержится в), && (пересекается) и = ANY.

⚠️ GIN отлично читается, но дороже на запись: вставка одной строки трогает индекс по разу на каждый элемент/ключ внутри значения. Поэтому под нагрузку с частыми апдетами jsonb у GIN есть параметр fastupdate (отложенная пачечная вставка) — но это уже тюнинг, который держит DBA. Девелоперский вывод: GIN — для «много читаем по содержимому, пишем умеренно».

GIN — это предметный указатель

B-tree индексирует значение целиком и отвечает на «равно/больше/меньше». GIN устроен наоборот: раскладывает каждое значение на составляющие (ключи и значения jsonb, элементы массива) и для каждой держит список строк, где она встречается — как предметный указатель в конце книги:

plaintext

B-tree: строка → значение целиком            (=, <, >, префикс)
  row 7 → {"milk":"oat","gift":true}   можно спросить «равно ли всё это» — но не «есть ли gift»
 
GIN: составляющая → список строк, где она есть   (инвертированный указатель)
  "milk"=oat   → [3, 7, 11, 15, …]
  "gift"=true  → [7, 200, 400, …]   ← attrs @> '{"gift":true}' = взять этот готовый список
  tag=coffee   → [1, 2, 3, …]
  tag=limited  → [200, 400, 600, …] ← tags @> ARRAY['limited'] = взять этот список

Поэтому containment @> летит по GIN (Bitmap Index Scan), а B-tree на нём срывается в Seq Scan. Операторы и опклассы GIN:

Тип столбца	Операторы	Опкласс
`jsonb`	`@>` (содержит), `?` (есть ключ), `?\|` / `?&` (любой / все ключи)	`jsonb_ops` (по умолчанию) — всё перечисленное
`jsonb`, только `@>`	`@>`	`jsonb_path_ops` — меньше и быстрее, но без `?`
`text[]` (массив)	`@>` (содержит все), `<@` (содержится в), `&&` (пересекается)	`array_ops` (по умолчанию)

Что показывает наш код

demo.sql строит drink_specs_lab (200 000 строк: jsonb attrs + text[] tags, редкие признаки gift/limited у 0.5%) и дважды объясняет два containment-запроса — до и после GIN:

sql

-- без индекса оба идут Seq Scan
SELECT id FROM drink_specs_lab WHERE attrs @> '{"gift": true}';   -- jsonb
SELECT id FROM drink_specs_lab WHERE tags  @> ARRAY['limited'];   -- массив
 
CREATE INDEX drink_specs_lab_attrs_gin ON drink_specs_lab USING gin (attrs);
CREATE INDEX drink_specs_lab_tags_gin  ON drink_specs_lab USING gin (tags);
 
-- с GIN оба идут Bitmap Index Scan по GIN

Запуск

docker compose up -d
make lecture L=06-indexing-and-explain/06-05-gin-for-jsonb-and-arrays

Вывод:

plaintext

== 1) jsonb @> БЕЗ индекса — Seq Scan (B-tree тут не помощник) ==
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on drink_specs_lab (actual rows=1000.00 loops=1)
   Filter: (attrs @> '{"gift": true}'::jsonb)
   Rows Removed by Filter: 199000
 
 
== 2) массив @> БЕЗ индекса — тоже Seq Scan ==
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on drink_specs_lab (actual rows=1000.00 loops=1)
   Filter: (tags @> '{limited}'::text[])
   Rows Removed by Filter: 199000
 
 
== создаём два GIN-индекса: по attrs (jsonb) и по tags (массив) ==
 
== 3) jsonb @> С GIN — Bitmap Index Scan по GIN ==
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on drink_specs_lab (actual rows=1000.00 loops=1)
   Recheck Cond: (attrs @> '{"gift": true}'::jsonb)
   Heap Blocks: exact=1000
   ->  Bitmap Index Scan on drink_specs_lab_attrs_gin (actual rows=1000.00 loops=1)
         Index Cond: (attrs @> '{"gift": true}'::jsonb)
         Index Searches: 1
 
 
== 4) массив @> С GIN — Bitmap Index Scan по GIN ==
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on drink_specs_lab (actual rows=1000.00 loops=1)
   Recheck Cond: (tags @> '{limited}'::text[])
   Heap Blocks: exact=1000
   ->  Bitmap Index Scan on drink_specs_lab_tags_gin (actual rows=1000.00 loops=1)
         Index Cond: (tags @> '{limited}'::text[])
         Index Searches: 1

Без индекса оба @> идут Seq Scan'ом и выкидывают по 199 000 строк — B-tree сюда не поставить, он не умеет смотреть внутрь значения. После двух CREATE INDEX ... USING gin (...) оба запроса берут GIN через Bitmap Index Scan: индекс отдал список подходящих строк по содержимому. Recheck Cond — нормальная часть bitmap-плана: после индекса Postgres перепроверяет условие на самих строках.

Заборчик

Что мы упростили:

GIN — для поиска по содержимому (@>, ?, &&). Если по jsonb-полю нужен поиск по равенству скаляра (attrs->>'milk' = 'oat') — это снова B-tree, но по выражению ((attrs->>'milk'), как в 06-03), а не GIN. Тип индекса выбирают под форму запроса, а не под тип столбца.
Мы взяли селективный признак (0.5% строк) — на нём GIN явно выигрывает. На признаке, который есть у половины строк, планировщик осознанно вернётся к Seq Scan (тот же урок селективности, что в 06-01).
У GIN своя цена записи и обслуживания (fastupdate, раздувание, перестройка) — это тюнинг под нагрузку, территория DBA.
jsonb мощный, но не бесплатный. Когда гибкая схема в jsonb оправдана, а когда поле стоит вынести в нормальный столбец с констрейнтом — отдельный разговор в 07-02.

Девелоперский вывод один: для containment-поиска по jsonb/массиву ставь GIN и проверяй план в EXPLAIN.

Что забрать с собой

B-tree индексирует значение целиком (=, <, >, префикс); «поиск внутри» jsonb/массива он не умеет — там Seq Scan.
GIN — инвертированный индекс: раскладывает значение на ключи/элементы и обслуживает containment @> (а для jsonb ещё ?, ?|, ?&).
CREATE INDEX ... USING gin (col); для массивов @>/<@/&&, для jsonb — семейство операторов.
Опкласс jsonb_path_ops — меньше и быстрее на @>, но без ?; берут под чисто containment-запросы.
GIN дорог на запись (трогает индекс по разу на элемент) — он для «читаем по содержимому много, пишем умеренно».

Дальше — 06-06 «CREATE INDEX CONCURRENTLY»: как построить индекс на горячей таблице, не заблокировав запись на время сборки.