postgresql , 單列索引 , 複合索引 , 優化器 , 成本因子
經常聽到這樣的聲音：“查詢慢？加個索引吧。”，雖然話不專業，但是展現了早期基于rbo（基于規則）的優化器思維。
通常對業務不熟悉，或者對資料庫不熟悉時，可能會憑自覺做出這樣的判斷。
rbo思維存在較大的問題，是以導緻了cbo（基于成本）的出現。
再往後，(生成執行計劃->執行這樣的)靜态cbo又要落伍了，緊接着會是動态的執行計劃(邊執行->邊生成下一階段的執行計劃)。
動态執行計劃好似導航軟體的躲避擁堵功能，階段性的給出最佳的線路。
postgresql pro動态執行計劃roadmap
<a href="https://postgrespro.com/roadmap/">https://postgrespro.com/roadmap/</a>
<a href="https://postgrespro.com/roadmap/56513">https://postgrespro.com/roadmap/56513</a>
我們回到cbo，既然是基于成本的優化，那麼成本是如何算出來的呢？
資料庫收到使用者的sql請求後，會經過parser, rewrite, 産生paths, 産生最優plan, execute plan, 傳回結果。
源碼參考
<a href="https://www.postgresql.org/developer/backend/">https://www.postgresql.org/developer/backend/</a>
産生paths的功能類似下圖：達到目的有多少種方法；或者去往某個目的地，有多少種走法；又或者解題有多少種解法。
産生最優plan，(從多個解法中，選擇成本最低的path)，生成plan。
我們看上面那張圖，每一個node(小圓圈)是一次運算，運算完将資料輸送給上層的node，到達頂端時計算結束傳回結果給使用者。
每個path的成本，取決于該path每個node的成本總和。
接下來引出今日話題，當優化器可以選擇不同的索引解決同一個sql的問題時，該選哪個呢？
例子
接下來，看完本文，不僅僅可以解答今日問題，其他優化器相關的問題也迎刃而解。
前面說了，cbo是基于成本的優化，當一條sql可以使用多個索引，或者可以選擇多種通路路徑時，該如何選擇呢？
這是優化器經常需要面對的問題。特别是postgresql支援的通路方法很多，選擇更多。
有哪些node可以參考 src/backend/commands/explain.c
手冊中有詳細的成本計算方法的例子
chapter 67. how the planner uses statistics
<a href="https://www.postgresql.org/docs/9.6/static/planner-stats-details.html">https://www.postgresql.org/docs/9.6/static/planner-stats-details.html</a>
拆解後，node成本的計算實際上依賴幾個東西：
1. 成本因子，詳見
<a href="https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#runtime-config-query-constants">https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#runtime-config-query-constants</a>
2. 統計資訊（記錄數、page數、列統計資訊、線性相關性、高頻值、高頻值的比例等），詳見
pg_stats統計視圖。
3. 算法。每種node的算法都不一樣，詳見
src/backend/optimizer/path/costsize.c
通過改變統計資訊、成本因子、算法，可以改變node的成本計算結果。
1. 統計資訊通過analyze收集，postgresql支援列級設定柱狀圖bucket大小。預設是100，最大可以設定到10000。
bucket越大，統計資訊越準确，但是統計耗時越長。
修改統計資訊，會直接影響node的成本計算結果。
2. 修改成本因子，可以直接影響node的成本計算結果。
例如全表掃描node，修改seq_page_cost會影響全表掃描node單個page的掃描成本。
3. 修改算法，也會導緻成本計算結果的變化，需要動到postgresql核心costsize.c，或者使用postgresql核心提供的hook修改成本的計算結果。
有三種方法，可以讓優化器最終選哪個path生成plan。
改成本因子，實際上是改node成本的計算結果。進而讓優化器改變最終的選擇。
要生成準确的成本，需要三個因素都準确，1. 成本因子，2. 統計資訊，3. 算法。
其中成本因子的校準，可以參考如下文章
<a href="https://github.com/digoal/blog/blob/master/201311/20131126_03.md">《優化器成本因子校對 - postgresql explain cost constants alignment to timestamp》</a>
通過修改成本因子，可以達到修正對應node成本的目的。
測試表
測試sql1
如果要讓以上sql使用idx2(使用idx2)，我們隻需要調大cpu_tuple_cost的開銷即可(因為這部分開銷是idx1産生的，而idx2不會産生這部分開銷)。
在設大cpu_tuple_cost之前，為什麼資料庫選擇了idx1呢，到底什麼導緻了idx2的成本高于idx1了？
我們看到idx2比idx1略大（page數更多），是以離散掃描的成本算進來，導緻總成本比idx2更低了。
通過開關，可以讓優化器避免選擇某些路徑，這些路徑不會被生成，也不會計算成本，最終也不會被選擇。
postgresql支援的開關如下
<a href="https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#runtime-config-query-enable">https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#runtime-config-query-enable</a>
控制是否提升子查詢
控制顯示的join（full join除外）是否使用使用者提供的join順序。
其他開關
通過設定這些開關，可以讓優化器使用或者不使用某些path，進而控制最終的執行計劃。
例如把所有的索引掃描，bitmap scan都關掉，會變成全表掃描。
通過hint插件（實際上就是hook做的），可以強制優化器使用你設定的路徑。
比如告訴優化器，請使用hash join，或者使用某個索引。
hint的使用例子如下
<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>
預設情況下，資料庫多表join時，會使用窮舉法，将所有的join順序排列出來，生成非常多的path。join的表越多，path就越多，導緻執行計劃花費較多的時間。
如果想避免窮舉法帶來多表join執行計劃花費過多，
一種方法是使用前面提到的顯示join以及設定join_collapse_limit，from_collapse_limit=1。
另一種方法是使用遺傳算法，當from中的join對象大于門檻值，将使用遺傳算法。
遺傳算法請參考
<a href="https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#guc-geqo-threshold">https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#guc-geqo-threshold</a>
1. 10.0對優化器有一些改造，比如自定義統計次元，比如join循環的優化。
<a href="https://github.com/digoal/blog/blob/master/201703/20170330_05.md">《postgresql 10.0 preview 功能增強 - 自由定義統計資訊次元》</a>
<a href="https://www.postgresql.org/docs/devel/static/sql-createstatistics.html">https://www.postgresql.org/docs/devel/static/sql-createstatistics.html</a>
自定義列統計資訊例子
3. 更多詳見10.0的release note
<a href="https://www.postgresql.org/docs/devel/static/release-10.html">https://www.postgresql.org/docs/devel/static/release-10.html</a>
e.1.3.1.4. optimizer
add multi-column optimizer statistics to compute the correlation ratio and number of distinct values (tomas vondra, david rowley, Álvaro herrera)
new commands are create, alter, and drop statistics. this is helpful in estimating query memory usage and when combining the statistics from individual columns.
improve planner matching of boolean indexes (tom lane)
improve performance of queries referencing row-level security restrictions (tom lane)
the optimizer now has more flexibility in reordering executor behavior.
<a href="https://github.com/digoal/blog/blob/master/201404/20140426_01.md">《索引順序掃描引發的堆掃描io放大背後的統計學原理與解決辦法》</a>
<a href="https://github.com/digoal/blog/blob/master/201403/20140316_01.md">《postgresql 嵌套循環成本估算方法 - nestloop loop cost & cost_material run_cost》</a>