From: Ринат Мухтаров <r.mukhtarov@rabota.ru>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: PostgreSQL 12.15 query performance problem
Date: Tue, 12 Sep 2023 21:25:57 +0000
Message-ID: <64f5b8ebc96d48f49da8a8f4a3cf7b40@rabota.ru> (raw)
Hello!
select version();
--PostgreSQL 12.15 (Ubuntu 12.15-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
create schema if not exists test;
drop table if exists test.sample_data;
create table test.sample_data (id, data) as (
values
(9::int, '{"click_id": "8dQZ1Q61daZvNyO", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'::jsonb),
(1, '{"click_id": "8dQZ1Q2eA9Z5n75", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(2, '{"click_id": "1", "utm_source": "cityads"}'),
(10, '{"click_id": "8dQZ1Q7WFYZiASC", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(11, '{"click_id": "8dQZ1Q7Y3yZvIpx", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(12, '{"click_id": "8dQZ1Q8jFjZrppg", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(13, '{"click_id": "8dQZ1Q8IW7ZA7WY", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(3, '{"click_id": "8dQZ1Q2WXjZmTBR", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(4, '{"click_id": "8dQZ1Q2XkuZe6hw", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(5, '{"click_id": "8dQZ1Q2XtaZA88c", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(7, '{"click_id": "8dQZ1Q2XW8Zivqs", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(6, '{"click_id": "8dQZ1Q2XAWZn0x3", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}'),
(8, '{"7": "777", "arr": [9, 8, 3, 4], "click_id": "8dQZ1Q61arZmTAm", "utm_medium": "cpa", "utm_source": "cityads", "utm_campaign": "Bg0m12"}')
);
--next create function jsonb_unnest_recursive(jsonb) from
--https://github.com/rin-nas/postgresql-patterns-library/blob/master/functions/jsonb_unnest_recursive....
--or see attachement
--Q1
select j.*
from test.sample_data as s
cross join jsonb_unnest_recursive(s.data) as j;
--55 rows retrieved in 3 s 914 ms
--Q2
select j.*
from test.sample_data as s
cross join lateral (
-- code body from jsonb_unnest_recursive(jsonb)
with recursive r (path, value, member_of) as
(
select
array[k.key],
v.value,
t.type
from jsonb_typeof(s.data) as t(type)
left join jsonb_each(case t.type when 'object' then s.data end) as o(obj_key, obj_value) on true
left join jsonb_array_elements(case t.type when 'array' then s.data end) with ordinality as a(arr_value, arr_key) on true
cross join coalesce(o.obj_key, (a.arr_key - 1)::text) as k(key)
cross join coalesce(o.obj_value, a.arr_value) as v(value)
where t.type in ('object', 'array')
and k.key is not null
union all
select
array_append(r.path, k.key),
v.value,
t.type
from r
cross join jsonb_typeof(r.value) as t(type)
left join jsonb_each(case t.type when 'object' then r.value end) as o(obj_key, obj_value) on true
left join jsonb_array_elements(case t.type when 'array' then r.value end) with ordinality as a(arr_value, arr_key) on true
cross join coalesce(o.obj_key, (a.arr_key - 1)::text) as k(key)
cross join coalesce(o.obj_value, a.arr_value) as v(value)
where t.type in ('object', 'array')
and k.key is not null
)
select r.*
from r
where jsonb_typeof(r.value) not in ('object', 'array')
) as j;
-- 55 rows retrieved in 446 ms
Why Q2 much faster than Q1?
---
Best regards,
Rinat Mukhtarov
УВЕДОМЛЕНИЕ О КОНФИДЕНЦИАЛЬНОСТИ: Это электронное сообщение и любые документы, приложенные к нему, содержат конфиденциальную информацию. Настоящим уведомляем Вас о том, что если это сообщение не предназначено Вам, использование, копирование, распространение информации, содержащейся в настоящем сообщении, а также осуществление любых действий на основе этой информации, строго запрещено. Если Вы получили это сообщение по ошибке, пожалуйста, сообщите об этом отправителю по электронной почте и удалите это сообщение. CONFIDENTIALITY NOTICE: This email and any files attached to it are confidential. If you are not the intended recipient you are notified that using, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error please notify the sender and delete this email.
Attachments:
jsonb_unnest_recursive.sql (application/sql, 2.3K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: r.mukhtarov@rabota.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: PostgreSQL 12.15 query performance problem
In-Reply-To: <64f5b8ebc96d48f49da8a8f4a3cf7b40@rabota.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox