plan for function returning table combined with condition
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
Thierry Henrio <thierry.henrio@gmail.com> writes:
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
You want this SQL function to be inlined, but it isn't being.
I think the reason is that (by default) it's VOLATILE, and
inline_set_returning_function doesn't like that:
* Forget it if the function is not SQL-language or has other showstopper
* properties. In particular it mustn't be declared STRICT, since we
* couldn't enforce that. It also mustn't be VOLATILE, because that is
* supposed to cause it to be executed with its own snapshot, rather than
* sharing the snapshot of the calling query. We also disallow returning
* SETOF VOID, because inlining would result in exposing the actual result
* of the function's last SELECT, which should not happen in that case.
So try adding STABLE to the function definition.
(This could be better documented, perhaps.)
regards, tom lane
On Wed, Jul 20, 2022 at 8:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
You want this SQL function to be inlined, but it isn't being.
I think the reason is that (by default) it's VOLATILE, and
inline_set_returning_function doesn't like that:* Forget it if the function is not SQL-language or has other
showstopper
* properties. In particular it mustn't be declared STRICT, since we
* couldn't enforce that. It also mustn't be VOLATILE, because that is
* supposed to cause it to be executed with its own snapshot, rather
than
* sharing the snapshot of the calling query. We also disallow
returning
* SETOF VOID, because inlining would result in exposing the actual
result
* of the function's last SELECT, which should not happen in that case.So try adding STABLE to the function definition.
Indeed, when I add STABLE to function, then planner uses index (same plan
as (B) in original post).
Thanks Tom.
, Thierry