Re: plans for PostgreSQL 12
| От | Pavel Stehule |
|---|---|
| Тема | Re: plans for PostgreSQL 12 |
| Дата | |
| Msg-id | CAFj8pRCHWxak3jou1E+maASBwTTAktCxUd3XRNYctXXEtqmCgw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: plans for PostgreSQL 12 (Andres Freund <andres@anarazel.de>) |
| Ответы |
Re: plans for PostgreSQL 12
(Andres Freund <andres@anarazel.de>)
|
| Список | pgsql-hackers |
2018-06-04 20:55 GMT+02:00 Andres Freund <andres@anarazel.de>:
Hi,
On 2018-06-04 07:35:23 +0100, Simon Riggs wrote:
> On 4 June 2018 at 06:08, Pavel Stehule <pavel.stehule@gmail.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@nemesis postgresql]$ gcc --version
gcc (GCC) 8.1.1 20180502 (Red Hat 8.1.1-1)
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
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
- 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
- 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
- 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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: pg_replication_slot_advance to return NULL instead of 0/0 ifslot not advanced
Следующее
От: Ashutosh BapatДата:
Сообщение: Re: Performance regression with PostgreSQL 11 and partitioning