Обсуждение: 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
select s.id, s.name, j.* from shops s cross join jsonb_each(s.opening_times) as j(day, value)
) t
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 (
) t
$$ language sql
$$ 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
Filter: (id = 1307)
Rows Removed by Filter: 10540
Planning Time: 0.082 ms
Execution Time: 16.584 ms
Whereas plan for statement (B)
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
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