From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | plan cache overhead on plpgsql expression |
Date: | 2020-02-16 14:12:25 |
Message-ID: | CAFj8pRDRVfLdAxsWeVLzCAbkLFZhW549K+67tpOc-faC8uH8zw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)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(at)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(at)plt
0,25% postgres [.] SPI_push
0,25% plpgsql.so [.] SPI_push(at)plt
0,24% plpgsql.so [.] __sigsetjmp(at)plt
0,23% plpgsql.so [.] SPI_pop(at)plt
0,19% libc-2.30.so [.]
__memset_sse2_unaligned_erms
0,14% libc-2.30.so [.] memcpy(at)GLIBC_2(dot)2(dot)5
0,13% postgres [.] memcpy(at)plt
Is interesting so overhead of plan cache about 15%
The execution needs 32 sec on Postgres13 and 27sec on Postgres8.2
Regards
Pavel
From | Date | Subject | |
---|---|---|---|
Next Message | 曾文旌 (义从) | 2020-02-16 15:07:19 | Re: [Proposal] Global temporary tables |
Previous Message | Andrew Dunstan | 2020-02-16 06:51:37 | Re: Parallel copy |