jsonb_agg performance

Поиск
Список
Период
Сортировка
От jfleming@kispring.com
Тема jsonb_agg performance
Дата
Msg-id e50a3f774da9d82b44c1c508a790fd1ad42e66bd@6nqp-thgl.accessdomain.com
обсуждение исходный текст
Ответы Re: jsonb_agg performance  (Andrew Dunstan <andrew@dunslane.net>)
Список pgsql-performance
The jsonb_agg function seems to have significantly worse performance than its json_agg counterpart:

=> explain analyze select pa.product_id, jsonb_agg(attributes) from product_attributes2 pa group by pa.product_id;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1127.54..1231.62 rows=3046 width=380) (actual time=28.632..241.647 rows=3046 loops=1)
   Group Key: product_id
   ->  Sort  (cost=1127.54..1149.54 rows=8800 width=380) (actual time=28.526..32.826 rows=8800 loops=1)
         Sort Key: product_id
         Sort Method: external sort  Disk: 3360kB
         ->  Seq Scan on product_attributes2 pa  (cost=0.00..551.00 rows=8800 width=380) (actual time=0.010..7.231 rows=8800 loops=1)
 Planning time: 0.376 ms
 Execution time: 242.963 ms
(8 rows)

=> explain analyze select pa.product_id, json_agg(attributes) from product_attributes3 pa group by pa.product_id;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1136.54..1240.62 rows=3046 width=387) (actual time=17.731..30.126 rows=3046 loops=1)
   Group Key: product_id
   ->  Sort  (cost=1136.54..1158.54 rows=8800 width=387) (actual time=17.707..20.705 rows=8800 loops=1)
         Sort Key: product_id
         Sort Method: external sort  Disk: 3416kB
         ->  Seq Scan on product_attributes3 pa  (cost=0.00..560.00 rows=8800 width=387) (actual time=0.006..5.568 rows=8800 loops=1)
 Planning time: 0.181 ms
 Execution time: 31.276 ms
(8 rows)

The only difference between the two tables is the type of the attributes column (jsonb vs json).  Each table contains the same 8800 rows.  Even running json_agg on the jsonb column seems to be faster:

=> explain analyze select pa.product_id, json_agg(attributes) from product_attributes2 pa group by pa.product_id;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1127.54..1231.62 rows=3046 width=380) (actual time=30.626..62.943 rows=3046 loops=1)
   Group Key: product_id
   ->  Sort  (cost=1127.54..1149.54 rows=8800 width=380) (actual time=30.590..34.157 rows=8800 loops=1)
         Sort Key: product_id
         Sort Method: external sort  Disk: 3360kB
         ->  Seq Scan on product_attributes2 pa  (cost=000..551.00 rows=8800 width=380) (actual time=0.014..7.388 rows=8800 loops=1)
 Planning time: 0.142 ms
 Execution time: 64.504 ms
(8 rows)

Is it expected that jsonb_agg performance would be that much worse than json_agg?

В списке pgsql-performance по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: Hash join gets slower as work_mem increases?
Следующее
От: Andrew Dunstan
Дата:
Сообщение: Re: jsonb_agg performance