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
