Re: plans for PostgreSQL 12

2018-06-04 20:55 GMT+02:00 Andres Freund <andres(at)anarazel(dot)de>:

> Hi,
>
> On 2018-06-04 07:35:23 +0100, Simon Riggs wrote:
> > On 4 June 2018 at 06:08, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >
> > > 4. optimization expression without necessity to create snapshots -
> > > experiments
> > >
> > > @4 There are lot of not database expressions in PLpgSQL - like var1 :=
> var1
> > > + var2 or var1 := var1 + konst. Own calculation needs about 1% of time
> of
> > > total expression evaluation time. Almost all time get preparing plan
> cache,
> > > preparing snapshot, .. For this case, when no database object is used,
> we
> > > don't need use this infrastructure. I would to measure performance
> impact,
> > > and testing if these optimizations are interesting or not.
>
> Can you show your testcase and the corresponding profile? It seems like
> this should be solvable without adding a new "snapshotless, really
> immutable" class.
>
>
>
./configure --with-libxml --enable-tap-tests --enable-debug --with-perl
CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer"

[pavel(at)nemesis postgresql]$ gcc --version
gcc (GCC) 8.1.1 20180502 (Red Hat 8.1.1-1)

I executed simple script

do $$ declare i bigint = 1; s bigint = 0; begin while i <= 100000000 loop
s := s + i; i := i + 1; end loop; raise notice '%', s; end $$;

7,68% postmaster postgres [.]
GetSnapshotData ▒
7,53% postmaster plpgsql.so [.]
exec_eval_simple_expr ▒
6,49% postmaster postgres [.]
ExecInterpExpr ▒
4,13% postmaster postgres [.]
LWLockRelease ▒
4,12% postmaster postgres [.]
AllocSetAlloc ▒
3,67% postmaster postgres [.]
PopActiveSnapshot ▒
3,39% postmaster postgres [.]
GetCachedPlan ▒
3,28% postmaster postgres [.]
SPI_plan_get_cached_plan ▒
3,11% postmaster postgres [.]
LWLockAttemptLock ▒
2,90% postmaster postgres [.]
OverrideSearchPathMatchesCurrent ▒
2,64% postmaster plpgsql.so [.]
plpgsql_param_eval_var ▒
2,62% postmaster plpgsql.so [.]
exec_assign_value ◆
2,42% postmaster postgres [.]
RevalidateCachedQuery ▒
2,41% postmaster postgres [.]
AcquireExecutorLocks ▒
2,40% postmaster postgres [.]
PushActiveSnapshot ▒
2,30% postmaster postgres [.]
CopySnapshot ▒
2,27% postmaster postgres [.]
CheckCachedPlan ▒
2,18% postmaster postgres [.]
AcquirePlannerLocks ▒
2,01% postmaster postgres [.]
LWLockAcquire ▒
1,98% postmaster plpgsql.so [.]
exec_stmt ▒
1,94% postmaster postgres [.]
MemoryContextAlloc ▒
1,93% postmaster postgres [.]
AllocSetFree ▒
1,77% postmaster postgres [.]
ResourceArrayRemove ▒
1,64% postmaster plpgsql.so [.]
exec_eval_expr ▒
1,62% postmaster postgres [.]
ResourceArrayAdd ▒
1,55% postmaster postgres [.]
ScanQueryForLocks ▒
1,34% postmaster postgres [.]
ReleaseCachedPlan ▒
1,24% postmaster plpgsql.so [.]
exec_cast_value ▒
1,14% postmaster postgres [.]
int84pl ▒
1,14% postmaster postgres [.]
recomputeNamespacePath ▒
1,12% postmaster postgres [.]
GetTransactionSnapshot ▒
1,08% postmaster plpgsql.so [.]
exec_eval_cleanup ▒
0,99% postmaster postgres [.]
MemoryContextReset ▒
0,99% postmaster plpgsql.so [.]
exec_assign_expr ▒
0,83% postmaster plpgsql.so [.]
assign_simple_var ▒
0,82% postmaster postgres [.]
int8pl ▒
0,77% postmaster postgres [.]
ResourceOwnerForgetPlanCacheRef ▒
0,75% postmaster postgres [.]
pfree ▒
0,69% postmaster postgres [.]
ResourceOwnerRememberPlanCacheRef ▒
0,57% postmaster postgres [.]
ResourceOwnerEnlargePlanCacheRefs ▒
0,51% postmaster postgres [.]
ResourceArrayEnlarge ▒
0,44% postmaster postgres [.]
RecoveryInProgress ▒
0,44% postmaster plpgsql.so [.]
exec_stmts ▒
0,39% postmaster plpgsql.so [.]
exec_eval_boolean ▒
0,38% postmaster postgres [.]
TransactionIdPrecedes ▒
0,38% postmaster plpgsql.so [.]
exec_stmt_while ▒
0,29% postmaster postgres [.]
choose_custom_plan ▒
0,29% postmaster plpgsql.so [.]
setup_param_list ▒
0,27% postmaster plpgsql.so [.]
exec_stmt_assign ▒
0,27% postmaster postgres [.]
GetCurrentCommandId

report with call graph - some parts

- 61,06%
exec_assign_expr
▒
- 55,96%
exec_eval_expr
▒
- 54,70%
exec_eval_simple_expr ▒
+ 14,86%
SPI_plan_get_cached_plan ▒
+ 12,72%
GetTransactionSnapshot ▒
+ 8,26% ExecEvalExpr
(inlined) ▒
+ 7,04%
PushActiveSnapshot ▒
+ 4,08%
PopActiveSnapshot ▒
+ 2,13%
ReleaseCachedPlan ▒
0,97% MemoryContextSwitchTo
(inlined) ▒
- 3,81%
exec_assign_value
▒
0,74%
assign_simple_var ▒
0,61%
exec_cast_value ▒
+ 1,14% exec_eval_cleanup

- 11,51% 0,00% postmaster plpgsql.so [.] ExecEvalExpr
(inlined) ▒
- ExecEvalExpr
(inlined) ▒
- 11,03%
ExecInterpExpr
▒
3,10%
plpgsql_param_eval_var ▒
1,11%
int84pl ▒
0,79% int8pl

- 6,90%
GetCachedPlan ▒
- 3,36%
RevalidateCachedQuery ▒
0,96%
OverrideSearchPathMatchesCurrent ▒
0,88%
AcquirePlannerLocks ▒
- 1,31%
CheckCachedPlan ▒
0,71%
AcquireExecutorLocks ▒
0,60%
ResourceOwnerRememberPlanCacheRef ▒
- 6,22%
GetTransactionSnapshot ▒
- 5,58%
GetSnapshotData ▒
+ 1,64%
LWLockAcquire ▒
+ 1,30% LWLockRelease

This example is worst case, but it shows significant overhead of cached
plans and snapshots there.

What do you think about it?

> > Sounds good. I think this would need to be restricted by operator and
> > datatype, since in general you won't know if the datatype functions
> > need a snapshot or not. Immutable functions for the operators ought to
> > do it, but I think that might not be enough.
>
> It'd indeed not be enough. E.g. enum_lt et al are immutable but access
> the catalog.
>
>
good to known, thank you

Pavel

> Greetings,
>
> Andres Freund
>