Re: Function's execute overhead reducing

On 11/16/19 11:23 PM, Игорь Выскорко wrote:
> Hi all!
>
> The best way to describe my question is to show the code as first:
>
> create table tst(
> id int primary key,
> j1 jsonb,
> j2 jsonb
> );
>
> insert into tst
> select
> ser,
> jsonb_build_object(
> floor(random() * 10 + 1), floor(random() * 1000 + 1),
> floor(random() * 10 + 1), floor(random() * 1000 + 1),
> floor(random() * 10 + 1), floor(random() * 1000 + 1)
> ),
> jsonb_build_object(
> floor(random() * 10 + 1), floor(random() * 1000 + 1),
> floor(random() * 10 + 1), floor(random() * 1000 + 1),
> floor(random() * 10 + 1), floor(random() * 1000 + 1)
> )
> from generate_series(1, 500000) ser;
>
> analyze tst;
>
>
> -- original func is a bit complicated. But it doesn't matter here
> create or replace function tst_func(a jsonb, b jsonb) returns bigint
> stable
> language sql
> as $$
> select
> sum(
> ((_a.value::text)::int - (coalesce(b->>_a.key, '0'))::int)::int
> )
> from
> jsonb_each(a) _a
> $$;
>
> -- get plain data
> explain analyze select
> id,
> j1,
> j2
> from
> tst;
>
> -- use subquery (the same code as in function)
> explain analyze select
> id,
> j1,
> j2,
> (
> select
> sum(
> ((_a.value::text)::int - (coalesce(j2->>_a.key, '0'))::int)::int
> )
> from
> jsonb_each(j1) _a
> )
> from
> tst;
>
> -- use function
> explain analyze select
> id,
> j1,
> j2,
> tst_func(j1, j2)
> from
> tst;
>
> select version();
>
>
> And after run it I got following results:
>
> CREATE TABLE
> INSERT 0 500000
> ANALYZE
> CREATE FUNCTION
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Seq Scan on tst (cost=0.00..13558.00 rows=500000 width=108) (actual time=0.009..40.348 rows=500000 loops=1)
> Planning time: 0.189 ms
> Execution time: 56.356 ms
> (3 rows)
>
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tst (cost=0.00..1644808.00 rows=500000 width=116) (actual time=0.021..1966.190 rows=500000 loops=1)
> SubPlan 1
> -> Aggregate (cost=3.25..3.26 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=500000)
> -> Function Scan on jsonb_each _a (cost=0.00..1.00 rows=100 width=64) (actual time=0.002..0.002 rows=3 loops=500000)
> Planning time: 0.072 ms
> Execution time: 1982.192 ms
> (6 rows)
>
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Seq Scan on tst (cost=0.00..138558.00 rows=500000 width=116) (actual time=0.072..5308.897 rows=500000 loops=1)
> Planning time: 0.067 ms
> Execution time: 5328.196 ms
> (3 rows)
>
> version
> -------------------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.13 on x86_64-pc-linux-gnu (Ubuntu 9.6.13-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
> (1 row)
>
>
> As you see, subquery version needs 2 seconds when func version needs more than 5. And it's sad to see. I love functions and actually this func (its production version) is widely used across our project.
> I tried to alter function as immutable and even parallel safe but it doesn't help.
> I understand that functions execution is not free, but why so much?
> So, my question is: is there any way to make this function works faster? Something similar to prepared statement or maybe rules (create rule) or whatever else is available.

My suspicion is that the query in the function is hidden from the
planner and so it ends up running two separate SELECT queries without
reference to each other. A quick test and possible solution:

My results for the above on old machine.:

Straight query:
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tst (cost=0.00..1644807.00 rows=500000 width=116) (actual
time=0.033..2808.596 rows=500000 loops=1)
SubPlan 1
-> Aggregate (cost=3.25..3.26 rows=1 width=8) (actual
time=0.005..0.005 rows=1 loops=500000)
-> Function Scan on jsonb_each _a (cost=0.00..1.00
rows=100 width=64) (actual time=0.003..0.003 rows=3 loops=500000)
Planning Time: 16.162 ms
Execution Time: 2846.815 ms

Function in query
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------
Seq Scan on tst (cost=0.00..138557.00 rows=500000 width=116) (actual
time=0.119..7048.285 rows=500000 loops=1)
Planning Time: 0.105 ms
Execution Time: 7098.057 ms

I changed the function to:

CREATE OR REPLACE FUNCTION public.tst_func(i integer, a jsonb, b jsonb)
RETURNS TABLE(id integer, val bigint)
LANGUAGE sql
STABLE
AS $function$
select
i,
sum(
((_a.value::text)::int - (coalesce(b->>_a.key, '0'))::int)::int
)
from
jsonb_each(a) _a
$function$

Using 'table' function:

test=# explain analyze select
tst.id,
j1,
j2
from
tst
join
tst_func(id, j1, j2) as f
on
tst.id = f.id;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..531057.00 rows=1 width=108) (actual
time=0.042..2002.258 rows=500000 loops=1)
-> Seq Scan on tst (cost=0.00..13557.00 rows=500000 width=108)
(actual time=0.014..70.936 rows=500000 loops=1)
-> Subquery Scan on f (cost=1.00..1.02 rows=1 width=4) (actual
time=0.003..0.003 rows=1 loops=500000)
Filter: (tst.id = f.id)
-> Aggregate (cost=1.00..1.01 rows=1 width=12) (actual
time=0.003..0.003 rows=1 loops=500000)
-> Function Scan on jsonb_each _a (cost=0.00..1.00
rows=100 width=0) (actual time=0.003..0.003 rows=3 loops=500000)
Planning Time: 0.297 ms
Execution Time: 2037.601 ms

>
> PS current situation maybe solved by denormalization with precalculations of function and storing results along with data, but it's not the way i would like to use, because it leads to more issues to solve (invalidation, for instance)
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com