plan for function returning table combined with condition

From: Thierry Henrio <thierry(dot)henrio(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: plan for function returning table combined with condition
Date: 2022-07-20 17:51:55
Message-ID: CAMPYKo35ixNo2rSPZTS9Y6DF+dSDOD1eVAJEKfnjG2yOGEcj=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2022-07-20 18:16:49 Re: Batch process
Previous Message Adrian Klaver 2022-07-20 17:39:01 Re: Batch process