入門 pg_stat_statements：擴充套件的安裝與設定

在 PostgreSQL 裡，pg_stat_statements 擴充套件是用來收集查詢統計資料的工具。它可以讓你看到哪些查詢最常被執行、哪些查詢最花時間，以及資料庫資源到底用得好不好。你不用再一條一條查詢用 EXPLAIN 慢慢看，直接就能掌握資料庫效能的全貌。

用 pg_stat_statements 的好處：

即時監控：你可以馬上看到哪些查詢正在讓資料庫爆炸。

整體效能分析：所有查詢的資訊都能看到，不用只看你手動挑出來的那些。

找出慢查詢：很容易就能知道哪些查詢最拖時間。

發現重複查詢：可以優化 cache，或是幫熱門查詢加 index。

安裝與設定 pg_stat_statements

現在你知道 pg_stat_statements 有什麼用，接下來我們一步一步來看怎麼安裝和設定它。

1. 檢查 PostgreSQL 是否支援。 先確定你的 PostgreSQL 有支援 pg_stat_statements 這個擴充套件。從 PostgreSQL 9.2 開始，這個擴充套件就有內建了。要檢查有沒有裝，可以執行：

SELECT extname FROM pg_extension;

如果 pg_stat_statements 沒有在清單裡，可能是你的管理員還沒裝它。

裝好又啟用的話，應該會長這樣：

2. 加入擴充套件。

要啟用 pg_stat_statements，你要把它加到 PostgreSQL 的預載入 library 清單裡。這要在 postgresql.conf 設定檔裡改。

步驟：

1. 找到 postgresql.conf 檔案。通常在 PostgreSQL 的 data 目錄裡。
2. 打開來編輯。
3. 加上或修改這一行：

   shared_preload_libraries = 'pg_stat_statements'

為什麼要這樣？因為 pg_stat_statements 需要預先載入，才能在系統層級追蹤查詢。

4. 存檔後重啟 PostgreSQL server，這樣設定才會生效。Linux 下可以用這個指令：

   sudo systemctl restart postgresql
   

如果你是在本機開發或測試，直接重啟 server 也可以。

3. 在資料庫裡建立擴充套件。 PostgreSQL server 重啟後，我們可以在指定的資料庫裡建立 pg_stat_statements 擴充套件。用 psql 或其他工具連進資料庫，執行：

CREATE EXTENSION pg_stat_statements;

如果沒出錯，這行指令就算成功。現在 pg_stat_statements 已經在你的資料庫啟用了。

4. 設定 pg_stat_statements 參數。

裝好擴充套件後，建議調整一下它的參數，這樣收集統計資料才會比較準。主要參數都可以在 postgresql.conf 裡設。

主要參數

• pg_stat_statements.track
• 決定要追蹤哪些查詢。
• 可選值：
  • all — 追蹤所有查詢（debug 跟分析時推薦）。
  • top — 只追蹤 top-level 查詢。
  • none — 不追蹤。
• 設定範例：

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • 設定統計裡最多要存幾筆查詢。
  • 預設是 5000。
  • 如果你的系統查詢很多，可以調大一點，例如：

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • 決定 server 重啟時要不要保留統計資料。
  • 可選值：on 或 off。
  • 建議設 on：

    pg_stat_statements.save = on
    

改完參數後，記得再重啟一次 PostgreSQL server。

檢查 pg_stat_statements 有沒有在跑

現在擴充套件已經裝好又設定好了，來看看它有沒有在收資料。要看查詢統計，執行這個查詢：

SELECT
    queryid,        -- 查詢的唯一識別碼
    query,          -- 查詢內容
    calls,          -- 查詢被執行的次數
    total_time,     -- 執行總時間（毫秒）
    rows            -- 查詢回傳的資料列數
FROM pg_stat_statements
ORDER BY total_time DESC;

這些欄位是什麼意思？

• queryid：查詢的唯一識別碼，找參數不同但內容一樣的查詢很方便。
• query：執行過的 SQL 查詢內容。
• calls：這個查詢被執行了幾次。
• total_time：總共花了多少時間（所有 calls 加起來）。
• rows：查詢總共回傳了幾筆資料。

舉例來說，如果你看到某個查詢 calls = 100 而 total_time = 50000（50 秒），這個查詢就超級拖慢系統，該優化了！

pg_stat_statements 的常見用法

1. 找最慢的查詢。 要找最花時間的查詢，把結果用 total_time 排序：

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

2. 找最常被執行的查詢。 要找最常被執行的查詢，用 calls 排序：

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

3. 分析 index 使用狀況。 如果你發現很多查詢都很慢，記得檢查有沒有用到 index。像是有 WHERE 條件的查詢，沒 index 很容易拖慢效能。

清除 pg_stat_statements 的資料

有時候你會想把統計資料清空，重新開始分析。可以用這個指令：

SELECT pg_stat_statements_reset();

清空後，所有統計資料都會歸零，重新開始收集。

實用小技巧

限制統計資料的數量：如果你的系統查詢量超大，pg_stat_statements.max 設合理一點，避免太吃資源。

定期清空統計資料：每次要分析效能前，建議先清空，這樣不會混到舊資料。

特別注意慢查詢：就算某些查詢很少執行，只要很慢，也可能讓資料庫卡住。

現在你已經會安裝、設定、還有用 pg_stat_statements 來分析查詢效能了。下一堂課我們會更深入教你怎麼用它找出慢查詢，還有怎麼優化查詢。