用 EXPLAIN ANALYZE 來測量 SQL 查詢實際執行時間

如果 EXPLAIN 這個指令讓你像是在看水晶球，能預測 PostgreSQL 打算怎麼執行查詢，那 EXPLAIN ANALYZE 就是讓你變成真正的偵探，直接查出 實際發生了什麼事。

EXPLAIN 跟 EXPLAIN ANALYZE 的主要差異：

EXPLAIN —— 這是理論，顯示 PostgreSQL 預計怎麼執行查詢。你會看到像是預估的資料列數 (rows) 跟執行成本 (cost) 這種東西。

EXPLAIN ANALYZE —— 這是實戰。PostgreSQL 真的會執行查詢，然後顯示：

• 每個步驟實際處理了多少資料列。
• 每個操作實際花了多少時間。
• 跟計畫裡的預估值（rows 跟 cost）做比較。

舉例：如果你的查詢預計只會處理 100 筆資料，結果實際上處理了 10,000 筆，EXPLAIN ANALYZE 馬上就會把這個小秘密攤在陽光下！

基本語法跟用法

跟 EXPLAIN 一樣，EXPLAIN ANALYZE 超簡單。只要在 EXPLAIN 指令後面加上 ANALYZE 就好。

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

PostgreSQL 會做這些事：

• 它會執行查詢。
• 把每個執行計畫的步驟都記錄下來，包含實際的數據。
• 回傳完整的查詢執行過程說明。

EXPLAIN ANALYZE 會給你哪些資料？

操作的實際執行時間：

• Actual Start Time：這個操作什麼時候開始。
• Actual End Time：這個操作什麼時候結束。

總共處理了多少資料列：

這可以幫你判斷計畫裡的預估值（rows）準不準。

Buffer 資訊：

看磁碟跟記憶體的 buffer 是怎麼被用的。

EXPLAIN ANALYZE 的使用範例

來看個實際例子。我們有一個 students 資料表，裡面存著學生的資料：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

我們來查詢年齡大於 20 歲的學生：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

查詢結果範例：

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

來拆解一下這個結果：

• Seq Scan —— PostgreSQL 用的是順序掃描整個資料表。
• cost=0.00..14.00 —— 這是預估的操作成本。
• rows=2 —— PostgreSQL 預計這個查詢會回傳 2 筆資料（這次猜對了！）。
• actual time=0.025..0.026 —— 這個操作實際花的時間（單位是毫秒）。
• Rows Removed by Filter: 2 —— 有兩筆資料被過濾掉，因為不符合 WHERE 條件。

理論 vs. 實際

這就是 EXPLAIN ANALYZE 的魔力：它讓你看到查詢實際是怎麼被執行的，還能跟理論上的執行計畫做比較。

我們再來看一個複雜一點的例子。

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

查詢結果範例：

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

我們看到：

1. PostgreSQL 在 0.057 毫秒內完成查詢。
2. 只有一筆資料（rows=1）符合 WHERE 條件。
3. 有三筆資料被過濾掉（Rows Removed by Filter: 3）。

總結

用 EXPLAIN ANALYZE 你可以找出瓶頸，也能知道怎麼優化查詢。舉例來說：

• 如果 Seq Scan 太「重」，也許該加個 index 了。
• 如果 PostgreSQL 的預估跟實際差很大，記得檢查一下資料表的統計資料（ANALYZE）或 index 結構。