PostgreSQL: JSONB и GIN-индекс — таблица, запросы, индексы

Поиск по полю JSONB без индекса приводит к полному сканированию таблицы — на больших объёмах запросы тормозят. JSONB хранится в бинарном виде и поддерживает индексы; GIN ускоряет операторы содержания (@>), существования ключа (?) и комбинации (?&, ?|). Проблема: добавили колонку JSONB, фильтруете через @> или ?, но индекс не создали — план показывает Seq Scan. Симптомы: медленный SELECT по атрибутам, рост времени при росте таблицы. Ниже — таблица с JSONB, создание GIN-индекса, примеры запросов и проверка использования индекса по документации JSON Types и GIN.

Решение

Работа с полем JSONB и GIN-индексом для быстрого поиска. По JSON Types и GIN Indexes.

Таблица с JSONB

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attrs JSONB
);

GIN-индекс для поиска по JSONB

Для операторов @>, ?, ?&, ?|:

CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs);

Для частых запросов по одному ключу — выражение:

CREATE INDEX idx_products_attrs_brand ON products USING GIN ((attrs -> 'brand'));

Запросы

Содержит пару ключ–значение:

SELECT * FROM products WHERE attrs @> '{"color": "red"}';
SELECT * FROM products WHERE attrs @> '{"color": "red", "size": "M"}';

Есть ключ:

SELECT * FROM products WHERE attrs ? 'color';

Извлечь значение и фильтровать:

SELECT * FROM products WHERE attrs->>'brand' = 'Apple';

Агрегация по полю из JSONB:

SELECT attrs->>'brand' AS brand, COUNT(*), AVG((attrs->>'price')::numeric)
FROM products WHERE attrs ? 'price'
GROUP BY attrs->>'brand';

Полный пример

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attrs JSONB
);
CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs);

INSERT INTO products (name, attrs) VALUES
  ('iPhone', '{"brand": "Apple", "color": "black"}'),
  ('Galaxy', '{"brand": "Samsung", "color": "blue"}');

SELECT * FROM products WHERE attrs @> '{"color": "black"}';
SELECT * FROM products WHERE attrs ? 'brand';
SELECT name, attrs->>'brand' AS brand FROM products;

Проверка

План с GIN — после создания индекса выполните:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE attrs @> '{"color": "red"}';

В плане должен быть Bitmap Index Scan или Index Scan по idx_products_attrs_gin, а не Seq Scan. См. EXPLAIN ANALYZE.

Без индекса — удалите индекс, выполните тот же EXPLAIN. Должен появиться Seq Scan; на большой таблице Execution Time вырастет. После этого индекс можно создать снова.
Оператор ? — запросы с attrs ? 'key' также используют GIN-индекс по attrs. Проверьте план для такого запроса.

Типичные ошибки

Поиск по attrs->>‘key’ = ‘value’ без GIN по выражению — обычный GIN по всему attrs не всегда используется для ->>'key'. Для частых фильтров по одному ключу создайте GIN ((attrs -> ‘key’)) или используйте @> с объектом и общий GIN по attrs.
Индекс по JSON вместо JSONB — тип json в PostgreSQL не так удобен для индексов и операторов содержания. Для поиска и индексации используйте JSONB.
Подстановка значений в строку запроса — в приложении всегда передавайте JSON как параметр ($1), а не склеивайте строку. См. node-pg и параметризованные запросы.

Где применять

Prod / dev: каталоги с гибкими атрибутами (товары, события, метаданные), фильтры по полям внутри JSON. После создания таблицы с JSONB добавляйте GIN при запросах по содержанию или ключам.
Совместно с EXPLAIN — при замедлении запросов по JSONB проверяйте план и наличие GIN-индекса.

Связанные сниппеты: EXPLAIN ANALYZE — поиск медленного запроса, Node.js (pg): Pool и запросы.

Документация: JSON Types, GIN.

Как использовать