PostgreSQL DBA(9) - 執行計劃資料結構

查詢執行計劃(Query Plan)對於DBA都已經很熟悉了，這一小節結合先前一些章節的內容，通過日誌分析獲得執行計劃對應的資料結構(PlannedStmt)，通過分析可以知道執行計劃是怎麼來的，達到"知其然而知其所以然"的目的。

一、開啟日誌

編輯postgresql.conf配置檔案,設定debug_print_plan/debug_pretty_print為on:

log_destination = 'csvlog'
log_directory = 'pg_log' #與postgresql.conf檔案在同一級目錄
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 2d
log_rotation_size = 100MB
#
debug_print_parse = off  #是否列印parse樹
debug_print_rewritten = off #是否列印parse rewrite樹
debug_print_plan = on #是否列印plan樹
debug_pretty_print = on #是否以優雅的方式顯示

二、日誌分析

開啟日誌,重啟資料庫後,在$PGDATA/pg_log目錄下會生成相應的日誌檔案,執行SQL語句,可以找到對應的日誌輸出.
測試SQL語句:

select * from (
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
  inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1001')
union all
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
  inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1002') 
) as ret
order by ret.grbh
limit 4;

使用Sublime Text工具開啟日誌,如下圖所示（注意:planTree、rtable節點已摺疊）:

commandType值為1,對應的是SELECT,SQL語句長度為455,relationOids的值為(o 16391 16394 16397 16391 16394 16397)，分別對應t_dwxx/t_grxx/t_jfxx三張表.

testdb=# select relname from pg_class where oid in (16391,16394,16397);
 relname 
---------
 t_dwxx
 t_grxx
 t_jfxx
(3 rows)

rtable和planTree中的詳細結構已在先前章節做過詳細解釋(相關連結詳見參考資料),這裡不再累述.

三、執行計劃資料結構

SQL語句的執行計劃:

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)

通過日誌分析得到的執行計劃資料結構如下圖所示:

四、參考資料

PostgreSQL 原始碼解讀（22）- 查詢語句#7（PlannedStmt結構詳解-日誌分析）
PostgreSQL 原始碼解讀（23）- 查詢語句#8（PlannedStmt與QUERY PLAN）