Postgres Pro
Downloads
Home   >   mailing lists

Thread: plan for function returning table combined with condition

plan for function returning table combined with condition

From
Thierry Henrio
Date:
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

Re: plan for function returning table combined with condition

From
Tom Lane
Date:
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

Re: plan for function returning table combined with condition

From
Thierry Henrio
Date:


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