PostgreSQL in 文法 的優化器處理以及如何優化

postgresql in 的優化器處理以及如何優化

在使用資料庫的過程中，經常會遇到需要比對多個值的情況。

通常的寫法包括：

每種寫法會産生多種執行計劃的可能，如下：

sql優化政策是盡量減少cpu的運算以及page的掃描數量。

下面針對每種sql，看看對應的可能的執行計劃有什麼差别，（使用開關來控制執行計劃的選擇, 如set enable_indexscan=off）

支援的開關如下：

開始測試，使用auto_explain輸出執行計劃：

測試sql寫法1：

優化器選擇1 (index scan)：

離散掃描，适合小的掃描集。

優化器選擇2 (bitmap scan)：

比index scan多了recheck的開銷，以及按照ctid排序的開銷。

适合大的掃描集，排序的目的是減少離散掃描，還可以用到塊裝置的prefetch。

優化器選擇3 (seq scan)：

适合非常龐大的掃描集。

測試sql寫法2：

測試sql寫法3：

優化器選擇1 (bitmapindex scan + bitmapor)：

使用or的寫法，隻能選擇bitmap index scan，是以不如使用in的寫法。

優化器選擇2 (seq scan)：

測試sql寫法4：

優化器選擇1 (nestloop join)：

适合小的掃描集，并且其中有一個表的join列是帶有主鍵或唯一限制的。

優化器選擇2 (hash join)：

适合大的掃描集，同時兩個表的join列上面都沒有索引的情況。

優化器選擇3 (merge join)：

适合大的掃描集，并且兩個表的join列都有索引。

測試sql寫法5：

測試sql寫法6：

優化選擇建議如下，可以根據需求來寫sql，最不建議寫的是or：

index scan：

bitmap scan：

seq scan：

bitmapindex scan + bitmapor|and：

nestloop join：

hash join：

merge join：

如果你發現寫好的sql沒有選擇最優的執行計劃，可以通過設定優化器開關，或者使用hint plan這個插件來指定優化器使用對應的scan或join method.

http://pghintplan.osdn.jp/pg_hint_plan.html