標簽

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