Hello,
I have a table with a jsonb row, opening_times, that I need to expand for
later processing. jsonb is an object, like so {"1": [["06:00:00",
"23:59:59"]], ...}.
select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t
I made a function out of this sql:
create or replace function expand_shop_opening_times() returns table(id
int, name text, day int, startt time, endt time)
as $$
select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t
$$ language sql
So I can use it like so (A):
select id, name from expand_shop_opening_times() where id=1307;
The plan for statement (A) is:
Function Scan on expand_shop_opening_times (cost=0.25..12.75 rows=5
width=36) (actual time=15.950..16.418 rows=7 loops=1)
Filter: (id = 1307)
Rows Removed by Filter: 10540
Planning Time: 0.082 ms
Execution Time: 16.584 ms
Whereas plan for statement (B)
select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t
where id=1307
I have:
Nested Loop (cost=0.28..12.80 rows=100 width=41) (actual
time=0.030..0.038 rows=7 loops=1)
-> Index Scan using shops_pkey on shops s (cost=0.28..8.29 rows=1
width=341) (actual time=0.012..0.014 rows=1 loops=1)
Index Cond: (id = 1307)
-> Function Scan on jsonb_each j (cost=0.00..1.00 rows=100 width=64)
(actual time=0.008..0.009 rows=7 loops=1)
Planning Time: 0.116 ms
Execution Time: 0.062 ms
Is there are any improvement I can make to my function definition so that
planner can find a better plan for the (A) statement?
Cheers, Thierry
