Query Plan 案例入門

只有實戰才能累積能力的技術

本文提供一個簡單案例作為識讀 Query Plan 的入門，理解資料庫系統的行為，並且回饋給自己擬出改善策略。Query Plan 的識讀和改善，資料形態與開發文化的掌握是很關鍵的要素，但都是只有實際的職場才能接觸到的情境，是難以取代的「真人智慧」。

有關為何會有查詢計畫，請參閱另一篇「略懂查詢處理的老規矩」。

EXPLAIN ANALYZE

• 列出查詢計畫及實際執行的結果，找出效能瓶頸。
• 所有資料庫使用者都必須學習的技術，自己下的查詢自己檢查。
• 進階使用者的技術分野。
• 只有實戰才能累積能力的技術。
• 查詢計畫可能會因為資料分佈不同而改變。
• 一定要在實際的資料上評估。
• 不同資料庫的查詢邏輯不同，調教方式不能移植。

以下為入門案例，初步瞭解實際情況之後，再閱讀其他理論資訊，會更有感覺。

改善前

問題分析

1. 總執行時間為: 14329.521 (actual time)* 1 (loops) = 14329.521 ms 約 14 秒。
2. 兩個步驟各佔約 7 秒: 7446.517 * 1 約 7.4 秒，458.859 * 15 約 6.9 秒。

Nested Loop Left Join (7.4 秒)

依階層計算可發現瓶頸在 Bitmap Index Scan on pg060_idx1，489.915 * 15 約 7.3 秒。既然已使用索引查詢，就找出 pg060_idx1 的內容。從上一層 Bitmap Heap Scan on pg060 可知所屬的 Table：

Indexes:
    "pg060_pk" PRIMARY KEY, btree (agnt_id, vrfy_wkym, sys_code)
    "pg060_idx1" btree (div_no, vrfy_wkym)

其引用的 pg060_idx1 只需要參考第 2 欄位的鍵值，但依舊需要掃描完第 1 鍵值，浪費了很多時間。

Index Scan using pg059_pk on pg059 b (6.9 秒)

情境同上，不適當的複合欄位索引造成索引未能加速查詢。

改善方式

總之經過許多猜想之後，直接跳到結論是建立新的索引：

CREATE INDEX pg059_idx2 ON pg059 (vrfy_wkym);
CREATE INDEX pg060_idx2 ON pg060 (vrfy_wkym);

由查詢計畫可知，僅需要參考一個欄位即可，那就建立單欄位的索引；複合欄位索引，除非查詢條件真的非常符合，否則空間和時間效益都不大(如此案例就是如此)。

不是加了索引就會快唷！！！

你可以參考 PostgreSQL 使用手冊：多欄位索引。

改善後

結論

本案例未附上原始查詢語句，因為在分析過程中，不一定需要，關注太多反而是雜訊而沒有幫助。此例的原始語句是 View 的組合查詢，但其實我就沒有去拆解原語句，直接看最後資料庫的查詢計畫。

無論你怎麼輸入查詢語句，查詢計畫才是真的，那才資料庫實際的執行內容。

原始查詢語句用於重構資料邏輯。如果查詢計畫已無改善空間，才回到原始語句，以尋求資料邏輯的改善。（再往上就是應用邏輯或老闆邏輯了）

小練習：https://www.db-fiddle.com/f/awN5Kj5PGoBm1Q4Z5yqjUT/1
多欄位索引的練習，可以線上體驗跑跑看。

延伸問題

「你有觀察過自己的查詢（工作）計畫嗎？」

「面對不同的資料量（工作量），你的查詢計畫有不一樣嗎？」

此篇的口說內容：