PostgreSQL 12.15 query performance problem

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.sql
--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.