PgSQL · 特性分析 · Plan Hint · 數據庫內核月報

## 背景有一個功能，是社區官方版”永遠”不考慮引入的(參見[PG TODO](https://wiki.postgresql.org/wiki/Todo)，查找”Oracle-style”)，即類似Oracle的Plan Hint。社區開發者的理念是，引入Hint功能，會掩蓋優化器本身的問題，導致缺陷不被暴露出來。但對于我們的使用者來講，遇到某些SQL的查詢計劃不好，性能出了問題，使用了其他方法又不奏效的情況下，首先的目標還是想盡快解決問題，而Hint就可以在這種時候幫助到我們。可喜的是，通過集成“民間”的 pg_hint_plan 插件（[文檔](http://pghintplan.osdn.jp/pg_hint_plan.html)），RDS for PG已經支持了Hint功能（RDS for PPAS也是支持的）。現在我們來研究一下這個插件如何使用，又是如何做到改變優化器優化過程，讓優化器聽我們“指揮”的。 ## 使用 Plan Hint在RDS for PG里面缺省是沒有打開的，可以LOAD命令啟用： ~~~ postgres=# LOAD 'pg_hint_plan'; LOAD ~~~ 但注意這只在會話級別有效，重新連接后將失效。如果想要每次連接都自動啟用Hint，可以使用下面的命令（注意必須以RDS的根用戶執行，否則會遇到權限錯誤）。這樣下次連接時，Hint就默認啟用了。 ~~~ postgres=> alter role all set session_preload_libraries = 'pg_hint_plan'; ALTER ROLE ~~~ 為了便于說明，我們使用下面的shell腳本來創建2張表： ~~~ for i in `seq 2` ; do psql -c "drop table t${i}" psql -c "create table t${i}(a int, b int);" psql -c "insert into t${i} select generate_series(1,1000), random() *1000+1" psql -c "create index t${i}_i_a on t${i}(a)" psql -c "create index t${i}_i_b on t${i}(b)" done ~~~ 然后在t1上進行查詢，不使用和使用Hint的查詢計劃分別如下： ~~~ postgres=> explain select * from t1 where a = 1; QUERY PLAN ----------------------------------------------------------------- Index Scan using t1_i_a on t1 (cost=0.28..8.29 rows=1 width=8) Index Cond: (a = 1) (2 rows) postgres=> /*+ SeqScan(t1) */ explain select * from t1 where a = 1; QUERY PLAN --------------------------------------------------- Seq Scan on t1 (cost=0.00..17.50 rows=1 width=8) Filter: (a = 1) (2 rows) ~~~ 可以看出，利用Hint后，我們成功強制使用了表掃描。Hint一般以SQL注釋的形式，出現在SQL的前面，并以/+開頭，以/結尾。注意/*和+之間不能有空格。 ## Hint的種類 pg_hint_plan插件支持的Hint有很多種，分成如下幾類(具體參見[pg_hint_plan文檔](http://pghintplan.osdn.jp/hint_list.html)）： ~~~ 掃描類（Scan Method），指定表的訪問路徑，舉例如下： --順序掃描，參數為表名，也可以帶模式名 SeqScan(t1) --索引掃描，參數為表名和索引名，注意兩者之間是空格，沒有逗號 IndexScan(t1 t1_i_a) --TID掃描 TidScan(t1) --禁止順序掃描 NoSeqScan(t1) --禁止索引掃描 NoIndexScan(t1) 連接類（Join Method），指定表連接的方法，舉例如下： NestLoop(t1 t2) MergeJoin(t1 t2) HashJoin(t1 t2) 連接順序類（Join Order），指定連接的順序，舉例如下： --使t3和t1先連接，最后和t2連接 Leading(t2 (t3 t1)) SET類，即改變任意的GUC變量，舉例如下： --改變random_page_cost Set(random_page_cost 3.0) ROW類型，改變表的連接結果集的估計大小，舉例如下: --將t1和t2的連接結果的估計大小擴大10倍 Rows(t1 t2 *10) ~~~ ## 內核實現看完了形形色色的Hint，我們會想，這些Hint是怎么改變復雜的優化器邏輯，使其生成我們需要的查詢計劃的呢？我們從其源碼看起（源碼可以從[這里](http://iij.dl.sourceforge.jp/pghintplan/)下載）。插件主要的代碼集中在pg_hint_plan.c里面。從其中`PG_init`函數的代碼可以看出，它利用了`planner_hook`（優化器的函數鉤子，實際上是全局變量，存放函數地址，可以被插件更改，換成插件自定義函數的地址），用`pg_hint_plan_planner`取代了原來的優化器邏輯。這樣PG在處理一個SQL時，將調用`pg_hint_plan_planner`來做優化。而`pg_hint_plan_planner`會調用`get_hints_from_comment`，來讀取Hint，并調用`create_hintstate`進行語法分析。這里要說明的是，`create_hintstate`遇到一張表上的多個同類型Hint（包括重復的Hint），只保留最后一個，前面的會忽略。另外，還有兩個函數鉤子被利用：`get_relation_info_hook`?和?`join_search_hook`。這兩個鉤子分別被修改指向了`pg_hint_plan_get_relation_info`和`pg_hint_plan_join_search`。前者是在優化器處理基本表（非視圖、非函數的表）獲取表信息時被調用，調用棧如下： ~~~ query_planner -> add_base_rels_to_query -> build_simple_rel -> get_relation_info -> get_relation_info_hook(即pg_hint_plan_get_relation_info) ~~~ 這個`pg_hint_plan_get_relation_info`做了什么呢？仔細看會驚訝的發現，它是用來刪除索引的！對，它在優化器獲取表的基本信息后被調用，然后其從基本信息刪除了那些在Hint中未使用的索引。例如，t1上有兩個索引t1_i_a和t1_i_b，如果指定了IndexScan(t1 t_i_b)這個Hint，那么t1_i_a的索引信息在這里被刪除，這樣在后續的優化中，就永遠不會考慮t1_i_a這個索引了！再看`pg_hint_plan_join_search`，其被調用的位置如下： ~~~ query_planner -> make_one_rel -> make_rel_from_joinlist ->join_search_hook(即pg_hint_plan_join_search) ~~~ 可見，它是在為一個SQL語句生成連接結果時被調用，其輸入為待連接的表，輸出為連接后生成的表及其最優的查詢計劃。它主要做了兩件事： 1. 調用`rebuild_scan_path`重新生成基本表的訪問路徑。為什么要重新生成呢？因為在基本表的訪問計劃生成階段，掃描類的Hint并未實際起作用（只是對索引做過刪除處理）。例如，即使指定了IndexScan(t1 t1_i_a)，但外部的GUC變量`enable_indexscan`被設置為了off，在這里也只會看到一個表掃描（SeqScan）的查詢計劃。因此這里需要重新設置好GUC變量（例如如果遇到IndexScan Hint，需要把GUC變量enable_indexscan重置為on），再做一遍訪問計劃。由于基本表一般數量較少，訪問計劃也只需再生成一次，所以此步開銷是可接受的； 2. 調用`pg_hint_plan_standard_join_search`生成連接的計劃。這里是應用連接方法和連接順序Hint的地方。要想改變連接方法或順序，需要進一步修改優化器的整個邏輯，但優化器沒那么多的預定義鉤子可用了，采用函數鉤子的方法不可行。于是，插件便“自備”了優化器的主流程代碼（其實是從同版本的PG里面拷貝出來的），見插件代碼中的core.c和make_join_rel.c兩個文件。里面很多地方是被插件修改過的。其中核心的是修改對`add_paths_to_joinrel`的調用，使優化器實際調用`add_paths_to_joinrel_wrapper`。這個函數是用于為輸入的兩張表（可能是連接生成的中間表），生成一個連接計劃。可以看到`add_paths_to_joinrel_wrapper`會先去查找有沒有對應的Hint，如果有就直接利用，并舍棄掉不符合Hint的連接方法和順序（這是連接順序Hint其作用的地方）。可以看到，此插件的實現并不復雜，它巧妙利用了優化器優化流程中的關鍵點，來應用Hint，達到固定查詢計劃的目的。 ## 性能測試從內核實現可以看出，指定Hint后會帶來如下開銷：基本表的訪問路徑要生成兩次；每次連接兩個中間表時，要檢查是否有對應的Hint；很多地方需要反復更新GUC變量來影響計劃生成。當然，由于直接指定了表的連接方法、順序等，減少了生成的中間計劃，這一點又節省了很多開銷。所以，對使用Hint后的編譯時間是否比原來長，不能一概而論。下面我們對Hint造成的編譯開銷做一下粗略測試。測試用例如下： ~~~ \timing DO $$DECLARE count int; BEGIN count := 1; LOOP count := count + 1; begin EXECUTE 'explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b'; IF count > 10000 THEN EXIT; END IF; exception when others then end; END LOOP; END$$; DO $$DECLARE count int; BEGIN count := 1; LOOP count := count + 1; begin EXECUTE '/*+ IndexScan(t1 t1_i_a) IndexScan(t2 t2_i_a) IndexScan(t3 t3_i_a) IndexScan(t4 t4_i_a) IndexScan(t5 t5_i_a) IndexScan(t6 t6_i_a) IndexScan(t7 t7_i_a) IndexScan(t8 t8_i_a) Leading ( t1 t2 t3 t4 t5 t6 t7 t8 ) */ explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b' ; IF count > 10000 THEN EXIT; END IF; exception when others then end; END LOOP; END$$; ~~~ 這里我們使用了8張表，每張表都只有a、b兩個int字段。用兩個DO語句，每個都執行同一SQL語句10000次。一個DO語句是不帶Hint的，另一個帶了較復雜的Hint。測試結果，不帶Hint的執行耗時17秒左右，帶Hint的14秒左右。即帶Hint的反而編譯時間更短（注意這里只執行了explain，為真正執行SQL語句）。