Nic Lin's Blog

喜歡在地上打滾的 Rails Developer

為什麼 Postgres 不選擇 index scan 卻選擇 seq scan?

最近在分析影響效能的 Query,發現 PostgreSQL 有時的查詢效能不如我們預期,用了 EXPLAIN 下去分析索引,發現確實新增的 index 並沒有在 query plain 裡面,我想瞭解為什麼。

評估結果是 seq scan 更快

在數據量很小的時候, seq scan 會比 index scan 更加有效。

那是因為 index scan 至少要發生兩次 I/O,一次是讀取索引塊,一次是讀取數據塊,代價遠高於 seq can。

當 index 很大的時候,情況可能會更加複雜。

Postgres 會自行調整評估值

如果 Postgres 堅持不使用你打的 index, 有些解法是用 enable_seqscan = off 強制關閉,不過並 不建議直接用 enable_seqscan = off 這個動作

query plan 會自行評估用 seqscan / index scan / hash / map 哪一種比較快, 有些時候 seqscan 反而是最快的.

另外有一種說法是:

pgsql 的 query plan 有一個評估值的方式來 “預測” 哪一種方式比較快, 然後會在 cpu loading 比較輕的時候 “故意” 去用比較慢的方式來跑, 累積執行時間的紀錄, 然後調整評估值裡面的參數, 跑一段時間之後的 pgsql 會逐漸處於執行速度穩定的狀態.

這部分待驗證。

PostgreSQL 在處理 Multi-table JOIN 時,會觸發兩種機制

  • exhaustive query planning (全面查詢規劃) 能保證獲得最佳的查詢執行計畫
  • genetic probabilistic search (遺傳機率搜尋) 不能保證最佳的查詢執行計畫

當 JOIN Table 過多時,採用 exhaustive query planning 會非常耗時,例如 16 個 Table 就有 16! 的排列可能。

此時為了避免計算查詢執行計畫過久,所以 PostgreSQL 引入了 genetic probabilistic search,將基因演算法帶入查詢執行計畫的計算,以減少計算時間,但也因為演算法的特性,所以產出的最終執行計畫不一定是最佳的。

genetic probabilistic search 需要演化,所以每次的查詢執行計畫不一,導致整體查詢時間時快時慢,而且若有多台 PostgreSQL 的情況下會更嚴重,因為每台都有各自的基因演化,平均查詢時間更不穩定。

參考來源

comments powered by Disqus