標(biāo)簽

PostgreSQL , UDT , 自定義類型

---

背景

PG 允許用戶自定義復(fù)合類型，自定義復(fù)合類型由多個(gè)類型組成，使用時(shí)會(huì)引入一些deform的開銷。

例子

postgres=# create unlogged table t(id int, c1 tp1, c2 int); CREATE TABLE postgres=# insert into t select id, '(1,2,3,4,5)'::tp1, id from generate_series(1,10000000) t(id); INSERT 0 10000000 postgres=# select pg_backend_pid(); pg_backend_pid ---------------- 57357 (1 row)

deform 引入一定開銷

postgres=# explain (analyze,verbose,timing,costs,buffers) select max((t.c1).a) from t; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=23931.62..23931.62 rows=1 width=4) (actual time=3020.162..3020.163 rows=1 loops=1) Output: max((c1).a) Buffers: shared hit=25707 -> Seq Scan on public.t (cost=0.00..2256.77 rows=10000000 width=41) (actual time=0.019..1310.990 rows=10000000 loops=1) Output: id, c1, c2 Buffers: shared hit=25707 Planning Time: 0.083 ms Execution Time: 3020.199 ms (8 rows) postgres=# explain (analyze,verbose,timing,costs,buffers) select max(c2) from t; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=23931.62..23931.62 rows=1 width=4) (actual time=2613.945..2613.946 rows=1 loops=1) Output: max(c2) Buffers: shared hit=25707 -> Seq Scan on public.t (cost=0.00..2256.77 rows=10000000 width=4) (actual time=0.017..1281.070 rows=10000000 loops=1) Output: id, c1, c2 Buffers: shared hit=25707 Planning Time: 0.073 ms Execution Time: 2613.980 ms (8 rows)

perf 跟蹤

perf top -p 57357 -ag

自定義復(fù)合類型，引入開銷 slot_deform_tuple

Samples: 4K of event 'cpu-clock', Event count (approx.): 710891745 Children Self Shared Objec Symbol + 13.71% 13.71% postgres [.] ExecInterpExpr + 10.66% 10.66% postgres [.] slot_deform_tuple + 10.27% 10.27% postgres [.] heap_getnext + 7.54% 7.54% postgres [.] ExecEvalFieldSelect + 7.09% 7.09% postgres [.] HeapTupleSatisfiesMVCC + 5.09% 5.09% postgres [.] AllocSetAlloc + 4.89% 4.89% postgres [.] MemoryContextReset + 4.29% 4.29% postgres [.] ExecScan + 3.73% 3.73% postgres [.] slot_getsomeattrs + 3.69% 3.69% postgres [.] heapgetpage + 3.36% 3.36% postgres [.] XidInMVCCSnapshot + 3.13% 3.13% postgres [.] AllocSetReset + 2.87% 2.87% postgres [.] heap_tuple_untoast_attr + 2.82% 2.82% postgres [.] SeqNext + 2.80% 2.80% libc-2.17.so [.] __memset_sse2 + 2.66% 2.66% libc-2.17.so [.] __memcpy_ssse3_back + 2.56% 2.56% postgres [.] ExecAgg + 2.54% 2.54% postgres [.] ExecStoreTuple + 1.83% 1.83% postgres [.] palloc + 0.93% 0.93% postgres [.] TransactionIdFollowsOrEquals + 0.68% 0.68% postgres [.] int4larger + 0.58% 0.58% postgres [.] hash_search_with_hash_value 0.47% 0.47% postgres [.] TransactionIdPrecedes 0.33% 0.33% postgres [.] ExecSeqScan 0.33% 0.33% postgres [.] pg_detoast_datum 0.31% 0.31% postgres [.] CheckForSerializableConflictOut 0.23% 0.23% postgres [.] heap_page_prune_opt 0.12% 0.12% postgres [.] memset@plt 0.08% 0.08% postgres [.] ResourceOwnerForgetBuffer 0.08% 0.08% postgres [.] LWLockAcquire 0.08% 0.08% postgres [.] PinBuffer 0.04% 0.04% postgres [.] LWLockRelease 0.04% 0.04% postgres [.] UnpinBuffer.constprop.6 0.04% 0.04% postgres [.] hash_any 0.02% 0.02% [kernel] [k] sock_def_readable 0.02% 0.02% postgres [.] memcpy@plt 0.02% 0.02% postgres [.] ReadBuffer_common 0.02% 0.02% postgres [.] RecoveryInProgress 0.02% 0.02% postgres [.] ReleaseBuffer 0.02% 0.02% postgres [.] isTempToastNamespace

內(nèi)部字段perf輸出

Samples: 3K of event 'cpu-clock', Event count (approx.): 811125000 Children Self Shared O Symbol + 18.74% 18.74% postgres [.] slot_deform_tuple + 18.26% 18.26% postgres [.] ExecInterpExpr + 12.54% 12.54% postgres [.] heap_getnext + 11.62% 11.62% postgres [.] HeapTupleSatisfiesMVCC + 4.96% 4.96% postgres [.] ExecScan + 4.91% 4.91% postgres [.] slot_getsomeattrs + 4.75% 4.75% postgres [.] ExecAgg + 4.10% 4.10% postgres [.] SeqNext + 4.02% 4.02% postgres [.] heapgetpage + 4.02% 4.02% postgres [.] MemoryContextReset + 3.61% 3.61% postgres [.] XidInMVCCSnapshot + 3.34% 3.34% postgres [.] ExecStoreTuple + 1.27% 1.27% postgres [.] int4larger + 0.94% 0.94% postgres [.] TransactionIdFollowsOrEquals + 0.70% 0.70% postgres [.] TransactionIdPrecedes + 0.65% 0.65% postgres [.] hash_search_with_hash_value 0.40% 0.40% postgres [.] CheckForSerializableConflictOut 0.38% 0.38% postgres [.] ExecSeqScan 0.27% 0.27% postgres [.] heap_page_prune_opt 0.11% 0.11% postgres [.] ReadBufferExtended 0.08% 0.08% postgres [.] PinBuffer 0.08% 0.08% postgres [.] UnpinBuffer.constprop.6 0.05% 0.05% postgres [.] LWLockAcquire 0.05% 0.05% postgres [.] LWLockRelease 0.05% 0.05% postgres [.] ResourceOwnerForgetBuffer 0.03% 0.03% [kernel] [k] rebalance_domains 0.03% 0.03% postgres [.] LockBuffer 0.03% 0.03% postgres [.] ReleaseBuffer

總結(jié)

以上是生活随笔為你收集整理的PostgreSQL 自定义复合类型(composite type) deform引入的额外开销的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。