Комментарии 26
Всё-таки интересен план проведения эксперимента. Возможно ли, что PostgreSQL кэширует результаты предыдущих запросов? На обе схемы каждый раз выполнялся один и тот же запрос, или разные? Данные в обеих таблицах одинаковые или разные? Может, распределение данных оказалось неудачным
Давайте индексы добавим. И данных побольше.
В моем случае "побольше данных" это был бы тест ради теста, не коррелирует с нашей бизнес задачей. А про индексы снизу написала @Insolita- будет еще лучше.
Да, если индексы добавить - можно и еще большей эффективности добиться, я тоже около года назад проверяла https://donnainsolita.medium.com/postgres-jsonb-vs-property-value-table-5f5b9f183a9d
Разница во времени — секунда, для нашего случая это не критично.
Секунда на самом деле это колоссально много, пока условный "воркер" крутит эту секунду запрос, остальные запросы стакаются. Как итог - вермя ожидания на выполнение будет расти коскадно
Павел, секундочку, а какой план запроса в случае реляционной схемы? Ответы на вопросы производительности стоит начинать искать с плана и только с него.
Спасибо за комментарий, хочу уточнить, в моем эксперименте я проверял работу по умолчанию. Задачи оптимизировать запросы не было, потому что они не являются сейчас бутылочным горлышком.
Вот полный EXPLAIN реляционного запроса:
Finalize Aggregate (cost=45949.77..45949.78 rows=1 width=8) (actual time=191.621..191.621 rows=1 loops=1) |
-> Gather (cost=45949.56..45949.77 rows=2 width=8) (actual time=191.490..196.400 rows=3 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Partial Aggregate (cost=44949.56..44949.57 rows=1 width=8) (actual time=171.319..171.319 rows=1 loops=3) |
-> Nested Loop (cost=0.42..44949.56 rows=1 width=0) (actual time=171.314..171.314 rows=0 loops=3) |
-> Parallel Seq Scan on history_b_results hbr (cost=0.00..43934.00 rows=124 width=4) (actual time=171.313..171.313 rows=0 loops=3)|
Filter: (expiration_date > (CURRENT_DATE - 10)) |
Rows Removed by Filter: 1000000 |
-> Index Scan using history_b_pkey on history_b hb (cost=0.42..8.19 rows=1 width=4) (never executed) |
Index Cond: (id = hbr.history_b_id) |
Filter: ((customer_uuid)::text = '7e240a94-261b-4eeb-b0d3-8c6de47d9557'::text) |
Planning Time: 0.148 ms |
Execution Time: 196.466 ms |Тогда не понятно что вы хотели оценить. В случае с одной (индексированной) таблицей это явно оверхед на работу с jsonb, связанный также с полным извлечением данных jsonb. В случае с двумя таблицами всё упирается в просмотр бОльшей таблицы сканом. У вас даже время выполнения сопоставимое получилось для всех записей и по одному клиенту. Без индекса такой подзапрос не делают же. Без индекса просто ожидаемый результат.
Вывод с выигрышность-невыигрышностью мне кажется не верный.
Сам делал денормализацию, в том числе с коллекциями в оракле. Очень от сценария использования зависит. Если обработка в бд, то лучше уж ухитриться кластеризовать данные.
Я отвечал на такой вопрос - если мы данные храним эстетически красиво и компактно (для меня это вариант с JSONB), будет ли это драматически влиять на производительность? Ответ оказался - нет, не будет.
Чтобы хоть как-то сравнить эффективность запросов, надо смотреть их планы при решении одной и той же задачи - иначе вот так и может получиться, что там спрятался Seq Scan, и сравнение JSON-варианта идет с не самой эффективной альтернативой. И на ее фоне результаты могут выглядеть приемлемо, хотя "на самом деле" могут проигрывать кратно.
Помимо отсутствия индекса history_b_results(history_b_id), не понятно для чего разбивать таблицу на две - можно в history_b_results добавить customer_uuid и не делать JOIN. Но возможно, что в реальности есть какие-то доп. детали, которые опущены в примере.
WTF moments:
1) зачем 100500 раз вызывать TO_DATE() для данных вместо одного вызова для параметра (CURRENT_DATE - 10) ?
2) если требуется дописывать значения в history_b , то проблемой будет не сколько формат поля results, а сколько конфликты при update => нужно ещё поле version (хотя на время select это не должно драматически повлиять)
P.S. 3) если нужен только Array - мы уверены, что хотим JSONB не из желания поэкспериментировать с JSONB?
Не могу сказать что тест аккуратный: JSON payload мелкий.
Увеличьте размер JSON payload и посмотрите на реакцию DBMS на парсинг более крупных JSON полей.
Результаты логичны:
- в первом случае (Выборка по всем данным) вы парсите JSON и за это платите временем.
- во втором случае (Выборка с фильтром по customer_uuid) вы добавляете поиск по GUID, что сужает поиск, и начитываете из одной таблицы, а в нормальной форме читаете из 2х таблиц с JOIN. Небольшая задержка логична.
Повозившись с XML и JSON в базе данных с неконтролиуемой структурой XML/JSON, я вернулся к классической реляционной модели т.к. DBMS лучше заточены на них.
Тынц для глубокого погружения в тему от корифея.
Совсем недавно вышло видео о JSON в Postgres, так хорошо рассказаны текущие проблемы и как и решили или собераются решить.
а причём тут вообще jsonb? я не вижу, чтобы вы в запросах с ним работали, хотя изначально про это речь шла:
Я сказал, что скорее всего не будет, ведь JSONB уже давно на рынке и по полям в объекте, если надо, можно создать индекс.
Я разворачиваю jsonb в рекордсет при запросе jsonb_to_recordset, согласно документации (https://www.postgresql.org/docs/9.4/functions-json.html) это JSON Processing Function.
Храним данные в JSONB, как это влияет на скорость запросов?