Hi
I testing very simple function
create or replace function f1(int) returns int as $$ declare i int = 0;
begin while i < $1 loop i = i + 1; end loop; return i; end $$ language
plpgsql immutable;
profile - when function is marked as immutable
8,65% postgres [.] ExecInterpExpr
▒
8,59% postgres [.] AcquireExecutorLocks
▒
6,95% postgres [.] OverrideSearchPathMatchesCurrent
▒
5,72% plpgsql.so [.] plpgsql_param_eval_var
▒
5,15% postgres [.] AcquirePlannerLocks
▒
4,54% postgres [.] RevalidateCachedQuery
▒
4,52% postgres [.] GetCachedPlan
▒
3,82% postgres [.] ResourceArrayRemove
▒
2,87% postgres [.] SPI_plan_get_cached_plan
▒
2,80% plpgsql.so [.] exec_eval_expr
▒
2,70% plpgsql.so [.] exec_assign_value
▒
2,55% plpgsql.so [.] exec_stmt
▒
2,53% postgres [.] recomputeNamespacePath
▒
2,39% plpgsql.so [.] exec_cast_value
▒
2,19% postgres [.] int4pl
▒
2,13% postgres [.] int4lt
▒
1,98% postgres [.] CheckCachedPlan
volatile
7,21% postgres [.] GetSnapshotData
6,92% plpgsql.so [.] exec_eval_simple_expr
5,79% postgres [.] AcquireExecutorLocks
5,57% postgres [.] ExecInterpExpr
4,12% postgres [.] LWLockRelease
3,68% postgres [.] OverrideSearchPathMatchesCurrent
3,64% postgres [.] PopActiveSnapshot
3,36% plpgsql.so [.] plpgsql_param_eval_var
3,31% postgres [.] LWLockAttemptLock
3,13% postgres [.] AllocSetAlloc
2,91% postgres [.] GetCachedPlan
2,79% postgres [.] MemoryContextAlloc
2,76% postgres [.] AcquirePlannerLocks
2,70% postgres [.] ResourceArrayRemove
2,45% postgres [.] PushActiveSnapshot
2,44% postgres [.] RevalidateCachedQuery
2,29% postgres [.] SPI_plan_get_cached_plan
2,18% postgres [.] CopySnapshot
1,95% postgres [.] AllocSetFree
1,81% postgres [.] LWLockAcquire
1,71% plpgsql.so [.] exec_assign_value
1,61% plpgsql.so [.] exec_stmt
1,59% plpgsql.so [.] exec_eval_expr
1,48% postgres [.] int4pl
1,48% postgres [.] CheckCachedPlan
1,40% plpgsql.so [.] exec_cast_value
1,39% postgres [.] int4lt
1,38% postgres [.] recomputeNamespacePath
1,25% plpgsql.so [.] exec_eval_cleanup
1,08% postgres [.] ScanQueryForLocks
1,01% plpgsql.so [.] exec_eval_boolean
1,00% postgres [.] pfree
For tested function almost all CPU should be used for int4pl and int4lt
functions - but there are used only 4% together. I think so almost all of
8,59% postgres [.] AcquireExecutorLocks
▒
6,95% postgres [.] OverrideSearchPathMatchesCurrent
▒
5,72% plpgsql.so [.] plpgsql_param_eval_var
▒
5,15% postgres [.] AcquirePlannerLocks
▒
4,54% postgres [.] RevalidateCachedQuery
▒
4,52% postgres [.] GetCachedPlan
▒
3,82% postgres [.] ResourceArrayRemove
▒
2,87% postgres [.] SPI_plan_get_cached_plan
▒
2,53% postgres [.] recomputeNamespacePath
▒
can be reduced if we know so we should to call just builtin immutable V1
functions.
My example is a extrem - when you use any embedded SQL, then the profile
will be significantly changed. But for some cases there can be nice some
significant speedup of expressions only functions (like PostGIS)
Regards
Pavel
