如何閱讀PG資料庫的執行計劃

如果我們已經找到了某條 T OP SQL, 要針對 S QL 進行最佳化分析，該如何進行呢？在學習其他資料庫的時候，我們已經瞭解到了， S QL 最佳化主要從三個角度進行： 1）掃描方式；2）連線方式；3）連線順序。如果解決好這三方面的問題，那麼這條S QL 的執行效率就基本上是靠譜的。

看懂 S QL 的執行計劃的關鍵也是要首先了解這三方面的基本概念，只有搞清楚了這些基本概念，才能夠更好的看懂 S QL 的執行計劃，下面我們分別來學習這些預備知識。

要想讓 S QL 語句有好的執行效果，首先要採用正確的掃描方式。 P G 的掃描方式與 Oracle等其他資料庫類似，但也存在較大的不同，為了掌握好S QL 語句最佳化的技術，我們首先要學會看 S QL 語句的執行計劃，而看執行計劃的最為基礎的能力就是看懂每一步的掃描方式。下表是一個 P G 常用的表掃描方式的清單，大家一定要熟練掌握。

掃描方式簡稱	掃描方式說明
Seq Scan	順序掃描整個物件
Parallel Seq Scan	採用並行方式順序掃描整個物件
Index Scan	採用離散讀的方式，利用索引訪問某個物件
Index Only Scan	僅透過索引，不訪問錶快速訪問某個物件
Bitmap Index Scan	透過多個索引掃描後形成點陣圖找到符合條件的資料
Bitmap Heap Scan	往往跟隨 bitmap index scan，使用該掃描生成的點陣圖訪問物件
CTE Scan	從 CTE(Common Table Expression)中掃描資料 (WITH Block)
Function Scan	從儲存過程中掃描資料

順序掃描（ Seq Scan ）往往是開銷最大的掃描方式，其方式是針對一個關係（表）從頭到尾進行掃描，從而找到所需要的資料。如果這張表上的資料量比較大，那麼這種掃描方式可能會產生較大的 I O ，消耗較多的 C PU 資源，持續較長的時間。如果某條 SQL 語句掃描某張表的時候返回的記錄數較少（或者返回記錄的比例較少，比如小於 5%）。而S QL 語句的 W HERE 條件中具有針對某幾個欄位的某些條件的，那麼在這張表上建立適當的索引可能會大大提高這條 S QL 的執行效率。如果掃描返回的記錄數佔表的比例比較大，比如超過 50%，那麼，透過索引掃描該表可能效率還不如直接進行順序掃描。因此我們不能看到順序掃描就認為這條 SQL 掃描資料的方式存在問題，而是要根據實際情況來判斷掃描方式是否合理。

並行順序掃描（ Parallel   Seq   Scan）是一種改良的順序掃描，從P G 9.6 開始支援的一種新的掃描功能。如果對於某張表的掃描無法使用索引，必須進行順序掃描，那麼我們如何提高這樣的掃描的效能呢？答案就是 Parallel   Seq   Scan，透過並行掃描的方式對大表進行掃描，從而減少掃描所需的時間。採用並行掃描時應該注意兩個問題：第一個問題是，並行掃描會增加系統的資源開銷，比如在S QL 執行時會消耗更多的 C PU/IO/ 記憶體等資源。如果系統資源本身存在瓶頸，那麼就要儘可能限制並行掃描的數量；第二是並行掃描並不一定具有更高的效率，在不同的系統環境與資料情況下，有時候並行順序掃描效率並不會比普通的順序掃描更快。這取決於並行掃描的協同工作成本是否較高。

索引掃描（ Index   Scan ） 是我們希望遇到的掃描方式，不過索引唯 一掃描（ Index   Only   Scan）具有更高的效率，因為Index   Only   Scan不需要再進行回表操作，就可以完成執行工作，獲得到所需要的資料，因為索引中已經包含了S QL 執行所需要的所有資料。不過我們要注意的是，有些時候，索引掃描的效率還不一定比順序掃描高，比如某個掃描需要返回的行數較多，底層儲存的順序讀效能遠高於離散讀，這種情況下，如果我們還一味追求索引掃描，那麼可能會起到副作用。

C TE SCAN 是一種特殊的掃描，當 S QL 語句中存在 C TE 結構（語法上的 W ITH …） ,那麼在S QL 的執行計劃中會看到 C TE SCAN 的內容。相當於從一個固化的子查詢體中獲得資料。 C TE 結構在一次 S QL 執行中只執行一次，但是可以給 S QL 中的子查詢多次使用，從而減少響應的開銷。

Function   Scan也是一種特殊的掃描方式，是從函式中獲取資料。

針對一個單表的訪問，我們只要選擇最適合的表掃描方式就可以實現最佳化了，不過我們面對的 S QL 往往不是一張單表訪問的，很多 S QL 涉及多張表的關聯操作。因此僅僅瞭解 P G 資料庫的掃描方式是不夠的，我們需要認真學習一下 P G 資料庫的表連線方式。和其他關係型資料庫類似， PostgreSQL 支援三種連線操作：巢狀迴圈連線 （ Nested   Loop   Join） 、合併連線 （ Merge   Join）和 雜湊連線 （ Hash   Join） 。PostgreSQL 中的巢狀迴圈連線和合並連線有幾種變體。 要注意的是這裡所說的 P G 資料庫的表連線方式與 S QL 語句中的表連線不是一碼事。 PostgreSQL支援的三種join方法都可以進行所有的join操作，不僅是INNER JOIN，還有LEFT/RIGHT OUTER JOIN、FULL OUTER JOIN等 。

Nested   Loop   Join（巢狀迴圈連線）是最基本的連線操作，它可以用於任何連線條件。 PostgreSQL 支援巢狀迴圈連線 ，包括其多 種變體。 參與 Nested  Loo p Join 的兩張表分為外表（ Outer）和內表（Inner），首先找出外表符合條件的資料集，然後針對這個資料集的每一行進行一次迴圈，找出內表中符合條件的資料。針對內表的掃描可能是Index   Scan，也可能是Seq   Scan。如果內表資料量不大，那麼Seq   Scan是可以接受的，如果內表比較大，那麼進行Seq   Scan的成本太高，就可能導致Nested   Loop的成本過高。因此這種情況下，就需要在內表上建立適當的索引來進行最佳化。如果關聯條件使用索引的效果不佳，那麼Nested   Loop連線的效能就無法最佳化了。另外如果外表的結果集太大，有上萬甚至幾十萬條記錄，那麼Nested   Loop的迴圈次數就很大，哪怕內表掃描使用Index   Scan，總體效率也不高。

每當讀取外部表的每個元組時，上述巢狀迴圈連線必須掃描內部表的所有元組。如果上面所說的情況出現，由於為每個外表元組掃描整個內表是一個昂貴的過程， PostgreSQL 透過一種變種的 Nested   Loop連線方式- 物化巢狀迴圈連線（Materialized Nested Loop Join）以降低內表的總掃描成本 ，從而解決這個問題 。

=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                         QUERY PLAN                                          

----------------------------------------------------------------------------------

 Nested Loop  (cost=1000.00..180434.51 rows=2 width=8)

   Join Filter: (o.o_id = i.o_id)

   ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

         Filter: (o_id < 3000)

   ->  Materialize  (cost=1000.00..178450.45 rows=29 width=8)

         ->  Gather  (cost=1000.00..178450.31 rows=29 width=8)

               Workers Planned: 2

               ->  Parallel Seq Scan on test_outer o  (cost=0.00..177447.41 rows=12 width=8)

                     Filter: ((o_w_id = 29) AND (o_c_id = 1831))

(9 rows)

從上面的執行計劃看，針對表的過濾條件比較好，篩選後只有 29條記錄，因此針對這張表的條件建立了一個物化檢視，用t est_inner 作為外表，執行 n ested loop 。

第二種常用的表連線方式是 Merge   Join（合併連線）在一些其他資料庫中也叫 Sort Merge Join ，是因為兩個結果集做 JOIN 之前，都需要對連線欄位進行排序，然後再進行連線。如果結果集數量不大，所有元組都可以儲存在記憶體中，那麼排序操作就可以在記憶體中進行；否則，將使用臨時檔案。使用臨時檔案排序的效率遠低於記憶體排序，因此要確保 w ork_mem 的配置足夠大，從而提高合併連線的效能。與巢狀迴圈連線一樣，合併連線也支援物化合並連線來物化內表，使內表掃描更加高效。 Merge   Join往往在內外表的大小相差較小的情況下有較好的效果。

第三種常用的表連線方式是 Hash   Join（雜湊連線）。與Merge  Join 類似， H ash  Join 只能用於自然連線和等連線。 PostgreSQL 中的 H ash  Join的行為取決於表的大小。如果目標表足夠小（更準確地說，內表的大小是 work_mem 的 25% 或更少），它將是一個簡單的兩階段記憶體雜湊連線；否則 需要採用具有傾斜處理的 混合 雜湊 連線。

記憶體中雜湊連線（ In-memory   Hash   Join） 是在work_mem上處理的，這個hash表區在PostgreSQL中稱為batch。一批具有雜湊槽，內部稱為桶 。外表上構建好 Hash桶之後，內表的連線欄位逐個探測Hash桶，完成連線操作。  

 

當內表的元組無法在work_mem中儲存為一個batch時，PostgreSQL使用了混合雜湊連線和skew演算法，這是基於混合雜湊連線的一種變體。在構建和探測階段，PostgreSQL 準備多個批次。批次數與桶數相同 ， 在這個階段，work_mem中只分配了一個batch，其他batch作為臨時檔案建立；並將屬於這些批次的元組寫入相應的檔案並使用臨時元組儲存功能進行儲存。在混合雜湊聯接中，構建和探測階段執行的次數與批次數相同，因為內表和外表儲存在相同的批次數中。在構建和探測階段的第一輪中，不僅建立了每個批次，而且處理了內部表和外部表的第一批。另一方面，第二輪和後續輪次的處理需要向/從臨時檔案寫入和重新載入，因此這些是昂貴的過程。因此，PostgreSQL 還準備了一個名為skew的特殊批處理，以在第一輪更有效地 處理更多的元組。

瞭解了表的掃描方式與表連線的方式之後，我們就可以來分析 S QL 的執行計劃了。不過在看執行計劃之前，我們還需要了解一下執行計劃中的每個節點的操作。常見的操作包括如下幾種：

l  join – 採用某種方法把兩個 n ode 的資料連線起來

l  sort – 進行排序操作

l  limit – 透過 l imit 結束掃描，限制返回的資料量

l  aggregate – 進行彙總

l  hash aggregate – 透過 h ash 分組資料

l  unique – 對於已經排序的資料進行除重

l  gather – 從不同的併發 w orker 中彙總資料

學習了每個節點的運算子，我們基本上就能看懂 P G 的執行計劃了。我們可以使用 e xplain 命令來檢視 P G 的 S QL 語句的執行計劃。 Explain 命令的語法如下：

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

EXPLAIN [ ( option [, ...] ) ] statement

 ANALYZE 執行 S QL 並且顯示執行細節

 VERBOSE 詳細輸出

 COSTS 顯示執行計劃開銷

 BUFFERS 顯示查詢的 b uffers 操作資訊

 TIMING 顯示執行消耗的時間

 SUMMARY 在最後顯示彙總資訊

FORMAT TEXT / XML / JSON / YAML 顯示格式選擇

下面我們還是以上面舉例的那條 S QL 來看看 SQL 的執行計劃。透過 e xplain 命令我們可以獲得某條 S QL 語句的執行計劃。比如下面的 S QL:

 

PG=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                         QUERY PLAN                                          

-------------------------------------------------------------------------------------------

 Nested Loop  (cost=1000.00..180434.51 rows=2 width=8)

   Join Filter: (o.o_id = i.o_id)

   ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

         Filter: (o_id < 3000)

   ->  Materialize  (cost=1000.00..178450.45 rows=29 width=8)

         ->  Gather  (cost=1000.00..178450.31 rows=29 width=8)

               Workers Planned: 2

               ->  Parallel Seq Scan on test_outer o  (cost=0.00..177447.41 rows=12 width=8)

                     Filter: ((o_w_id = 29) AND (o_c_id = 1831))

(9 rows)

我們看到最下面的兩行，只針對 test_outer 表做並行 Seq   Scan，條件正是S QL 語句中針對該表的兩個過濾條件。 Parallel   Sequence   Scan的成本為:

(cost=0.00..177447.41 rows=12 width=8)

從上面的資料可以看出， Parallel  Seq Scan 的成本是 177447.41。經過Gather後生成了一個物化檢視，成本變為178450.45。rows=12指出了本操作返回的行數，而w idth =8指出了每行資料的長度，r ows*width 可以計算出操作涉及的位元組數。

然後執行了一個和物化檢視同等級的 Seq   Scan，是針對 test_inner 表的，這個掃描操作：

  ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

這個操作的成本為 691.75，返回2971條記錄。然後這兩個結果集之間進行 Join, 方式採用的是 Nested   Loop。

讀懂了執行計劃，就可以判斷執行計劃中那些地方存在問題了。我們看到對於 test_outer 表的掃描採用 Parallel   Seq   Scan的成本佔比很高，如果要最佳化這條S QL ，可以考慮建立一個 o _c_id 和 o _w_id 的索引來進一步最佳化。

highgo=# create index idx_outer1 on test_outer(o_c_id,o_w_id);

CREATE INDEX

highgo=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                      QUERY PLAN                                       

---------------------------------------------------------------------------------------

 Hash Join  (cost=729.32..760.00 rows=2 width=8)

   Hash Cond: (o.o_id = i.o_id)

   ->  Index Scan using idx_outer1 on test_outer o  (cost=0.43..30.98 rows=29 width=8)

         Index Cond: ((o_c_id = 1831) AND (o_w_id = 29))

   ->  Hash  (cost=691.75..691.75 rows=2971 width=8)

         ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

               Filter: (o_id < 3000)

(7 rows)

可以看出，執行計劃中使用了這個索引，而且表連線方式也變成了 Hash   Join，Cost也下降了上百倍。這是P G 資料庫 S QL 最佳化最為常用的方法。