RedShift 查詢計畫與效能調校

EXPLAIN

EXPLAIN 指令用於查詢計畫的執行計畫、但不實際執行。其用法為將要執行的查詢前面補上 EXPLAIN 即可：

EXPLAIN
SELECT
*
FROM logs
INNER JOIN metadata USING (log_id)
WHERE logs.event_at > '2023-06-01'

這裡的範例為：我想篩選出六月以後的 log，並且與 metadata 表格做連接，兩張表格的原始資料量分別約為 7,600 萬列與 3,400 萬列，此 EXPLAIN 輸出為：

XN Hash Join DS_BCAST_INNER  (cost=6396.21..420419301233.89 rows=407041 width=368)
  Hash Cond: (("outer".log_id)::text = ("inner".log_id)::text)
  ->  XN Seq Scan on metadata  (cost=0.00..339927.40 rows=33992740 width=346)
  ->  XN Hash  (cost=7614.04..7614.04 rows=609124 width=94)
        ->  XN Seq Scan on logs  (cost=0.00..7614.04 rows=609124 width=94)
              Filter: ("event_at" > '2023-06-01 00:00:00'::timestamp without time zone)

在每一個操作的後方皆可以看到這樣格式的一串訊息：

(cost=6396.21..420419301233.89 rows=407041 width=368)

這裡面出現的數字分別為：

1. 初始成本（6396.21）：為資料庫開始進行輸出之前會需要的起始成本，如排序或連接等操作導致的成本
2. 總成本（420419301233.89） ：為假定整個計畫被完整執行的條件下的總成本，這個成本有包含到 WHERE 所刪去的資料量，但不考慮如 LIMIT 的影響。
3. 預期輸出（ 407,041 列）
4. 預期每列寬度（平均 368 bytes）

注意到由於查詢指令並不會被執行，所以上面的所有數字皆為估計。

而上面的查詢計畫可解釋為：

• 此查詢計畫的最上層為一個雜湊連結（hash join）運算，其初始成本為 6,396.21 單位而總成本為 4,204 億單位，預期輸出 40 萬列，每列的平均資料大小為 368 byte。
• 該雜湊連結是基於 log_id 欄位，並對應為：
• 在 metadata 表格上做循序檢查，初始成本 0，總成本 339,927.40，作為外表（outer）
• 內表（inner）做雜湊處理，成本反映為 7,614.04
• 在 logs 表格上做循序檢查以選出六月以後的列，初始成本 0，總成本 7,614.04，作為內表

注意到在原本的指令裡是以 logs 為左表而 metadata 為右表格，但這個查詢計畫裡內外關係則對調了，這是因為在建構查詢計畫時 RedShift 會自動以較大的那張表格最為 outer ，而不是依照原始指令中的左右關係。

參數

VERBOSE

RedShift 僅有支援 VERBOSE 一種參數，顧名思義輸出大量細節。以上面的查詢為例會得到大概像是這樣的輸出：

  { HASHJOIN
  :startup_cost 6398.31
  :total_cost 420556652092.38
  :plan_rows 407174
  :node_id 1
  :parent_id 0
  :plan_width 368
  :best_pathkeys ((
    { PATHKEYITEM
    :key
      { RELABELTYPE
      :arg
        { VAR
        :varno 1
        :varattno 1
        :vartype 1043
        :vartypmod 36
        :varlevelsup 0
        :varnoold 1
        :varoattno 1
...

沒有 ANALYZE

十分可惜的是 PostgreSQL 的 EXPLAIN ANALYZE 在 RedShift 中並沒有實作，所以我們沒辦法利用這項功能。

調校

在 Query analysis workflow 文件中列舉了數項可以改善查詢效率的辦法，其中包含重新檢視表格的設計、減少不必要的資料量、對表格進行資源回收與更新中繼資料與利用 EXPLAIN 來檢視查詢計畫等辦法。

本文注重在查詢計畫的部分，內容擷取自 Analyzing the query plan 及 Improving query performance。整理出來可以注意的點包括：

聯結（Join）所採用的演算法

RedShift 在進行連接時會有三種情況：

1. 巢狀迴圈（nested loop；最糟）
2. 雜湊聯結（hash join）
3. 合併聯結（merge join；最佳）

其中巢狀迴圈為進行交叉連結#Cross_join)（cross join）時所會用到的方法，並且為最差的連結方式，其產生的計算成本會十分可觀，應該盡量避免。若不是刻意要進行交叉連結的場景下，則應該重構程式碼以避免發生。

雜湊連結與合併連結皆應用於其他各連結方式，又根據官方文件，合併聯結在通常狀況下速度會最快，因此我們可以將合併鏈結視為最佳解。合併連結的使用條件為進行連結的欄位同時為該表格的分佈金鑰（dist key）且為排序索引（sort key）。

若連結方式為雜湊連結，改善的方法包括嘗試讓它變成合併連結，即改用那些是分佈金鑰且為排序索引的欄位來進行連結，另外就是確保連結用的欄位本身有唯一性。

以我的查詢為例，修改表格設置讓主鍵¹成為分佈金鑰及排序索引後查詢計畫變成了這樣：

XN Merge Join DS_DIST_NONE  (cost=0.00..1386161.10 rows=372554 width=404)
  Merge Cond: (("outer".log_id)::text = ("inner".log_id)::text)
  ->  XN Seq Scan on metadata  (cost=0.00..339927.40 rows=33992740 width=346)
  ->  XN Seq Scan on logs  (cost=0.00..956003.50 rows=609124 width=94)
        Filter: ("event_at" > '2023-06-01 00:00:00'::timestamp without time zone)

成效是十分可觀的——初始成本將為 0、總成本直接從 4,200 億降為 138 萬。

連結時的內外關係

理論上 RedShift 要能主動發現到表格大小的差異，並以資料量較大的表格作為外側、較小的表格為內側。若沒有發生，則需要對表格跑 ANALYZE 以重建其中繼資料。

高成本的排序運算

如果有排序運算的成本很高，那反映了兩種狀況：

第一種很廢話，即當前進行排序的欄位並不是排序索引，因此有對應的成本。

另一種則是資料並沒有照排序索引進行排序——排序索引並不會保證資料隨時都照著這個規則排序，當遇到這個狀況時則需要對表格跑 VACUUM 以進行資源回收與重建索引。

高成本的廣播運算子

如果有高成本的運算帶著以下任一廣播（broadcast）運算子，則需要重新安排分佈金鑰的安排：

• DS_BCAST_INNER 表示表格會被廣播給所有的節點，若是小表格的話就先無視沒差，不過在大表格的時候傳輸成本將會十分可觀
• DS_DIST_ALL_INNER 表示資料會集中在一個節點上運算
• DS_DIST_BOTH 表示發生大量的資料重新分配