标签
PostgreSQL , UDT , 自定义类型
背景
PG 允许用户自定义复合类型，自定义复合类型由多个类型组成，使用时会引入一些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
自定义复合类型，引入开销 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
内部字段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