Пошук уроків, статей та іншого контенту
Створите індекси для JSONB, порівняєте операторні класи та оптимізуєте пошук за ключами й значеннями.
jsonb зберігає дані у бінарному форматі та підтримує пошук усередині документа. Без індексу PostgreSQL зазвичай змушений перевірити кожен рядок таблиці:
SELECT *
FROM orders
WHERE payload @> '{"status": "paid"}'::jsonb;Для великих таблиць це може бути повільно, особливо якщо запити виконуються часто. Для пошуку вмісту JSONB найчастіше використовують індекси типу GIN.
GIN зберігає зв’язки між елементами документа та рядками таблиці. Це дає змогу швидко знаходити рядки за ключами, значеннями та фрагментами JSONB.
Базовий індекс створюється так:
CREATE INDEX orders_payload_gin_idx
ON orders
USING GIN (payload);Якщо операторний клас не вказано явно, PostgreSQL використовує jsonb_ops.
Такий індекс добре підходить для запитів з операторами:
@> — перевірка, чи містить JSONB вказаний фрагмент;
<@ — перевірка, чи міститься JSONB у вказаному фрагменті;
? — перевірка наявності ключа або елемента масиву;
?| — наявність хоча б одного з ключів;
?& — наявність усіх вказаних ключів;
@? і @@ — окремі JSONPath-запити.
Приклади запитів:
-- Документ містить пару status: paid
SELECT *
FROM orders
WHERE payload @> '{"status": "paid"}'::jsonb;
-- У документі є ключ customer_id
SELECT *
FROM orders
WHERE payload ? 'customer_id';
-- Є хоча б один із ключів
SELECT *
FROM orders
WHERE payload ?| ARRAY['status', 'customer_id'];
-- Є всі вказані ключі
SELECT *
FROM orders
WHERE payload ?& ARRAY['status', 'customer_id'];jsonb_ops і jsonb_path_opsОператорний клас визначає, як саме індекс зберігає дані та які оператори може обслуговувати.
jsonb_opsЦе стандартний операторний клас:
CREATE INDEX orders_payload_ops_idx
ON orders
USING GIN (payload jsonb_ops);Особливості:
підтримує широкий набір операторів;
працює з перевіркою наявності ключів через ?, ?| і ?&;
підтримує пошук через @>;
зазвичай займає більше місця;
може бути повільнішим або більшим для простих containment-запитів.
Використовуйте jsonb_ops, якщо запити перевіряють не лише вміст JSONB, а й наявність ключів.
jsonb_path_opsЦей операторний клас створюється явно:
CREATE INDEX orders_payload_path_idx
ON orders
USING GIN (payload jsonb_path_ops);Він орієнтований на пошук фрагментів JSONB через @> та підтримувані JSONPath-оператори.
Особливості:
часто має менший розмір;
може бути ефективнішим для частих запитів через @>;
не підтримує оператори перевірки ключів ?, ?| і ?&;
не є універсальною заміною jsonb_ops.
Порівняння:
-- Працює з jsonb_ops і jsonb_path_ops
SELECT *
FROM orders
WHERE payload @> '{"status": "paid"}'::jsonb;
-- Потребує jsonb_ops
SELECT *
FROM orders
WHERE payload ? 'status';Не варто без потреби створювати обидва GIN-індекси на одному стовпці. Вони збільшують розмір бази та вартість операцій INSERT, UPDATE і DELETE. Вибір залежить від реальних запитів.
Наведений приклад створює таблицю замовлень, заповнює її тестовими даними та демонструє різні типи індексів.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO orders (payload)
SELECT jsonb_build_object(
'status',
CASE
WHEN i % 4 = 0 THEN 'paid'
WHEN i % 4 = 1 THEN 'new'
WHEN i % 4 = 2 THEN 'cancelled'
ELSE 'processing'
END,
'customer_id', i % 500,
'total', (i % 200) * 10,
'tags', jsonb_build_array(
CASE WHEN i % 2 = 0 THEN 'online' ELSE 'store' END,
CASE WHEN i % 3 = 0 THEN 'priority' ELSE 'regular' END
),
'meta', jsonb_build_object(
'source', CASE WHEN i % 2 = 0 THEN 'web' ELSE 'mobile' END
)
)
FROM generate_series(1, 20000) AS numbers(i);
-- Оновлюємо статистику після масового вставлення
ANALYZE orders;
-- Універсальний GIN-індекс
CREATE INDEX orders_payload_ops_idx
ON orders
USING GIN (payload jsonb_ops);
-- B-tree-індекс для конкретного значення всередині JSONB
CREATE INDEX orders_status_idx
ON orders ((payload->>'status'));
-- Числове значення потрібно привести до numeric
CREATE INDEX orders_total_idx
ON orders (((payload->>'total')::numeric));
ANALYZE orders;
-- Пошук за фрагментом JSONB
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE payload @> '{"status": "paid"}'::jsonb;
-- Пошук за наявністю ключа
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE payload ? 'customer_id';
-- Пошук за значенням через expression index
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE payload->>'status' = 'paid';
-- Порівняння числового значення
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE (payload->>'total')::numeric >= 1000;EXPLAIN (ANALYZE, BUFFERS) показує фактичний план і час виконання. У плані для GIN-індексу можна побачити Bitmap Index Scan та Bitmap Heap Scan. Для B-tree-індексу зазвичай використовується Index Scan або bitmap-план.
На маленьких таблицях PostgreSQL може свідомо вибрати Seq Scan, оскільки повне послідовне читання в такому випадку дешевше. Це не означає, що індекс не працює.
GIN зручний для пошуку за різними ключами JSONB. Але якщо застосунок регулярно читає одне й те саме поле, expression index може бути кращим.
CREATE INDEX orders_status_idx
ON orders ((payload->>'status'));Тоді запит має використовувати такий самий вираз:
SELECT *
FROM orders
WHERE payload->>'status' = 'paid';Індекс прив’язаний не до абстрактного ключа status, а до конкретного виразу payload->>'status'.
Для числових значень важливо виконувати приведення типу і в індексі, і в запиті:
CREATE INDEX orders_customer_id_idx
ON orders (((payload->>'customer_id')::integer));
SELECT *
FROM orders
WHERE (payload->>'customer_id')::integer = 42;Без приведення до числа PostgreSQL порівнюватиме значення як текст. Наприклад, текстове сортування відрізняється від числового:
'100' < '20' -- для текстового порівняння
100 > 20 -- для числового порівнянняВибір залежить від форми запитів.
Підходить, коли:
ключі документа можуть відрізнятися;
потрібно шукати фрагмент JSONB;
використовуються @>, ?, ?| або ?&;
структура документа має кілька рівнів вкладеності.
Приклад:
SELECT *
FROM orders
WHERE payload @> '{
"status": "paid",
"meta": {"source": "web"}
}'::jsonb;Підходить, коли:
часто фільтрують за одним конкретним полем;
значення потрібно сортувати або порівнювати як число;
потрібен звичайний B-tree для =, <, >, ORDER BY.
Приклад:
CREATE INDEX orders_total_idx
ON orders (((payload->>'total')::numeric));
SELECT *
FROM orders
WHERE (payload->>'total')::numeric BETWEEN 500 AND 1000
ORDER BY (payload->>'total')::numeric;Для одного часто використовуваного скалярного поля expression index зазвичай простіший і компактніший за повний GIN-індекс.
Оператор @> порівнює структуру JSONB, тому вкладені об’єкти можна передавати як фрагмент документа:
SELECT *
FROM orders
WHERE payload @> '{
"meta": {
"source": "mobile"
}
}'::jsonb;Для масивів @> перевіряє наявність елементів:
SELECT *
FROM orders
WHERE payload @> '{"tags": ["priority"]}'::jsonb;Цей запит означає, що масив tags містить елемент "priority". Він не вимагає повної рівності масиву.
Для аналізу запиту використовуйте:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE payload @> '{"customer_id": 42}'::jsonb;Звертайте увагу на:
тип сканування: Seq Scan, Index Scan або Bitmap Index Scan;
actual time — фактичний час виконання;
rows — кількість знайдених рядків;
Buffers — кількість прочитаних блоків;
різницю між оціненими та фактичними кількостями рядків.
Після значних змін даних оновлюйте статистику:
ANALYZE orders;Якщо значення дуже нерівномірно розподілені, оцінка селективності може бути неточною. У такій ситуації планувальник іноді обирає послідовне сканування навіть за наявності індексу.
CREATE INDEX orders_payload_btree_idx
ON orders (payload);B-tree не є стандартним вибором для пошуку фрагмента JSONB через @> або наявності ключів. Для таких запитів використовуйте GIN.
jsonb_path_ops для перевірки ключівCREATE INDEX orders_payload_path_idx
ON orders
USING GIN (payload jsonb_path_ops);Такий індекс не обслуговує:
WHERE payload ? 'status'Якщо потрібні ?, ?| або ?&, використовуйте jsonb_ops.
Індекс:
CREATE INDEX orders_total_idx
ON orders (((payload->>'total')::numeric));Запит із таким самим приведенням:
WHERE (payload->>'total')::numeric > 100Якщо в запиті використати інший вираз, наприклад payload->'total', цей індекс може не застосуватися.
Одночасне створення jsonb_ops, jsonb_path_ops і кількох expression index без аналізу запитів збільшує витрати на запис. Створюйте індекси для конкретних умов, які справді часто використовуються.
Для невеликої таблиці послідовне сканування може бути швидшим. Перевіряйте не сам факт наявності індексу, а фактичний план через EXPLAIN (ANALYZE, BUFFERS).
Для пошуку всередині jsonb найчастіше використовують GIN.
jsonb_ops підтримує більше операторів, зокрема ?, ?| і ?&.
jsonb_path_ops орієнтований на @> та може бути компактнішим для containment-запитів.
Для одного часто використовуваного ключа зручним є expression index.
Для числових значень тип потрібно явно привести і в індексі, і в запиті.
Вибір індексу потрібно перевіряти через EXPLAIN (ANALYZE, BUFFERS).
Не створюйте кілька взаємозамінних індексів без аналізу реальних запитів.