使用 EXPLAIN 調校 SQL 查詢

遇到 SQL 查詢效能不彰時，除了 review SQL 以外，使用 EXPLAIN 可以了解效能低落的原因。

這篇文章介紹如何使用這個指令及解讀回傳的結果。

Query Process

要解釋 EXPLAIN 前，需要了解 PostgreSQL 在查詢的內部流程。主要有下列步驟:

• Parser: 如同編譯器一樣，PostgreSQL 需要將人看的懂的 SQL 翻譯成它能執行的語言 (query tree)
• Rewrite: 調整 query tree，分解 views 到對應的 table
• Planner: 由 query tree 規劃該如何快速地找到資料。這個步驟跟 query 效能直接相關
• Execute: 執行 query

使用 EXPLAIN 能讓我們知道 query 會如何執行，接下來說明它的用法。

EXPLAIN

基本的用法是在 SQL 最前面加上 EXPLAIN，則查詢結果回傳 query plan。也就是預估每次下 SQL 查詢時，背後的 planner 讀取多少 row, index 並且花了多少資源。

假如要真的執行 SQL，可使用 EXPLAIN ANALYZE，則 PG 會執行並分析此 SQL。

以下面 SQL 為例:

EXPLAIN SELECT *
FROM employees
JOIN public.address ON address.employee_id = employees.id
WHERE first_name = 'Alex'

回傳結果會是

逐一分析結果:

• Hash Join (cost=20.18..43.69 rows=5 width=120) 執行 JOIN 的預估，此操作需要將一張表讀進 memory 再根據條件找出記錄
  • cost 是猜測此 SQL 要花多少時間執行。裡面包含二個數字: 前者為隨機找二筆記錄需要的時間；後者表示逐一檢視記錄的總成本
  • rows 為猜測需要掃描多少筆記錄
  • width 為猜測平均每筆記錄的長度 (bytes)
• Hash Cond: (address.employee_id = employees.id) 使用 employee id 去 address table 找對應的記錄
• -> Seq Scan on address (cost=0.00..20.70 rows=1070 width=48) 逐一掃描 address 的成本
• -> Hash (cost=20.12..20.12 rows=4 width=72)
• -> Seq Scan on employees (cost=0.00..20.12 rows=4 width=72) 逐一掃描 employees 的預估成本
• Filter: (first_name = ‘Alex’::text) 使用 first_name 當查詢條件

在 query planner 階段，PostgreSQL 會比較隨機找出記錄和逐一掃描 heap file 需要的成本。

如果使用 index，會從 B+ tree root node 隨機找 child page 再搜尋對應的 heap file。一般來說，會比逐一找記錄來的快。

假如使用 pgadmin 的話有按鈕可以執行 EXPLAIN

它也可以用圖形的方式呈現 query plan

要注意使用 GUI 就不用在 SQL 加上 EXPLAIN

除了 Join 的 query plan, PostgreSQL 官方文件有列出各種 EXPLAIN 結果。如:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                 QUERY PLAN
-------------------------------------------------------------------​----------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: (unique1 = 42)

此 SQL 使用 index column 查詢，query planner 呈現 index scan 結果。

在調校 SQL query 時，可藉由 EXPLAIN 分析需要的成本及過程。

Reference

• Using EXPLAIN
• SQL and PostgreSQL: The Complete Developer’s Guide