用 pg_stat_statements 分析慢查詢

當你在做實際專案時，可能會有成千上萬的使用者同時跟你的應用程式互動。他們會送出查詢到資料庫、加資料、讀資料、更新資料... 然後你就發現你的伺服器開始「哀嚎」了。這就是你的查詢根本不夠優化的警訊。有時候那些「紙上談兵」看起來很美的查詢，實際上卻是效能災難。這時候 pg_stat_statements 就要上場啦。

pg_stat_statements 可以讓你：

1. 追蹤慢查詢。
2. 知道每個查詢被執行了幾次。
3. 了解它們總共花了多少時間。
4. 看到查詢的平均執行時間。
5. 避免犯下重寫整個應用程式的致命錯誤！

認識 pg_stat_statements 的結構

啟用這個擴充套件後，你的資料庫裡會多出一個特別的 view 叫 pg_stat_statements。這裡會存所有查詢的執行資料。先來看看裡面有什麼：

SELECT * FROM pg_stat_statements LIMIT 1;

結果可能長這樣（簡化版）：

簡單說明一下：

• query — SQL 查詢本身。
• calls — 這個查詢被執行了幾次。
• total_time — 這個查詢總共花了多少時間。
• rows — 查詢回傳的資料列數。
• shared_blks_read — 讀取了多少個 block（如果沒用 cache 就會去碰硬碟）。

結果分析

現在 pg_stat_statements 開好了，來看看怎麼找出慢查詢。

最慢的查詢

要找出哪些查詢最花時間，可以用這個查詢：

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

這裡：

• mean_time — 單次查詢的平均執行時間（total_time / calls）。
• ORDER BY total_time DESC — 依照總執行時間排序。

最常被執行的查詢

有時候問題不是慢查詢，而是某些查詢被執行太多次。像這樣：

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

查詢優化

1. 用索引

如果你發現某些欄位的查詢很慢，先檢查有沒有對這些欄位加 index。假設你有一個 students 表，資料很多，常常查 last_name，那就該加個 index：

CREATE INDEX idx_students_last_name ON students (last_name);

2. 重寫查詢

假設你發現像 SELECT * FROM orders WHERE amount > 1000 這種查詢太慢。很可能你不需要「全部欄位」，只要選你要的就好：

SELECT order_id, amount FROM orders WHERE amount > 1000;

清除統計資料

有時候你想只看新的結果（比如優化後），就要清掉 pg_stat_statements 裡的資料。這樣做：

SELECT pg_stat_statements_reset();

就像你計算機上的「重設」按鈕。執行後統計資料會重新開始收集。

找出有問題的查詢

想像你是大學的資料庫管理員，學生們都在抱怨他們的個人頁面載入超慢。你決定查查 pg_stat_statements：

步驟 1：找出最慢的查詢

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

你看到像 SELECT * FROM students WHERE status = 'active' 這種查詢竟然要 30 秒。哇，這不行，得馬上處理。

步驟 2：檢查索引 分析 students 表後，你發現 status 欄位沒 index。馬上補一個：

CREATE INDEX idx_students_status ON students (status);

步驟 3：檢查結果 優化後你再查一次 pg_stat_statements，發現查詢只要 0.5 秒。贏啦！

用 pg_stat_statements 常見錯誤

有時候管理員在分析查詢時會犯這些錯：

1. 沒啟用擴充套件。如果你忘了在 shared_preload_libraries 加 pg_stat_statements，根本不會收集統計資料。
2. 忽略索引。查詢慢不一定是查詢本身，可能只要加對 index 就解決了。
3. 沒重設統計資料。如果你沒執行 pg_stat_statements_reset()，舊資料會干擾你分析現在的狀況。

在工作中用 pg_stat_statements 就像資料庫的 GPS 導航：它會告訴你卡在哪裡，還會給你繞路建議。把這工具設好，你的資料庫效能就能大大提升啦！