PostgreSQL SQL OUTLINE插件sr_plan (儲存、篡改、固定 執行計劃)

postgresql , sql plan outline , 執行計劃篡改 , query rewrite , sr_plan , pg plan hint

功能較為強大的資料庫，通常都有query rewrite的功能，比如join時提升或下推條件，調整join順序等。

例如

執行以下查詢，我們看看query rewrite如何工作的

這裡隻提供了a.id=1的條件，這個查詢條件被重寫，推入子查詢中，是以我們看到子查詢實際上也過濾了b.id=1的條件。

通過以上例子，我們見識到了query rewrite的用途，那麼是不是所有場景都能rewrite 呢？

例如我們把以上query換一個條件，改寫為如下

從以上執行計劃，我們看到這個query rewrite并沒有将a.info='test1'間接的推入子查詢。

而實際上，postgresql隻是根據成本選擇了一個執行計劃，并不是說它不能推入a.info='test1'的條件，請繼續看我在後面sr_plan中的測試，會看到postgresql的cbo還是非常強大的。

另一方面，作為使用者，以上query可以改寫為如下（或者說這是你期待的query rewrite對吧）

改寫後的執行計劃如下，b在聚合前，可以使用a的條件過濾掉一些記錄，進而減少聚合的量

query rewrite是一個比較智能的工作，在某些情況下，可以起到很好的性能優化作用，query rewrite也是許多資料庫産品比拼的技術之一。

postgresql這方面還是非常有優勢的，請看我在sr_plan中的例子，加油。

其實除了query rewrite，postgresql的社群還提供了一個非常強大的插件，sr_plan。

類似于oracle的sql outline。

sr_plan插件，可以儲存query的執行計劃，（支援綁定變量的query），同時允許篡改執行計劃，讓篡改的執行計劃生效。

針對每一條儲存的執行計劃，允許單獨開啟或關閉。

sr_plan實際上利用了postgresql的鈎子，通過post_parse_analyze_hook擷取parser後的text并儲存到sr_plan的query字段中，通過planner_hook儲存、處理、傳回儲存的執行計劃。

了解sr_plan的工作原理，我們來試用一下，看看以上query如何使用sr_plan來重寫。

安裝依賴 - python 3.2+

安裝依賴mako

安裝依賴pycparser

安裝sr_plan

修改postgresql配置，讓資料庫啟動是加載鈎子

1. 在需要使用sr_plan的資料庫中建立extension, 他會建立保留執行計劃的表

2. 建立測試表，分别插入1000萬記錄

3. 開啟sr_plan.write_mode, 允許sr_plan收集sql和執行計劃

4. 檢視query 1的執行計劃

postgresql支援merge join、groupaggregate(通過index scan)，是以這個case，非常快，并不需要b對所有資料進行聚合。

但是為了示範需求，我們還是繼續往下，看看人為rewrite的sql

5. 檢視query 2的執行計劃

6. 執行以下query後，query的執行計劃被儲存到sr_plans中

7. 禁止sr_plan收集sql與執行計劃

8. 檢視儲存的執行計劃

9. 替換（篡改）執行計劃

将query_hash=1668453880的執行計劃替換為1956817209的執行計劃

達到query rewrite的目的

10. 允許query使用sr_plan儲存的執行計劃

11. 驗證query是否已使用sr_plan儲存的執行計劃

1. postgresql 本身支援的聚合、join、通路方法、query rewrite等非常豐富，通過 explainnode@src/backend/commands/explain.c 代碼可以看到，支援非常的豐富。

2. 通過sr_plan插件，我們可以儲存、篡改、固定query的執行計劃，達到與oracle outline system同樣的效果。

3. 隻要parser後的query不變，執行計劃就不會變化。

4. 除了sr_plan插件，postgresql還有一個plan hint插件，可以強行指定執行計劃，減少plan的時間，同時也可以避免plan不穩定的問題。

當然了，postgresql本身在執行計劃，統計資訊的更新方面都是非常給力的，需要使用以上插件的地方相對較少。

5. sr_plan支援綁定變量的sql,使用_p函數表示綁定參數

6. 你甚至可以改寫query，連接配接收對象都改掉。

<a href="https://github.com/digoal/blog/blob/master/201607/20160723_02.md">《關鍵時刻hint出彩 - pg優化器的參數優化、執行計劃固化case》</a>

<a href="https://github.com/digoal/blog/blob/master/201605/20160523_02.md">《postgresql 特性分析 plan hint》</a>

<a href="https://github.com/digoal/blog/blob/master/201604/20160401_01.md">《阿裡雲 postgresql pg_hint_plan插件的用法》</a>

<a href="https://github.com/digoal/blog/blob/master/201602/20160203_01.md">《postgresql sql hint的使用(pg_hint_plan)》</a>

<a href="https://github.com/postgrespro/sr_plan">https://github.com/postgrespro/sr_plan</a>