比較分析 EXPLAIN ANALYZE 跟 pg_stat_statements

這時你可能會有個很合理的疑問：為什麼我們需要兩種不同的分析工具？到底大家比較常用 EXPLAIN ANALYZE 還是 pg_stat_statements？我們來拆解一下這兩種方法，各自的優缺點，以及什麼時候該用哪一個。

這些工具能解決什麼問題

EXPLAIN ANALYZE：這是用來深入分析單一查詢的工具。如果你想知道 PostgreSQL 怎麼執行你的查詢、用了哪些節點、處理了多少資料列、每個步驟花了多少時間，這就是你要用的。它能幫你回答：「為什麼我的這個查詢跑這麼慢？」

pg_stat_statements：這是比較高層級的監控工具，會給你所有查詢的效能資訊。如果你想看整體效能狀況：「我的資料庫裡哪些查詢最慢？」或「哪些查詢最吃資源？」這就是你的選擇。

什麼時候該用 EXPLAIN ANALYZE

EXPLAIN ANALYZE 就像你的 debug 工具，可以讓你搞懂 PostgreSQL 怎麼執行某個查詢。你可以在這些情境下用它：

針對單一查詢做優化 如果有人抱怨你的 app 某個頁面超慢，第一步你會找到那個查詢，然後用 EXPLAIN ANALYZE。這會顯示查詢的執行計畫和實際指標，比如執行時間和處理的資料列數。

選對 index 當你新增或修改 index 時，用 EXPLAIN ANALYZE 看 PostgreSQL 有沒有用到你設的 index。如果沒用到，可能你加的 index 根本沒幫上忙。

debug 複雜查詢 如果你寫了一個有很多 JOIN 或 WHERE 的複雜查詢，用 EXPLAIN ANALYZE 看實際的執行計畫，可以找出瓶頸，比如不必要的全表掃描（hello，Seq Scan）。

範例：用 EXPLAIN ANALYZE 優化查詢

-- 查詢很慢
SELECT *
FROM students
WHERE name = 'Alice';

-- 分析執行計畫
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

如果你看到用了 Seq Scan，可能就是忘了加 index：

-- 在 name 欄位加個 index
CREATE INDEX idx_students_name ON students(name);

-- 再檢查一次
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

什麼時候該用 pg_stat_statements

這個工具超適合拿來分析整個系統的效能。你可以在這些情境下用它：

production 監控 pg_stat_statements 會顯示一段時間內查詢的執行情況。你可以很快找到最慢的查詢，因為有個 total_time 欄位，會顯示每個查詢總共花了多少時間。

找出「重」查詢 想知道哪些查詢最常讓資料庫吃緊？你可以用記憶體讀取次數（shared_blks_hit）或處理的資料列數（rows）來排序。

找出高頻查詢 有時候不只是慢查詢會出問題，執行很頻繁的查詢也會拖垮系統。比如有個查詢每分鐘跑 100 次，就算只優化一點點，也能大幅減輕伺服器壓力。

範例：用 pg_stat_statements 找慢查詢

-- 看查詢統計
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

這個查詢會顯示最花時間的前五個查詢。

比較這兩種方法：差在哪？

兩個工具一起用才是王道

寫程式沒有什麼神奇按鈕可以一次解決所有問題。最好的做法就是兩個工具一起用。比如：

1. 先用 pg_stat_statements 找出最慢或最常跑的查詢。

2. 再用 EXPLAIN ANALYZE 研究這些查詢，找出為什麼會卡。

實戰範例：完整流程

-- 步驟 1：找出最慢的查詢
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- 步驟 2：分析這個查詢
EXPLAIN ANALYZE
<從上一步複製查詢過來>;

常見錯誤

用 EXPLAIN ANALYZE 跟 pg_stat_statements 時，新手常常會犯這些錯：

1. 忘記資料要夠真實。如果你在空表上分析查詢，EXPLAIN ANALYZE 的結果會很誤導。記得你的測試資料庫要跟實際資料量差不多。

2. 忽略監控的資源消耗。如果 production server 開了 pg_stat_statements extension，要確定它有設好，不會造成太大負擔。

3. 只看理論計畫沒看實際數據。記得 EXPLAIN 只會給你理論計畫，要用 EXPLAIN ANALYZE 才有真實數據。

現在你已經有所有必要的知識，不只可以解決慢查詢，還能預防它們發生。PostgreSQL 給你很強大的工具，只要會搭配用，就算系統很忙也能跑得很順！

1

問卷/小測驗

查詢優化，等級 42，課堂 4

未開放

查詢優化

查詢優化

開啟