阿里云数据库ApsaraDB + 关注 每日抽奖!

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

  1. 云栖社区>
  2. 阿里云数据库ApsaraDB>
  3. 博客>
  4. 正文

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

德哥 2019-04-14 12:39:05 浏览441 评论0

摘要: 标签 PostgreSQL , UDT , 自定义类型 背景 PG 允许用户自定义复合类型,自定义复合类型由多个类型组成,使用时会引入一些deform的开销。 例子 postgres=# create unlogged table t(id int, c1 tp1, c2 int); C...

标签

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  
【云栖快讯】阿里巴巴小程序繁星计划,20亿补贴第一弹云应用免费申请,限量从速!  详情请点击

网友评论