阿里明虛：RDS for PostgreSQL加入Plan Hint功能
有一個功能，是社區官方版"永遠"不考慮引入的(參見 PG TODO ，查找"Oracle-style")，即類似Oracle的Plan Hint。社區開發者的理念是，引入Hint功能，會掩蓋優化器本身的問題，導致缺陷不被暴露出來。但對于我們的使用者來講，遇到某些SQL的查詢計劃不好，性能出了問題，使用了其他方法又不奏效的情況下，首先的目標還是想盡快解決問題。而Hint就可以在這種時候幫助到我們。可喜的是，通過集成“民間”的pg_hint_plan插件（ 文檔 ），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</code></pre>
然后在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)</code></pre>
可以看出，利用Hint后，我們成功強制使用了表掃描。Hint一般以SQL注釋的形式，出現在SQL的前面，并以/*+開頭，以*/結尾。注意/*和+之間不能有空格。
Hint的種類
pg_hint_plan插件支持的Hint有很多種，分成如下幾類(具體參見 pg_hint_plan文檔 ）：
掃描類（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)</code></pre>
內核實現
看完了形形色色的Hint，我們會想，這些Hint是怎么改變復雜的優化器邏輯，使其生成我們需要的查詢計劃的呢？我們從其源碼看起（源碼可以從 這里 下載）。
插件主要的代碼集中在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$$;</code></pre>
這里我們使用了8張表，每張表都只有a，b兩個int字段。用兩個DO語句，每個都執行同一SQL語句10000次。一個DO語句是不帶Hint的，另一個帶了較復雜的Hint。測試結果，不帶Hint的執行耗時17秒左右，帶Hint的14秒左右。即帶Hint的反而編譯時間更短（注意這里只執行了explain，未真正執行SQL語句）。
</div>
來自： http://yq.aliyun.com/articles/3072?spm=5176.100239.yqblog1.59.YItNDO
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！