用 pg_stat_statements 追蹤慢查詢

pg_stat_statements 是 PostgreSQL 內建的擴充套件，可以讓你追蹤資料庫裡到底發生了哪些查詢，以及它們的表現。基本上，它就像一個安靜但很會觀察的小幫手，會記錄每一步：哪些 SQL 查詢被執行、花了多少時間、被執行了幾次、還有對系統造成多大負擔。

為什麼要這樣做？首先，這樣可以找出有問題的查詢。有時候資料庫卡卡的，不是因為一個壞蛋，而是因為有十幾個一樣重的查詢一直被執行。再來，這些統計數據可以讓你看到到底哪些查詢在吃資源——CPU、記憶體、硬碟。你還可以知道 index 有沒有照你預期在運作：有些地方可能根本沒用到 index，有些地方則是 index 不夠。

pg_stat_statements 讓你不用猜，直接看到實際數字——然後根據這些數字來做判斷和優化。

怎麼找出慢查詢？

現在進入重點啦！用 pg_stat_statements 這張表，我們可以找出那些執行很久或是讓伺服器很忙的查詢。

主要想法：

pg_stat_statements 表裡的每一行都代表一個查詢的統計資料。查詢會根據它們的文字（也就是 query 欄位）分組，然後會計算出這些指標：

• total_time — 查詢執行的總時間（毫秒）。
• calls — 查詢被執行的次數。
• mean_time — 查詢平均執行時間（total_time / calls）。
• rows — 查詢回傳的資料列數。

簡單分析範例

我們來找出平均執行時間最久的查詢：

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

這個查詢會顯示執行最久的 TOP-5 查詢。注意 mean_time 欄位：如果那裡的數字超過 500-1000 毫秒，就是該優化這些查詢的警訊啦。

慢查詢分析範例

我們來看個例子：

這是剛剛那個查詢的結果：

我們看到什麼？

查詢 orders 表：執行次數很少（只有 10 次），但每次都拉出 1 萬筆資料。大概是這張表很大，而且查詢沒用到 index。

查詢 products 表：被呼叫了好幾千次，可能是在應用程式的迴圈裡。每次只回傳 100 筆資料，但因為太常被執行，這查詢也可能是個問題。

查詢 customers 表：執行很快（543 毫秒），但呼叫次數太多了。

優化慢查詢

現在我們找到有問題的查詢了，接下來要用 EXPLAIN ANALYZE 來看它們的執行計畫。像是 orders 表的查詢：

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'new';

我們可以看到什麼？

Seq Scan：如果查詢用的是順序掃描，那就該加個 index：

CREATE INDEX idx_orders_status ON orders (status);

過濾問題：如果查詢拉出太多資料，檢查一下查詢內容。也許該加點條件或是限制回傳筆數：

SELECT * FROM orders WHERE status = 'new' LIMIT 100;

查詢執行時間統計

有時候問題查詢不會那麼明顯。像是常常呼叫 function 或 subquery 的查詢。這時可以看 total_time 欄位：

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

這個查詢會顯示總執行時間最「貴」的查詢。

索引優化

很多慢查詢都是因為缺少必要的 index。用 pg_stat_statements 來看哪些查詢沒用到 index。如果你看到很多查詢都用一樣的過濾條件（像是 status 欄位），但速度很慢，就加個對應的 index：

CREATE INDEX idx_orders_status ON orders (status);

加完之後再用 EXPLAIN ANALYZE 看看查詢效能有沒有變好。

用 pg_stat_statements，你可以有效追蹤查詢效能，找出「瓶頸」，然後讓你的資料庫跑得更順。記得，越早開始分析查詢，整個系統就越容易優化！