PostgreSQL 原始碼解讀（23）- 查詢語句#8（PlannedStmt與QUERY P...

本小節簡單介紹了PG的PlannedStmt結構與Explain命令輸出的Query Plan的對應關係。

一、測試指令碼

testdb=# explain 
testdb-# select * from (
testdb(# select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
testdb(# from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
testdb(# inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
testdb(# where t_dwxx.dwbh IN ('1001')
testdb(# union all
testdb(# select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
testdb(# from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
testdb(# inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
testdb(# where t_dwxx.dwbh IN ('1002') 
testdb(# ) as ret
testdb-# order by ret.grbh
testdb-# limit 4;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Limit  (cost=96.80..96.81 rows=4 width=360)
   ->  Sort  (cost=96.80..96.83 rows=14 width=360)
         Sort Key: t_grxx.grbh
         ->  Append  (cost=16.15..96.59 rows=14 width=360)
               ->  Nested Loop  (cost=16.15..48.19 rows=7 width=360)
                     ->  Seq Scan on t_dwxx  (cost=0.00..12.00 rows=1 width=256)
                           Filter: ((dwbh)::text = '1001'::text)
                     ->  Hash Join  (cost=16.15..36.12 rows=7 width=180)
                           Hash Cond: ((t_jfxx.grbh)::text = (t_grxx.grbh)::text)
                           ->  Seq Scan on t_jfxx  (cost=0.00..17.20 rows=720 width=84)
                           ->  Hash  (cost=16.12..16.12 rows=2 width=134)
                                 ->  Seq Scan on t_grxx  (cost=0.00..16.12 rows=2 width=134)
                                       Filter: ((dwbh)::text = '1001'::text)
               ->  Nested Loop  (cost=16.15..48.19 rows=7 width=360)
                     ->  Seq Scan on t_dwxx t_dwxx_1  (cost=0.00..12.00 rows=1 width=256)
                           Filter: ((dwbh)::text = '1002'::text)
                     ->  Hash Join  (cost=16.15..36.12 rows=7 width=180)
                           Hash Cond: ((t_jfxx_1.grbh)::text = (t_grxx_1.grbh)::text)
                           ->  Seq Scan on t_jfxx t_jfxx_1  (cost=0.00..17.20 rows=720 width=84)
                           ->  Hash  (cost=16.12..16.12 rows=2 width=134)
                                 ->  Seq Scan on t_grxx t_grxx_1  (cost=0.00..16.12 rows=2 width=134)
                                       Filter: ((dwbh)::text = '1002'::text)
(22 rows)

二、對照關係

對照關係詳見下圖：

結合關係代數的理論和相關術語，可以更好的理解執行計劃。

三、小結

上一小節已通過分析日誌的方式介紹了PlannedStmt的結構，這一小節結合執行計劃進行對照解析，可以加深對執行計劃的理解和認識。