Re: Why overhead of SPI is so large?

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