Порівняння продуктивності запитів у PostgreSQL: JSONB проти Join запитів

При роботі з PostgreSQL часто виникають ситуації, коли потрібно обирати між різними патернами запитів для досягнення оптимальної продуктивності. Нещодавно я досліджував різницю в продуктивності між фільтрацією на основі JSONB і традиційними запитами з використанням JOIN. Ось підсумок отриманих висновків і порад, які допоможуть вам приймати обґрунтовані рішення при проектуванні бази даних і оптимізації запитів.

Запити

1. Запит з фільтрацією за допомогою JSONB

Цей запит використовує типи даних JSONB для фільтрації транзакцій:

EXPLAIN ANALYZE   
SELECT *   
FROM transactions   
WHERE tags @> '{"code": ["A87"]}'   
 OR tags @> '{"code": ["A88"]}';

2.

Запит на основі JOIN

Цей запит об'єднує таблицю transactions з реляційною таблицею tags для фільтрації:

EXPLAIN ANALYZE   
SELECT *  
FROM transactions t  
JOIN tags tg   
 ON t.tags_id = tg.id  
WHERE tg.code @> ARRAY['A87']   
 OR tg.code @> ARRAY['A88'];

Структури таблиць

Таблиця Transactions (JSONB)

Column | Type | Collation | Nullable | Default   
-------------------+-----------------------------+-----------+----------+------------------------------------------  
 id | integer | | not null | nextval('transactions_id_seq'::regclass)  
 transactionnumber | character varying(50) | | not null |   
 transaction_date | timestamp without time zone | | not null | now()  
 amount | numeric(10,2) | | not null |   
 status | character varying(10) | | not null |   
 tags | jsonb | | |   
Indexes:  
 "transactions_pkey" PRIMARY KEY, btree (id)  
 "idx_tags" gin (tags)  
 "idx_tags_code" gin ((tags -> 'code'::text))  
 "idx_transactionnumber" UNIQUE, btree (transactionnumber)  
 "transactions_transactionnumber_key" UNIQUE CONSTRAINT, btree (transactionnumber)  
Check constraints:  
 "transactions_status_check" CHECK (status::text = ANY (ARRAY['ACCEPTED'::character varying, 'REJECTED'::character varying, 'PENDING'::character varying]::text[]))

Таблиця Transactions (Join-Based)

Column | Type | Collation | Nullable | Default   
-------------------+-----------------------------+-----------+----------+------------------------------------------  
 id | integer | | not null | nextval('transactions_id_seq'::regclass)  
 transactionnumber | character varying(50) | | not null |   
 tags_id | integer | | |   
 transaction_date | timestamp without time zone | | not null | now()  
 amount | numeric(10,2) | | not null |   
 status | character varying(10) | | not null |   
Indexes:  
 "transactions_pkey" PRIMARY KEY, btree (id)  
 "idx_tags_id" btree (tags_id)  
 "transactions_transactionnumber_key" UNIQUE CONSTRAINT, btree (transactionnumber)  
Check constraints:  
 "transactions_status_check" CHECK (status::text = ANY (ARRAY['ACCEPTED'::character varying, 'REJECTED'::character varying, 'PENDING'::character varying]::text[]))  
Foreign-key constraints:  
 "transactions_tags_id_fkey" FOREIGN KEY (tags_id) REFERENCES tags(id)

Індекси для таблиць

Індекси для таблиці Transactions (JSONB)

SELECT   
 indexname,   
 indexdef   
FROM   
 pg_indexes   
WHERE   
 tablename = 'transactions';

Результат:

indexname | indexdef   
------------------------------------+---------------------------------------------------------------------------------------------------------------  
 transactions_pkey | CREATE UNIQUE INDEX transactions_pkey ON public.transactions USING btree (id)  
 transactions_transactionnumber_key | CREATE UNIQUE INDEX transactions_transactionnumber_key ON public.transactions USING btree (transactionnumber)  
 idx_tags | CREATE INDEX idx_tags ON public.transactions USING gin (tags)  
 idx_transactionnumber | CREATE UNIQUE INDEX idx_transactionnumber ON public.transactions USING btree (transactionnumber)  
 idx_tags_code | CREATE INDEX idx_tags_code ON public.transactions USING gin (((tags -> 'code'::text)))

Індекси для таблиці Tags

SELECT   
 indexname,   
 indexdef   
FROM   
 pg_indexes   
WHERE   
 tablename = 'tags';

Результат:

indexname | indexdef   
--------------------+----------------------------------------------------------------------  
 tags_pkey | CREATE UNIQUE INDEX tags_pkey ON public.tags USING btree (id)  
 idx_tags_code | CREATE INDEX idx_tags_code ON public.tags USING gin (code)  
 idx_tags_id_values | CREATE INDEX idx_tags_id_values ON public.tags USING gin (id_values)

Індекси для таблиці Transactions (Join-Based)

SELECT   
 indexname,   
 indexdef   
FROM   
 pg_indexes   
WHERE   
 tablename = 'transactions';

Результат:

indexname | indexdef
------------------------------------+---------------------------------------------------------------------------------------------------------------  
 transactions_pkey | CREATE UNIQUE INDEX transactions_pkey ON public.transactions USING btree (id)  
 transactions_transactionnumber_key | CREATE UNIQUE INDEX transactions_transactionnumber_key ON public.transactions USING btree (transactionnumber)  
 idx_tags_id | CREATE INDEX idx_tags_id ON public.transactions USING btree (tags_id)

Результати

План запиту для JSONB

Bitmap Heap Scan on transactions (cost=52.12..1017.69 rows=1505 width=116) (actual time=2.942..4.484 rows=1012 loops=1)  
 Recheck Cond: ((tags @> '{"code": ["A87"]}'::jsonb) OR (tags @> '{"code": ["A88"]}'::jsonb))  
 Heap Blocks: exact=628  
 -> BitmapOr (cost=52.12..52.12 rows=1515 width=0) (actual time=2.813..2.814 rows=0 loops=1)  
 -> Bitmap Index Scan on idx_tags (cost=0.00..23.79 rows=505 width=0) (actual time=2.085..2.085 rows=536 loops=1)  
 Index Cond: (tags @> '{"code": ["A87"]}'::jsonb)  
 -> Bitmap Index Scan on idx_tags (cost=0.00..27.58 rows=1010 width=0) (actual time=0.727..0.727 rows=476 loops=1)  
 Index Cond: (tags @> '{"code": ["A88"]}'::jsonb)  
Planning Time: 0.377 ms  
Execution Time: 4.671 ms

План запиту для Join

Hash Join (cost=70.54..1203.98 rows=950 width=140) (actual time=0.516..34.016 rows=919 loops=1)  
 Hash Cond: (t.tags_id = tg.id)  
 -> Seq Scan on transactions t (cost=0.00..1002.00 rows=50000 width=42) (actual time=0.252..22.501 rows=50000 loops=1)  
 -> Hash (cost=69.83..69.83 rows=57 width=98) (actual time=0.246..0.252 rows=57 loops=1)  
 -> Bitmap Heap Scan on tags tg (cost=16.46..69.83 rows=57 width=98) (actual time=0.057..0.217 rows=57 loops=1)  
 Recheck Cond: ((code @> '{A87}'::text[]) OR (code @> '{A88}'::text[]))  
 Heap Blocks: exact=33  
Planning Time: 0.664 ms  
Execution Time: 34.216 ms

Основні висновки

1.

Час виконання

• JSONB запит: 4.671 мс
• Join запит: 34.216 мс

JSONB запит значно перевищив за швидкістю виконання Join запит, завершившись майже в 7 разів швидше.

2. Вузькі місця

• Join запит страждає від послідовного сканування таблиці transactions, що обробляє 50,000 рядків. Це забирає більшу частину часу виконання запиту.
• JSONB запит ефективно використовує Bitmap Index Scan на стовпці tags та уникає сканування непотрібних рядків.

3. Оброблені рядки

• JSONB запит: Обробляє 1,012 рядків.
• Join запит: Обробляє 919 рядків.

Обидва запити повертають подібні результати, але JSONB запит робить це з набагато меншою витратою ресурсів.

Рекомендації

Використовуйте JSONB запити, коли:

1. Продуктивність має критичне значення: JSONB запит використовує ефективні індекси, що робить його ідеальним для швидких запитів.
2. Дані мають напівструктурований вигляд: JSONB — гнучкий вибір для зберігання та запитів до даних з різними або змінними схемами.
3. Індексування доступне: Переконайтесь, що створено GIN індекс на стовпці JSONB для досягнення оптимальної продуктивності:

Оптимізація JSONB запитів:

CREATE INDEX idx_tags ON transactions USING gin(tags);

Якщо ви часто запитуєте конкретний ключ, наприклад, code, в полі JSONB, можна створити більш цільовий індекс:

CREATE INDEX idx_tags_code ON transactions USING gin((tags->'code'));

Це створить GIN індекс саме для ключа code в стовпці tags типу JSONB, покращуючи продуктивність запитів при фільтрації за цим ключем.

Використовуйте Join запити, коли:

1. Цілісність даних є критичною: Якщо важливо підтримувати відносини між таблицями, Join запит буде більш доречним.
2. Дані повністю нормалізовані: Коли дані відповідають строгим принципам реляційного проектування, Join запити можуть забезпечити чіткість і зручність обслуговування.

Оптимізація Join запитів:

Додайте індекс на transactions.tags_id, щоб уникнути послідовного сканування:

CREATE INDEX idx_transactions_tags_id ON transactions(tags_id);

Регулярно запускайте ANALYZE, щоб оновлювати статистику та забезпечити оптимальні плани виконання запитів.

Висновок

У цьому порівнянні JSONB запит виглядає явним лідером з точки зору продуктивності завдяки ефективному використанню індексів та зменшеному навантаженню на обробку. Однак вибір запиту завжди має залежати від конкретних вимог вашого застосунку, включаючи проектування схеми, розмір даних та зручність обслуговування.

Розуміючи компроміси між JSONB та Join запитами, ви зможете проектувати вашу базу даних PostgreSQL так, щоб ефективно балансувати між продуктивністю та цілісністю даних.

Перекладено з: Comparing Query Performance in PostgreSQL: JSONB vs Join Queries