Re: Performance issue: jsonb_object_agg() is twice slower than to_jsonb()

On 9/9/16, Xtra Coder <xtracoder(at)gmail(dot)com> wrote:
> Hello,
>
> while testing a best-performance implementation of my code I've noticed
> very strange performance issue - jsonb_object_agg() is twice slower
> than to_jsonb(select...).
>
> Here are the results:
>
> "PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit"
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> jsonb_object_agg -> 5.9 sec
> to_jsonb -> 3.7 sec
>
>
> PostgreSQL 9.6rc1, compiled by Visual C++ build 1800, 64-bit
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> jsonb_object_agg -> 6.0 sec
> to_jsonb -> 3.2 sec
>
>
> Here is the code i've used to test performance. To my mind
> "jsonb_object_agg' should be twice faster because it does not introduce
> temp rowsets to be converted to jsonb. However actual result is the
> opposite.
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> DO LANGUAGE plpgsql $$
> DECLARE
> jsonb_result jsonb;
> count int;
> BEGIN
> count = 0;
> LOOP
> -- Impl #1
> -- jsonb_result = jsonb_object_agg('created', now() );
>
> -- Impl #2
> select to_jsonb(t) from (select now() as "created") t
> into jsonb_result;
>
> count = count + 1;
> EXIT WHEN count > 500000;
> END LOOP;
>
> raise notice 'result = %', jsonb_result;
> END; $$
>

Hello!

It is not a bug.

Functions jsonb_object_agg and to_jsonb are not similar even if your
code behavior uses them to get the same results.
At first to_jsonb is intended to work with a single row whereas
jsonb_object_agg is intended to work with multiple rows (aggregate
multiple rows). For more information see [1].

Since jsonb_object_agg is more complex it has a penalty for preparing
and finalizing, and for a single row that penalty is comparable to a
net work. At least it calls two function: jsonb_object_agg_transfn to
add the first (and in fact the single) row and
jsonb_object_agg_finalfn to get aggregated result.

The function jsonb_object_agg allows you to get result you can't get
by to_jsonb function:

postgres=# select jsonb_object_agg('key_' || x::text, x) from
generate_series(1,4) as x;
jsonb_object_agg
--------------------------------------------------
{"key_1": 1, "key_2": 2, "key_3": 3, "key_4": 4}
(1 row)

Also note that object_agg_finalfn call copies "intermediate" result
because it can be called several times (and for a single row it is
also wasting time and space) because jsonb_object_agg can be used as a
window function (note that in such case jsonb_object_agg_finalfn is
called once _per_ _row_):

postgres=# select x, jsonb_object_agg('key_' || x::text, x) over(ORDER
BY x) from generate_series(1,4) as x;
x | jsonb_object_agg
---+--------------------------------------------------
1 | {"key_1": 1}
2 | {"key_1": 1, "key_2": 2}
3 | {"key_1": 1, "key_2": 2, "key_3": 3}
4 | {"key_1": 1, "key_2": 2, "key_3": 3, "key_4": 4}
(4 rows)

If you really have to use possibilities of constructing jsonb object
but for a single row, try to use proper function:

-- Impl #3
jsonb_result = jsonb_build_object('created', now() ); -- 2.6sec
for me instead of 5.0 for to_json

So jsonb_object_agg runs longer just because it is more complex and if
you don't need possibilities the function gives you, it is wise to use
simpler functions like to_jsonb/jsonb_build_object.

[1] https://www.postgresql.org/docs/current/static/xaggr.html

--
Best regards,
Vitaly Burovoy