查詢效能分析入門

先來個真實故事。想像你現在是某個大型電商平台的工程師，一切都很順，除了有個小問題：每月銷售報表跑超慢，慢到用戶都能再下單一次才跑完。你開始緊張，你主管也緊張，PostgreSQL 伺服器熱到快像水壺一樣冒煙，整個感覺快世界末日。現在想像一下，如果你能一招找到原因並解決它，是不是超爽？

查詢效能分析就像幫你的 PostgreSQL 做健康檢查。它能幫你找出「瓶頸」（痛點），修好之後，使用者體驗更好，系統資源也省一大堆。

PostgreSQL 查詢底層到底怎麼跑？

你寫個簡單查詢像這樣：

SELECT * FROM products WHERE price > 100;

PostgreSQL 不會直接衝去撈資料。它會先分析你的查詢，想想怎麼跑最有效率，然後才開始動作。

查詢執行的主要步驟：

1. Parsing（解析）。PostgreSQL 會檢查你的查詢有沒有語法錯誤，然後轉成中間格式。
2. Optimization（最佳化）。查詢最佳化器會評估幾種執行方式，挑一個「最便宜」（最快、最省資源）的。
3. Execution（執行）。伺服器照選好的計畫去撈資料。

什麼是「瓶頸」？

「瓶頸」就是查詢裡拖慢整體速度的那一段。可能是某個操作突然吃掉超多時間或資源。比如 PostgreSQL 本來可以用 index 快速查，結果卻做了整張表的全掃描（Seq Scan），查詢就會變慢。又或者資料比預期多一大堆，伺服器就得花很多時間在排序、合併、過濾。

這些情況就是所謂的瓶頸——你應該優先找出來優化的地方。

查詢效能分析工具有哪些？

PostgreSQL 有幾個超強的工具，幫你找出查詢的問題：

1. EXPLAIN 跟 EXPLAIN ANALYZE。 這些指令會告訴你 PostgreSQL 準備怎麼執行查詢，甚至可以直接執行並量測實際效能。

• EXPLAIN：顯示查詢執行計畫，不會真的執行查詢。
• EXPLAIN ANALYZE：真的執行查詢，並顯示實際執行計畫和時間數據。

用 EXPLAIN 的範例：

EXPLAIN SELECT * FROM products WHERE price > 100;

輸出：

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

這裡顯示查詢用了「Seq Scan」——也就是全表掃描，對大表來說超沒效率。

2. pg_stat_statements。 這是個額外的 extension，會記錄所有執行過的查詢。它會顯示：

• 伺服器上有哪些查詢在跑。
• 每個查詢花了多少時間。
• 查詢回傳多少列、吃掉多少資源。

要啟用 pg_stat_statements，你要：

1. 啟用 extension：

CREATE EXTENSION pg_stat_statements;

2. 設定 PostgreSQL 設定檔： 在 postgresql.conf 檔案加上：

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. 重啟 PostgreSQL。

現在你可以分析查詢了：

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

這會顯示總執行時間前五名的「最重」查詢。

3. 慢查詢日誌。 你可以設定 PostgreSQL，把執行超過某個時間（例如 1 秒）的查詢記錄到 log 檔。

在 postgresql.conf 設定：

log_min_duration_statement = 1000  # 時間單位是毫秒（1 秒）

現在慢查詢都會被記錄到 PostgreSQL 的 log 裡。

查詢效能分析的主要指標

分析查詢效能時，記得注意這幾個重點指標：

1. 執行時間。查詢花了多少時間，越快越好。
2. 資料列數量。如果查詢回傳或掃描的資料列比你預期多，可能有問題。
3. Index 使用狀況。該用 index 卻跑成全表掃描（Seq Scan），就該優化了。
4. Buffer 跟磁碟操作。查詢如果一直跟磁碟互動，會比直接用記憶體慢很多。

這些知識怎麼實際用？

範例 1：慢查詢

你寫了個查詢要找所有價格大於 100 的商品：

SELECT * FROM products WHERE price > 100;

你發現查詢超慢。用 EXPLAIN 一看：

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

問題：查詢做了全表掃描，因為 price 欄位沒 index。

解法：

建立一個 index：

CREATE INDEX idx_price ON products(price);

現在查詢會用 Index Scan：

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

範例 2：用 pg_stat_statements 找慢查詢

用這個指令：

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

你發現有個查詢花超多時間。用 EXPLAIN ANALYZE 看細節，修一修，查詢就變快了。

當你開始用 EXPLAIN、pg_stat_statements 跟其他工具，你的查詢會跑得更快，PostgreSQL 伺服器也會像瑞士鐘錶一樣精準。下一堂課我們會深入 EXPLAIN 的參數，像 cost、rows 跟 width，讓你能像看小說一樣讀懂查詢執行計畫。