了解原理的重要性 - 論PostgreSQL merge join 成本評估陷阱 含case

postgresql支援三種join的方法，nestloop, merge, hash。

這三種join方法的差别和原理可以參考

<a href="https://www.postgresql.org/docs/devel/static/planner-optimizer.html">https://www.postgresql.org/docs/devel/static/planner-optimizer.html</a>

<a href="https://github.com/digoal/blog/blob/master/201205/20120521_02.md">《postgresql nestloop/hash/merge join講解》</a>

nested loop join:

merge join:

hash join:

對于merge join，在估算成本時，如果join列有索引，那麼會掃描索引，擷取該列的最大值和最小值。

(注意，資料庫的統計資訊中并沒有最大值和最小值)

那麼問題來了，如果索引出現了劇烈傾斜（或者沒有及時釋放空頁），那麼在評估merge join的執行計劃時，可能導緻執行計劃時間過長。

下面看一個例子。

建立兩張測試表，關閉表級autovacuum，以免影響結果

往兩張表分别插入1000萬記錄

檢查mergejoin已打開

打開時間記錄

檢視執行計劃，目前生成執行計劃的耗時很正常

收集統計資訊，生成表對應的vm, fsm檔案。

再次生成執行計劃，強制使用merge join，執行計劃的時間依舊正常

當沒有索引時，評估merge join的成本不需要擷取最大值和最小值

建立tbl1的join字段id的索引

目前索引大小214 mb

删除tbl1表的前9999999條記錄

重新生成執行計劃，發現現在執行計劃耗時變長了很多很多

再一次生成執行計劃，耗時還是不正常，但是略有好轉，可能因為索引頁的資料已經在記憶體中了。

執行計劃的時間與通過索引查詢join列的最大最小值的時間基本一緻

沒有評估到merge join的時候，執行計劃是正常的

将優化器的enable_mergejoin關閉，執行計劃的耗時恢複正常，是以問題的根源是merge join執行計劃本身的問題，後面會有更細緻的分析

目前索引大小依舊是214 mb

使用pageinspect插件，檢查一下目前索引

首先，從metapage，查到索引的root page id

查詢root page有多少條目，可以看到雖然資料都删了，但是索引還沒有清理，這些條目依舊存在索引頁中。

這也是為什麼使用這個索引查找min, max會很慢的原因，因為它不知道這些資料已經被删除了，必須通過索引條目通路到heap page對應的tuple後，才知道。

查找root page索引條目的明細

接下來使用vacuum tbl1 回收垃圾頁，這個動作同樣會回收tbl1的索引垃圾頁，對于全部dead的索引也，會置為empty page。

現在，使用索引又很快了

那麼現在merge join執行計劃的耗時恢複正常了嗎？

恢複了

雖然現在索引大小沒有變化，但是實際上沒有引用的index page都置為empty page了

具體詳見btree的readme

src/backend/access/nbtree/readme

觀察vacuum後索引頁的變化

首先擷取metapage的資訊，得到root page id，注意索引的層次并沒有變化，依舊是2層，也就是說有第一層是branch節點，第二層是leaf節點。

讀取root page的資訊，顯然現在root page隻有一個條目，即一級branch的某個page

檢視第一級，branch的資訊，找到第二級，leaf節點。

檢視第二級，leaf節點的資訊

leaf節點，對應的是heap table的行号，是以通過行号，可以直接通路資料

從以上分析可以得到一個結論

在資料庫中執行多表join時，如果沒有設定enable_mergejoin=off，那麼資料庫可能會選擇merge join，或者說資料庫需要評估merge join的成本。

當join列有索引存在，為了算出更精确的cost值，評估merge join的成本會用到該列的min, max值（通過掃描join列的索引得到）。

不管任何原因，掃描索引得到min,max 比較慢的話，執行計劃的時間都會被拉長。

某個業務，每天會從幾千萬資料中清除幾十萬，然後就發現某些join的sql執行計劃時間變得好長（雖然最後選擇的是nest loop join，但是評估過程依舊需要評估merge join的成本）。

如何發現的？

1. 使用perf

2. 使用gdb, 或者列印程序的 pstack

某個場景得到的bt

當系統關閉了autovacuum後，如果批量删除或更新資料，可能會導緻索引出現大量引用dead tuple的頁面，進而評估與這些列有關的join可能時間會變長（指merge join）

1. 當使用了綁定變量時，可能能解決以上問題，但是也可能無法解決以上問題，因為postgresql綁定變量有一個避免執行計劃傾斜的算法，會記錄custom plan的次數和平均成本，根據plan cache和傳入的參數，調用choose custom plan，評估generic plan的成本，和custem plan平均成本進行比較，以此判斷是否需要custom plan.

如果需要custom plan，那麼會重新評估各種執行計劃的成本。生成一次custom plan。

原理詳見本文末尾的幾篇參考文檔。

2. autovacuum設定的門檻值太大（autovacuum_vacuum_scale_factor=0.2），預設是20%，也就是說隻有資料發送了20%變化後，才會自動清理。

如何避免呢？

1. 不要關閉表的autovacuum。

2. 對于大表，可以設定表級autovacuum 門檻值，比如1%，或者更小一點。

create table 或者 alter table都可以，文法詳見postgresql手冊。

3. 開啟系統級autovacuum, 并設定合理的autovacuum_vacuum_scale_factor，不要太大。

4. 在大量删除資料或者更新資料後，人為的對這些表執行vacuum analyze table;, 避免以上問題。

當join列有索引存在，并且優化器允許merge join時，評估merge join的成本時需要用到該列的min,max值，min,max值通過索引獲得。

當join列都沒有索引存在時，評估merge join的成本，不需要min,max值。是以評估merge join的執行計劃很快。

從索引擷取min,max值，直接影響了産生執行計劃的耗時。

當資料被批量删除後，如果沒有觸發vacuum垃圾回收，評估merge join的成本就可能比較耗時，也就是本文提到的case。

執行vacuum後，index的垃圾也會被清理，優化器評估merge join成本時用到的min,max值可以很快獲得。

<a href="https://github.com/digoal/blog/blob/master/201606/20160617_02.md">《為什麼用 postgresql 綁定變量 沒有 oracle pin s 等待問題》</a>

<a href="https://github.com/digoal/blog/blob/master/201606/20160617_01.md">《postgresql plan cache 源碼淺析 - 如何確定不會計劃傾斜》</a>

<a href="https://github.com/digoal/blog/blob/master/201212/20121224_01.md">《執行計劃選擇算法 與 綁定變量 - postgresql prepared statement: spi_prepare, prepare|execute command, pl/pgsql style: custom &amp; generic plan cache》</a>