PostgreSQL 自定义复合类型(composite type) deform引入的额外开销

作者

digoal

日期

2018-09-28

标签

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.