plan cache overhead on plpgsql expression

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

plan cache overhead on plpgsql expression

Pavel Stehule
Hi

when I do some profiling of plpgsql, usually I surprised how significant overhead has expression execution. Any calculations are very slow.

This is not typical example of plpgsql, but it shows cleanly where is a overhead

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare i bigint = 0;
begin
  while i < 100000000
  loop
    i := i + 1;
  end loop;
end;
$function$

Profile of development  version

  10,04%  plpgsql.so                          [.] exec_eval_simple_expr
   9,17%  postgres                            [.] AcquireExecutorLocks
   7,01%  postgres                            [.] ExecInterpExpr
   5,86%  postgres                            [.] OverrideSearchPathMatchesCurrent
   4,71%  postgres                            [.] GetCachedPlan
   4,14%  postgres                            [.] AcquirePlannerLocks
   3,72%  postgres                            [.] RevalidateCachedQuery
   3,56%  postgres                            [.] MemoryContextReset
   3,43%  plpgsql.so                          [.] plpgsql_param_eval_var
   3,33%  postgres                            [.] SPI_plan_get_cached_plan
   3,28%  plpgsql.so                          [.] exec_stmt
   3,18%  postgres                            [.] ReleaseCachedPlan
   2,92%  postgres                            [.] ResourceArrayRemove
   2,81%  plpgsql.so                          [.] exec_assign_value
   2,74%  plpgsql.so                          [.] exec_cast_value
   2,70%  plpgsql.so                          [.] exec_eval_expr
   1,96%  postgres                            [.] recomputeNamespacePath
   1,90%  plpgsql.so                          [.] exec_eval_boolean
   1,82%  plpgsql.so                          [.] exec_eval_cleanup
   1,72%  postgres                            [.] ScanQueryForLocks
   1,68%  postgres                            [.] CheckCachedPlan
   1,49%  postgres                            [.] ResourceArrayAdd
   1,48%  plpgsql.so                          [.] exec_assign_expr
   1,42%  postgres                            [.] ResourceOwnerForgetPlanCacheRef
   1,24%  plpgsql.so                          [.] exec_stmts
   1,23%  plpgsql.so                          [.] exec_stmt_while
   1,03%  plpgsql.so                          [.] assign_simple_var
   0,73%  postgres                            [.] int84lt
   0,62%  postgres                            [.] ResourceOwnerEnlargePlanCacheRefs
   0,54%  postgres                            [.] int84pl
   0,49%  plpgsql.so                          [.] setup_param_list
   0,45%  postgres                            [.] ResourceArrayEnlarge
   0,44%  postgres                            [.] choose_custom_plan
   0,39%  postgres                            [.] ResourceOwnerRememberPlanCacheRef
   0,30%  plpgsql.so                          [.] exec_stmt_assign
   0,26%  postgres                            [.] GetUserId
   0,22%  plpgsql.so                          [.] SPI_plan_get_cached_plan@plt

and profile of PostgreSQL 8.2

  13,63%  plpgsql.so                          [.] exec_eval_simple_expr
   9,72%  postgres                            [.] AllocSetAlloc
   7,84%  postgres                            [.] ExecMakeFunctionResultNoSets
   6,20%  plpgsql.so                          [.] exec_assign_value
   5,46%  postgres                            [.] AllocSetReset
   4,79%  postgres                            [.] ExecEvalParam
   4,53%  plpgsql.so                          [.] exec_eval_datum
   4,40%  postgres                            [.] MemoryContextAlloc
   3,51%  plpgsql.so                          [.] exec_stmt
   3,01%  plpgsql.so                          [.] exec_eval_expr
   2,76%  postgres                            [.] int84pl
   2,11%  plpgsql.so                          [.] exec_eval_cleanup
   1,77%  postgres                            [.] datumCopy
   1,76%  postgres                            [.] MemoryContextReset
   1,75%  libc-2.30.so                        [.] __sigsetjmp
   1,64%  postgres                            [.] int84lt
   1,47%  postgres                            [.] pfree
   1,43%  plpgsql.so                          [.] exec_simple_cast_value
   1,36%  plpgsql.so                          [.] MemoryContextReset@plt
   1,28%  plpgsql.so                          [.] exec_stmt_while
   1,25%  plpgsql.so                          [.] exec_assign_expr
   1,22%  postgres                            [.] check_stack_depth
   1,09%  plpgsql.so                          [.] exec_eval_boolean
   1,06%  postgres                            [.] AllocSetFree
   0,99%  plpgsql.so                          [.] free_var
   0,93%  plpgsql.so                          [.] exec_cast_value
   0,93%  plpgsql.so                          [.] exec_stmts
   0,78%  libc-2.30.so                        [.] __memmove_sse2_unaligned_erms
   0,72%  postgres                            [.] datumGetSize
   0,62%  postgres                            [.] Int64GetDatum
   0,51%  libc-2.30.so                        [.] __sigjmp_save
   0,49%  postgres                            [.] ExecEvalConst
   0,41%  plpgsql.so                          [.] exec_stmt_assign
   0,28%  postgres                            [.] SPI_pop
   0,26%  plpgsql.so                          [.] MemoryContextAlloc@plt
   0,25%  postgres                            [.] SPI_push
   0,25%  plpgsql.so                          [.] SPI_push@plt
   0,24%  plpgsql.so                          [.] __sigsetjmp@plt
   0,23%  plpgsql.so                          [.] SPI_pop@plt
   0,19%  libc-2.30.so                        [.] __memset_sse2_unaligned_erms
   0,14%  libc-2.30.so                        [.] memcpy@GLIBC_2.2.5
   0,13%  postgres                            [.] memcpy@plt

Is interesting so overhead of plan cache about 15%

The execution needs 32 sec on Postgres13 and 27sec on Postgres8.2

Regards

Pavel

Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


ne 16. 2. 2020 v 15:12 odesílatel Pavel Stehule <[hidden email]> napsal:
Hi

when I do some profiling of plpgsql, usually I surprised how significant overhead has expression execution. Any calculations are very slow.

This is not typical example of plpgsql, but it shows cleanly where is a overhead

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare i bigint = 0;
begin
  while i < 100000000
  loop
    i := i + 1;
  end loop;
end;
$function$


Is interesting so overhead of plan cache about 15%

The execution needs 32 sec on Postgres13 and 27sec on Postgres8.2

On same computer same example in Perl needs only 7 sec.

Regards

Pavel


Regards

Pavel

Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
In reply to this post by Pavel Stehule
Hi,

On Sun, Feb 16, 2020 at 11:13 PM Pavel Stehule <[hidden email]> wrote:

> when I do some profiling of plpgsql, usually I surprised how significant overhead has expression execution. Any calculations are very slow.
>
> This is not typical example of plpgsql, but it shows cleanly where is a overhead
>
> CREATE OR REPLACE FUNCTION public.foo()
>  RETURNS void
>  LANGUAGE plpgsql
>  IMMUTABLE
> AS $function$
> declare i bigint = 0;
> begin
>   while i < 100000000
>   loop
>     i := i + 1;
>   end loop;
> end;
> $function$
>
> Profile of development  version
>
>   10,04%  plpgsql.so                          [.] exec_eval_simple_expr
>    9,17%  postgres                            [.] AcquireExecutorLocks
>    7,01%  postgres                            [.] ExecInterpExpr
>    5,86%  postgres                            [.] OverrideSearchPathMatchesCurrent
>    4,71%  postgres                            [.] GetCachedPlan
>    4,14%  postgres                            [.] AcquirePlannerLocks
>    3,72%  postgres                            [.] RevalidateCachedQuery
>    3,56%  postgres                            [.] MemoryContextReset
>    3,43%  plpgsql.so                          [.] plpgsql_param_eval_var
I was thinking about this overhead many months back and had even
written a patch to avoid going to the planner for "simple"
expressions, which can be handled by the executor.  Here is what the
performance looks like:

HEAD:

latency: 31979.393 ms

    18.32%  postgres  postgres           [.] ExecInterpExpr
    11.37%  postgres  plpgsql.so         [.] exec_eval_expr
     8.58%  postgres  plpgsql.so         [.] plpgsql_param_eval_var
     8.31%  postgres  plpgsql.so         [.] exec_stmt
     6.44%  postgres  postgres           [.] GetCachedPlan
     5.47%  postgres  postgres           [.] AcquireExecutorLocks
     5.30%  postgres  postgres           [.] RevalidateCachedQuery
     4.79%  postgres  plpgsql.so         [.] exec_assign_value
     4.41%  postgres  postgres           [.] SPI_plan_get_cached_plan
     4.36%  postgres  postgres           [.] MemoryContextReset
     4.22%  postgres  postgres           [.] ReleaseCachedPlan
     4.03%  postgres  postgres           [.] OverrideSearchPathMatchesCurrent
     2.63%  postgres  plpgsql.so         [.] exec_assign_expr
     2.11%  postgres  postgres           [.] int84lt
     1.95%  postgres  postgres           [.] ResourceOwnerForgetPlanCacheRef
     1.71%  postgres  postgres           [.] int84pl
     1.57%  postgres  postgres           [.] ResourceOwnerRememberPlanCacheRef
     1.38%  postgres  postgres           [.] recomputeNamespacePath
     1.35%  postgres  postgres           [.] ScanQueryForLocks
     1.24%  postgres  plpgsql.so         [.] exec_cast_value
     0.38%  postgres  postgres           [.] ResourceOwnerEnlargePlanCacheRefs
     0.05%  postgres  [kernel.kallsyms]  [k] __do_softirq
     0.03%  postgres  postgres           [.] GetUserId

Patched:

latency: 21011.871 ms

    28.26%  postgres  postgres           [.] ExecInterpExpr
    12.26%  postgres  plpgsql.so         [.] plpgsql_param_eval_var
    12.02%  postgres  plpgsql.so         [.] exec_stmt
    11.10%  postgres  plpgsql.so         [.] exec_eval_expr
    10.05%  postgres  postgres           [.] SPI_plan_is_valid
     7.09%  postgres  postgres           [.] MemoryContextReset
     6.65%  postgres  plpgsql.so         [.] exec_assign_value
     3.53%  postgres  plpgsql.so         [.] exec_assign_expr
     2.91%  postgres  postgres           [.] int84lt
     2.61%  postgres  postgres           [.] int84pl
     2.42%  postgres  plpgsql.so         [.] exec_cast_value
     0.86%  postgres  postgres           [.] CachedPlanIsValid
     0.16%  postgres  plpgsql.so         [.] SPI_plan_is_valid@plt
     0.05%  postgres  [kernel.kallsyms]  [k] __do_softirq
     0.03%  postgres  [kernel.kallsyms]  [k] finish_task_switch

I didn't send the patch, because it didn't handle the cases where a
simple expression consists of an inline-able function(s) in it, which
are better handled by a full-fledged planner call backed up by the
plan cache.  If we don't do that then every evaluation of such
"simple" expression needs to invoke the planner.  For example:

Consider this inline-able SQL function:

create or replace function sql_incr(a bigint)
returns int
immutable language sql as $$
select a+1;
$$;

Then this revised body of your function foo():

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS int
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare i bigint = 0;
begin
  while i < 1000000
  loop
    i := sql_incr(i);
  end loop; return i;
end;
$function$
;

With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
it takes 5102 ms.

I think the patch might be good idea to reduce the time to compute
simple expressions in plpgsql, if we can address the above issue.

Thanks,
Amit

plpgsql-simple-exprs.patch (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


út 18. 2. 2020 v 6:03 odesílatel Amit Langote <[hidden email]> napsal:
Hi,

On Sun, Feb 16, 2020 at 11:13 PM Pavel Stehule <[hidden email]> wrote:
> when I do some profiling of plpgsql, usually I surprised how significant overhead has expression execution. Any calculations are very slow.
>
> This is not typical example of plpgsql, but it shows cleanly where is a overhead
>
> CREATE OR REPLACE FUNCTION public.foo()
>  RETURNS void
>  LANGUAGE plpgsql
>  IMMUTABLE
> AS $function$
> declare i bigint = 0;
> begin
>   while i < 100000000
>   loop
>     i := i + 1;
>   end loop;
> end;
> $function$
>
> Profile of development  version
>
>   10,04%  plpgsql.so                          [.] exec_eval_simple_expr
>    9,17%  postgres                            [.] AcquireExecutorLocks
>    7,01%  postgres                            [.] ExecInterpExpr
>    5,86%  postgres                            [.] OverrideSearchPathMatchesCurrent
>    4,71%  postgres                            [.] GetCachedPlan
>    4,14%  postgres                            [.] AcquirePlannerLocks
>    3,72%  postgres                            [.] RevalidateCachedQuery
>    3,56%  postgres                            [.] MemoryContextReset
>    3,43%  plpgsql.so                          [.] plpgsql_param_eval_var

I was thinking about this overhead many months back and had even
written a patch to avoid going to the planner for "simple"
expressions, which can be handled by the executor.  Here is what the
performance looks like:

HEAD:

latency: 31979.393 ms

    18.32%  postgres  postgres           [.] ExecInterpExpr
    11.37%  postgres  plpgsql.so         [.] exec_eval_expr
     8.58%  postgres  plpgsql.so         [.] plpgsql_param_eval_var
     8.31%  postgres  plpgsql.so         [.] exec_stmt
     6.44%  postgres  postgres           [.] GetCachedPlan
     5.47%  postgres  postgres           [.] AcquireExecutorLocks
     5.30%  postgres  postgres           [.] RevalidateCachedQuery
     4.79%  postgres  plpgsql.so         [.] exec_assign_value
     4.41%  postgres  postgres           [.] SPI_plan_get_cached_plan
     4.36%  postgres  postgres           [.] MemoryContextReset
     4.22%  postgres  postgres           [.] ReleaseCachedPlan
     4.03%  postgres  postgres           [.] OverrideSearchPathMatchesCurrent
     2.63%  postgres  plpgsql.so         [.] exec_assign_expr
     2.11%  postgres  postgres           [.] int84lt
     1.95%  postgres  postgres           [.] ResourceOwnerForgetPlanCacheRef
     1.71%  postgres  postgres           [.] int84pl
     1.57%  postgres  postgres           [.] ResourceOwnerRememberPlanCacheRef
     1.38%  postgres  postgres           [.] recomputeNamespacePath
     1.35%  postgres  postgres           [.] ScanQueryForLocks
     1.24%  postgres  plpgsql.so         [.] exec_cast_value
     0.38%  postgres  postgres           [.] ResourceOwnerEnlargePlanCacheRefs
     0.05%  postgres  [kernel.kallsyms]  [k] __do_softirq
     0.03%  postgres  postgres           [.] GetUserId

Patched:

latency: 21011.871 ms

    28.26%  postgres  postgres           [.] ExecInterpExpr
    12.26%  postgres  plpgsql.so         [.] plpgsql_param_eval_var
    12.02%  postgres  plpgsql.so         [.] exec_stmt
    11.10%  postgres  plpgsql.so         [.] exec_eval_expr
    10.05%  postgres  postgres           [.] SPI_plan_is_valid
     7.09%  postgres  postgres           [.] MemoryContextReset
     6.65%  postgres  plpgsql.so         [.] exec_assign_value
     3.53%  postgres  plpgsql.so         [.] exec_assign_expr
     2.91%  postgres  postgres           [.] int84lt
     2.61%  postgres  postgres           [.] int84pl
     2.42%  postgres  plpgsql.so         [.] exec_cast_value
     0.86%  postgres  postgres           [.] CachedPlanIsValid
     0.16%  postgres  plpgsql.so         [.] SPI_plan_is_valid@plt
     0.05%  postgres  [kernel.kallsyms]  [k] __do_softirq
     0.03%  postgres  [kernel.kallsyms]  [k] finish_task_switch

I didn't send the patch, because it didn't handle the cases where a
simple expression consists of an inline-able function(s) in it, which
are better handled by a full-fledged planner call backed up by the
plan cache.  If we don't do that then every evaluation of such
"simple" expression needs to invoke the planner.  For example:

Consider this inline-able SQL function:

create or replace function sql_incr(a bigint)
returns int
immutable language sql as $$
select a+1;
$$;

Then this revised body of your function foo():

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS int
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare i bigint = 0;
begin
  while i < 1000000
  loop
    i := sql_incr(i);
  end loop; return i;
end;
$function$
;

With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
it takes 5102 ms.

I think the patch might be good idea to reduce the time to compute
simple expressions in plpgsql, if we can address the above issue.

Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution.

I am worried about too low percent if this fundament methods.

     2.91%  postgres  postgres           [.] int84lt
     2.61%  postgres  postgres           [.] int84pl
 
Perl

  18,20%  libperl.so.5.30.1                        [.] Perl_pp_add
  17,61%  libperl.so.5.30.1                        [.] Perl_pp_lt

So can be nice if we increase percent overhead over 10%, maybe more.

Maybe we can check if expression has only builtin immutable functions, and if it, then we can reuse expression state

More, if I understand well, the function is running under snapshot, so there is not possibility to plan invalidation inside function. So some checks should not be repeated.

Pavel


Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <[hidden email]> wrote:

> út 18. 2. 2020 v 6:03 odesílatel Amit Langote <[hidden email]> napsal:
>> I didn't send the patch, because it didn't handle the cases where a
>> simple expression consists of an inline-able function(s) in it, which
>> are better handled by a full-fledged planner call backed up by the
>> plan cache.  If we don't do that then every evaluation of such
>> "simple" expression needs to invoke the planner.  For example:
>>
>> Consider this inline-able SQL function:
>>
>> create or replace function sql_incr(a bigint)
>> returns int
>> immutable language sql as $$
>> select a+1;
>> $$;
>>
>> Then this revised body of your function foo():
>>
>> CREATE OR REPLACE FUNCTION public.foo()
>>  RETURNS int
>>  LANGUAGE plpgsql
>>  IMMUTABLE
>> AS $function$
>> declare i bigint = 0;
>> begin
>>   while i < 1000000
>>   loop
>>     i := sql_incr(i);
>>   end loop; return i;
>> end;
>> $function$
>> ;
>>
>> With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
>> it takes 5102 ms.
>>
>> I think the patch might be good idea to reduce the time to compute
>> simple expressions in plpgsql, if we can address the above issue.
>
>
> Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution.
I updated the patch to do that.

With the new patch, `select foo()`, with inline-able sql_incr() in it,
runs in 679 ms.

Without any inline-able function, it runs in 330 ms, whereas with
HEAD, it takes 590 ms.

Thanks,
Amit

plpgsql-simple-exprs_v2.patch (15K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <[hidden email]> wrote:

> On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <[hidden email]> wrote:
> > út 18. 2. 2020 v 6:03 odesílatel Amit Langote <[hidden email]> napsal:
> >> I didn't send the patch, because it didn't handle the cases where a
> >> simple expression consists of an inline-able function(s) in it, which
> >> are better handled by a full-fledged planner call backed up by the
> >> plan cache.  If we don't do that then every evaluation of such
> >> "simple" expression needs to invoke the planner.  For example:
> >>
> >> Consider this inline-able SQL function:
> >>
> >> create or replace function sql_incr(a bigint)
> >> returns int
> >> immutable language sql as $$
> >> select a+1;
> >> $$;
> >>
> >> Then this revised body of your function foo():
> >>
> >> CREATE OR REPLACE FUNCTION public.foo()
> >>  RETURNS int
> >>  LANGUAGE plpgsql
> >>  IMMUTABLE
> >> AS $function$
> >> declare i bigint = 0;
> >> begin
> >>   while i < 1000000
> >>   loop
> >>     i := sql_incr(i);
> >>   end loop; return i;
> >> end;
> >> $function$
> >> ;
> >>
> >> With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
> >> it takes 5102 ms.
> >>
> >> I think the patch might be good idea to reduce the time to compute
> >> simple expressions in plpgsql, if we can address the above issue.
> >
> >
> > Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution.
>
> I updated the patch to do that.
>
> With the new patch, `select foo()`, with inline-able sql_incr() in it,
> runs in 679 ms.
>
> Without any inline-able function, it runs in 330 ms, whereas with
> HEAD, it takes 590 ms.
I polished it a bit.

Thanks,
Amit

plpgsql-simple-exprs_v3.patch (15K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <[hidden email]> wrote:
> On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <[hidden email]> wrote:
> > út 18. 2. 2020 v 6:03 odesílatel Amit Langote <[hidden email]> napsal:
> >> I didn't send the patch, because it didn't handle the cases where a
> >> simple expression consists of an inline-able function(s) in it, which
> >> are better handled by a full-fledged planner call backed up by the
> >> plan cache.  If we don't do that then every evaluation of such
> >> "simple" expression needs to invoke the planner.  For example:
> >>
> >> Consider this inline-able SQL function:
> >>
> >> create or replace function sql_incr(a bigint)
> >> returns int
> >> immutable language sql as $$
> >> select a+1;
> >> $$;
> >>
> >> Then this revised body of your function foo():
> >>
> >> CREATE OR REPLACE FUNCTION public.foo()
> >>  RETURNS int
> >>  LANGUAGE plpgsql
> >>  IMMUTABLE
> >> AS $function$
> >> declare i bigint = 0;
> >> begin
> >>   while i < 1000000
> >>   loop
> >>     i := sql_incr(i);
> >>   end loop; return i;
> >> end;
> >> $function$
> >> ;
> >>
> >> With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
> >> it takes 5102 ms.
> >>
> >> I think the patch might be good idea to reduce the time to compute
> >> simple expressions in plpgsql, if we can address the above issue.
> >
> >
> > Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution.
>
> I updated the patch to do that.
>
> With the new patch, `select foo()`, with inline-able sql_incr() in it,
> runs in 679 ms.
>
> Without any inline-able function, it runs in 330 ms, whereas with
> HEAD, it takes 590 ms.

I polished it a bit.

the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,

So it is interesting speedup

Pavel



Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:


út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <[hidden email]> wrote:
> On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <[hidden email]> wrote:
> > út 18. 2. 2020 v 6:03 odesílatel Amit Langote <[hidden email]> napsal:
> >> I didn't send the patch, because it didn't handle the cases where a
> >> simple expression consists of an inline-able function(s) in it, which
> >> are better handled by a full-fledged planner call backed up by the
> >> plan cache.  If we don't do that then every evaluation of such
> >> "simple" expression needs to invoke the planner.  For example:
> >>
> >> Consider this inline-able SQL function:
> >>
> >> create or replace function sql_incr(a bigint)
> >> returns int
> >> immutable language sql as $$
> >> select a+1;
> >> $$;
> >>
> >> Then this revised body of your function foo():
> >>
> >> CREATE OR REPLACE FUNCTION public.foo()
> >>  RETURNS int
> >>  LANGUAGE plpgsql
> >>  IMMUTABLE
> >> AS $function$
> >> declare i bigint = 0;
> >> begin
> >>   while i < 1000000
> >>   loop
> >>     i := sql_incr(i);
> >>   end loop; return i;
> >> end;
> >> $function$
> >> ;
> >>
> >> With HEAD `select foo()` finishes in 786 ms, whereas with the patch,
> >> it takes 5102 ms.
> >>
> >> I think the patch might be good idea to reduce the time to compute
> >> simple expressions in plpgsql, if we can address the above issue.
> >
> >
> > Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution.
>
> I updated the patch to do that.
>
> With the new patch, `select foo()`, with inline-able sql_incr() in it,
> runs in 679 ms.
>
> Without any inline-able function, it runs in 330 ms, whereas with
> HEAD, it takes 590 ms.

I polished it a bit.

the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,

So it is interesting speedup

but regress tests fails



Pavel



Thanks,
Amit

regression.out (778 bytes) Download Attachment
regression.diffs (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:

> st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
>> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
>>> > I updated the patch to do that.
>>> >
>>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
>>> > runs in 679 ms.
>>> >
>>> > Without any inline-able function, it runs in 330 ms, whereas with
>>> > HEAD, it takes 590 ms.
>>>
>>> I polished it a bit.
>>
>>
>> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
>>
>> So it is interesting speedup
>
> but regress tests fails
Oops, I failed to check src/pl/plpgsql tests.

Fixed in the attached.

Thanks,
Amit

plpgsql-simple-exprs_v4.patch (16K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <[hidden email]> wrote:

> On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:
> > st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
> >> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
> >>> > I updated the patch to do that.
> >>> >
> >>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
> >>> > runs in 679 ms.
> >>> >
> >>> > Without any inline-able function, it runs in 330 ms, whereas with
> >>> > HEAD, it takes 590 ms.
> >>>
> >>> I polished it a bit.
> >>
> >>
> >> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
> >>
> >> So it is interesting speedup
> >
> > but regress tests fails
>
> Oops, I failed to check src/pl/plpgsql tests.
>
> Fixed in the attached.
Added a regression test based on examples discussed here too.

Thanks,
Amit

plpgsql-simple-exprs_v5.patch (18K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


st 19. 2. 2020 v 8:09 odesílatel Amit Langote <[hidden email]> napsal:
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <[hidden email]> wrote:
> On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:
> > st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
> >> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
> >>> > I updated the patch to do that.
> >>> >
> >>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
> >>> > runs in 679 ms.
> >>> >
> >>> > Without any inline-able function, it runs in 330 ms, whereas with
> >>> > HEAD, it takes 590 ms.
> >>>
> >>> I polished it a bit.
> >>
> >>
> >> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
> >>
> >> So it is interesting speedup
> >
> > but regress tests fails
>
> Oops, I failed to check src/pl/plpgsql tests.
>
> Fixed in the attached.

Added a regression test based on examples discussed here too.

It is working without problems

I think this patch is very interesting for Postgres 13

Regards

Pavel

Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <[hidden email]> napsal:


st 19. 2. 2020 v 8:09 odesílatel Amit Langote <[hidden email]> napsal:
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <[hidden email]> wrote:
> On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:
> > st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
> >> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
> >>> > I updated the patch to do that.
> >>> >
> >>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
> >>> > runs in 679 ms.
> >>> >
> >>> > Without any inline-able function, it runs in 330 ms, whereas with
> >>> > HEAD, it takes 590 ms.
> >>>
> >>> I polished it a bit.
> >>
> >>
> >> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
> >>
> >> So it is interesting speedup
> >
> > but regress tests fails
>
> Oops, I failed to check src/pl/plpgsql tests.
>
> Fixed in the attached.

Added a regression test based on examples discussed here too.

It is working without problems

I think this patch is very interesting for Postgres 13

I checked a performance of this patch again and I think so there is not too much space for another optimization - maybe JIT can help.

There is relative high overhead of call of strict functions - the params are repeatedly tested against NULL.

Regards

Pavel



Regards

Pavel

Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


po 24. 2. 2020 v 18:47 odesílatel Pavel Stehule <[hidden email]> napsal:


čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <[hidden email]> napsal:


st 19. 2. 2020 v 8:09 odesílatel Amit Langote <[hidden email]> napsal:
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <[hidden email]> wrote:
> On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:
> > st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
> >> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
> >>> > I updated the patch to do that.
> >>> >
> >>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
> >>> > runs in 679 ms.
> >>> >
> >>> > Without any inline-able function, it runs in 330 ms, whereas with
> >>> > HEAD, it takes 590 ms.
> >>>
> >>> I polished it a bit.
> >>
> >>
> >> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
> >>
> >> So it is interesting speedup
> >
> > but regress tests fails
>
> Oops, I failed to check src/pl/plpgsql tests.
>
> Fixed in the attached.

Added a regression test based on examples discussed here too.

It is working without problems

I think this patch is very interesting for Postgres 13

I checked a performance of this patch again and I think so there is not too much space for another optimization - maybe JIT can help.

There is relative high overhead of call of strict functions - the params are repeatedly tested against NULL.

But I found one issue - I don't know if this issue is related to your patch or plpgsql_check.

plpgsql_check try to clean after it was executed - it cleans all plans. But some pointers on simple expressions are broken after catched exceptions.

expr->plan = 0x80. Is interesting, so other fields of this expressions are correct.





Regards

Pavel



Regards

Pavel

Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <[hidden email]> napsal:


po 24. 2. 2020 v 18:47 odesílatel Pavel Stehule <[hidden email]> napsal:


čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <[hidden email]> napsal:


st 19. 2. 2020 v 8:09 odesílatel Amit Langote <[hidden email]> napsal:
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <[hidden email]> wrote:
> On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <[hidden email]> wrote:
> > st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <[hidden email]> napsal:
> >> út 18. 2. 2020 v 17:08 odesílatel Amit Langote <[hidden email]> napsal:
> >>> > I updated the patch to do that.
> >>> >
> >>> > With the new patch, `select foo()`, with inline-able sql_incr() in it,
> >>> > runs in 679 ms.
> >>> >
> >>> > Without any inline-able function, it runs in 330 ms, whereas with
> >>> > HEAD, it takes 590 ms.
> >>>
> >>> I polished it a bit.
> >>
> >>
> >> the performance looks very interesting - on my comp the execution time of  100000000 iterations was decreased from 34 sec to 15 sec,
> >>
> >> So it is interesting speedup
> >
> > but regress tests fails
>
> Oops, I failed to check src/pl/plpgsql tests.
>
> Fixed in the attached.

Added a regression test based on examples discussed here too.

It is working without problems

I think this patch is very interesting for Postgres 13

I checked a performance of this patch again and I think so there is not too much space for another optimization - maybe JIT can help.

There is relative high overhead of call of strict functions - the params are repeatedly tested against NULL.

But I found one issue - I don't know if this issue is related to your patch or plpgsql_check.

plpgsql_check try to clean after it was executed - it cleans all plans. But some pointers on simple expressions are broken after catched exceptions.

expr->plan = 0x80. Is interesting, so other fields of this expressions are correct.

I am not sure, but after patching the SPI_prepare_params the current memory context is some short memory context.

Can SPI_prepare_params change current memory context? It did before. But after patching different memory context is active.

Regards

Pavel






Regards

Pavel



Regards

Pavel

Thanks,
Amit
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule
Hi

I added this patch to a commitfest


It is very interesting speedup and it is in good direction to JIT expressions

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
Hi Pavel,

On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <[hidden email]> wrote:
>
> Hi
>
> I added this patch to a commitfest
>
> https://commitfest.postgresql.org/27/2467/
>
> It is very interesting speedup and it is in good direction to JIT expressions

Thank you.  I was planning to do that myself.

I will take a look at your other comments in a day or two.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

David Steele
Hi Amit,

On 2/25/20 3:42 AM, Amit Langote wrote:

> On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <[hidden email]> wrote:
>> I added this patch to a commitfest
>>
>> https://commitfest.postgresql.org/27/2467/
>>
>> It is very interesting speedup and it is in good direction to JIT expressions
>
> Thank you.  I was planning to do that myself.
>
> I will take a look at your other comments in a day or two.

Do you know when you'll have chance to look at Pavel's comments?

Regards,
--
-David
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
Hi David,

On Tue, Mar 17, 2020 at 8:53 PM David Steele <[hidden email]> wrote:

>
> Hi Amit,
>
> On 2/25/20 3:42 AM, Amit Langote wrote:
> > On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <[hidden email]> wrote:
> >> I added this patch to a commitfest
> >>
> >> https://commitfest.postgresql.org/27/2467/
> >>
> >> It is very interesting speedup and it is in good direction to JIT expressions
> >
> > Thank you.  I was planning to do that myself.
> >
> > I will take a look at your other comments in a day or two.
>
> Do you know when you'll have chance to look at Pavel's comments?

Sorry, I had forgotten about this. I will try to post an update by Thursday.

--
Thank you,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Amit Langote
In reply to this post by Pavel Stehule
Hi Pavel,

Sorry it took me a while to look at this.

On Tue, Feb 25, 2020 at 4:28 AM Pavel Stehule <[hidden email]> wrote:

> po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <[hidden email]> napsal:
>> But I found one issue - I don't know if this issue is related to your patch or plpgsql_check.
>>
>> plpgsql_check try to clean after it was executed - it cleans all plans. But some pointers on simple expressions are broken after catched exceptions.
>>
>> expr->plan = 0x80. Is interesting, so other fields of this expressions are correct.
>
> I am not sure, but after patching the SPI_prepare_params the current memory context is some short memory context.
>
> Can SPI_prepare_params change current memory context? It did before. But after patching different memory context is active.

I haven't been able to see the behavior you reported.  Could you let
me know what unexpected memory context you see in the problematic
case?

--
Thank you,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: plan cache overhead on plpgsql expression

Pavel Stehule


čt 19. 3. 2020 v 10:47 odesílatel Amit Langote <[hidden email]> napsal:
Hi Pavel,

Sorry it took me a while to look at this.

On Tue, Feb 25, 2020 at 4:28 AM Pavel Stehule <[hidden email]> wrote:
> po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <[hidden email]> napsal:
>> But I found one issue - I don't know if this issue is related to your patch or plpgsql_check.
>>
>> plpgsql_check try to clean after it was executed - it cleans all plans. But some pointers on simple expressions are broken after catched exceptions.
>>
>> expr->plan = 0x80. Is interesting, so other fields of this expressions are correct.
>
> I am not sure, but after patching the SPI_prepare_params the current memory context is some short memory context.
>
> Can SPI_prepare_params change current memory context? It did before. But after patching different memory context is active.

I haven't been able to see the behavior you reported.  Could you let
me know what unexpected memory context you see in the problematic
case?

How I can detect it? Are there some steps for debugging memory context?

Pavel

--
Thank you,
Amit
123