plan cache overhead on plpgsql expression
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
ne 16. 2. 2020 v 15:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
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
overheadCREATE 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
Show quoted text
Regards
Pavel
Hi,
On Sun, Feb 16, 2020 at 11:13 PM Pavel Stehule <pavel.stehule@gmail.com> 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
Attachments:
plpgsql-simple-exprs.patchtext/plain; charset=US-ASCII; name=plpgsql-simple-exprs.patchDownload+26-130
út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:
Hi,
On Sun, Feb 16, 2020 at 11:13 PM Pavel Stehule <pavel.stehule@gmail.com>
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_varI 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 [.] GetUserIdPatched:
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_switchI 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
Show quoted text
Thanks,
Amit
On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09@gmail.com> 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
Attachments:
plpgsql-simple-exprs_v2.patchtext/plain; charset=US-ASCII; name=plpgsql-simple-exprs_v2.patchDownload+151-59
On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09@gmail.com> 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
Attachments:
plpgsql-simple-exprs_v3.patchapplication/octet-stream; name=plpgsql-simple-exprs_v3.patchDownload+150-57
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:
On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09@gmail.com>
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
Show quoted text
Thanks,
Amit
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
út 18. 2. 2020 v 6:03 odesílatel Amit Langote <
amitlangote09@gmail.com> 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
Show quoted text
Pavel
Thanks,
Amit
On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <amitlangote09@gmail.com> 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
Attachments:
plpgsql-simple-exprs_v4.patchtext/plain; charset=US-ASCII; name=plpgsql-simple-exprs_v4.patchDownload+150-58
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <amitlangote09@gmail.com> 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
Attachments:
plpgsql-simple-exprs_v5.patchtext/plain; charset=US-ASCII; name=plpgsql-simple-exprs_v5.patchDownload+209-58
st 19. 2. 2020 v 8:09 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:
On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <
pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <
amitlangote09@gmail.com> 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
Show quoted text
Thanks,
Amit
čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
st 19. 2. 2020 v 8:09 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <
pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <
amitlangote09@gmail.com> 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
Show quoted text
Regards
Pavel
Thanks,
Amit
po 24. 2. 2020 v 18:47 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:st 19. 2. 2020 v 8:09 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <
pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <
amitlangote09@gmail.com> 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.
Show quoted text
Regards
Pavel
Regards
Pavel
Thanks,
Amit
po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
po 24. 2. 2020 v 18:47 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:čt 20. 2. 2020 v 20:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:st 19. 2. 2020 v 8:09 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:On Wed, Feb 19, 2020 at 3:56 PM Amit Langote <amitlangote09@gmail.com>
wrote:On Wed, Feb 19, 2020 at 3:38 PM Pavel Stehule <
pavel.stehule@gmail.com> wrote:
st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <
pavel.stehule@gmail.com> napsal:
út 18. 2. 2020 v 17:08 odesílatel Amit Langote <
amitlangote09@gmail.com> 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
Show quoted text
Regards
Pavel
Regards
Pavel
Thanks,
Amit
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
Pavel
Hi Pavel,
On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <pavel.stehule@gmail.com> 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
Hi Amit,
On 2/25/20 3:42 AM, Amit Langote wrote:
On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <pavel.stehule@gmail.com> 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
david@pgmasters.net
Hi David,
On Tue, Mar 17, 2020 at 8:53 PM David Steele <david@pgmasters.net> wrote:
Hi Amit,
On 2/25/20 3:42 AM, Amit Langote wrote:
On Tue, Feb 25, 2020 at 4:16 PM Pavel Stehule <pavel.stehule@gmail.com> 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
Hi Pavel,
Sorry it took me a while to look at this.
On Tue, Feb 25, 2020 at 4:28 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <pavel.stehule@gmail.com> 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
čt 19. 3. 2020 v 10:47 odesílatel Amit Langote <amitlangote09@gmail.com>
napsal:
Hi Pavel,
Sorry it took me a while to look at this.
On Tue, Feb 25, 2020 at 4:28 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:po 24. 2. 2020 v 18:56 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
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
Show quoted text
--
Thank you,
Amit
