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

On 9/9/16, Xtra Coder <xtracoder@gmail.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]https://www.postgresql.org/docs/current/static/xaggr.html.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thanks for analysis. So, it was my mistake to copy/paste usage of '
jsonb_object_agg()' from another use-case expecting better performance when
used in non-agg context. Yes, jsonb_build_object() is more appropriate in
this case - however it appeared in 9.5 and I was not aware about it,
therefore was using obsolete approach.