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

Поиск
Список
Период
Сортировка
От Xtra Coder
Тема Performance issue: jsonb_object_agg() is twice slower than to_jsonb()
Дата
Msg-id CAL2enjK1KXy54miYKt_Myg9NYo1Tg4e7WQaNsVu0ufqudpwn7Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Performance issue: jsonb_object_agg() is twice slower than to_jsonb()  (Vitaly Burovoy <vitaly.burovoy@gmail.com>)
Список pgsql-bugs
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; $$

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

Предыдущее
От: Andrew Gierth
Дата:
Сообщение: Re: Array dimension lost if dimension length is zero
Следующее
От: Shay Rojansky
Дата:
Сообщение: Re: Array dimension lost if dimension length is zero