PostgreSQL pg_stat_statements 擴充套件安裝與配置完整指南

功能簡介

pg_stat_statements 為 PostgreSQL 核心擴充套件，自動追蹤所有 SQL 執行統計 (呼叫次數、總耗時、平均耗時、資料列數、快取命中率、共享緩衝區讀寫等)，是查詢效能調優、發現慢查詢的核心工具。

安裝步驟 (原始碼編譯部署範例)

1. 取得原始碼：下載與 PostgreSQL 版本對應的原始碼包 (如 `postgresql-9.3.2.tar.gz`)，解壓。
2. 進入擴充套件目錄編譯安裝：

   cd /home/postgres/postgresql-9.3.2/contrib/pg_stat_statements
   make && make install

   成功後，`$PGHOME/share/extension/` 下會出現 `pg_stat_statements--1.1.sql` 等控制檔。

3. 資料庫內建立擴充套件：

   CREATE EXTENSION pg_stat_statements;

配置參數 (postgresql.conf)

# 必須：預載入共享庫，需重啟生效
shared_preload_libraries = 'pg_stat_statements'

# 選項：最大追蹤陳述式數 (預設 5000，依工作負載調大)
pg_stat_statements.max = 10000

# 選項：追蹤層級
# top   - 僅最上層陳述式 (預設)
# all   - 包含巢狀函數/觸發器內部陳述式
# none  - 停用
pg_stat_statements.track = all

# 9.3+ 不再需要 custom_variable_classes 參數

修改後必須重啟 PostgreSQL 服務 使 `shared_preload_libraries` 生效。

核心檢視欄位說明 (pg_stat_statements view)

常用分析查詢

Top 10 總耗時查詢

SELECT queryid, calls, total_time, mean_time, rows, 
       substring(query, 1, 200) AS short_query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Top 10 平均耗時查詢 (高頻慢查詢)

SELECT queryid, calls, mean_time, total_time,
       substring(query, 1, 200) AS short_query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_time DESC
LIMIT 10;

快取命中率分析

SELECT queryid,
       shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_ratio,
       calls, mean_time
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000
ORDER BY hit_ratio ASC
LIMIT 20;

維護操作

• 重置統計：`SELECT pg_stat_statements_reset();` (需超級用戶或 `pg_stat_statements` 角色)。
• 定期快照：建立排程作業將視圖內容寫入歷史表，支援趨勢分析。
• 版本升級：`ALTER EXTENSION pg_stat_statements UPDATE;` (次要版本間)。

注意事項

• 啟用會增加微量 CPU/記憶體開銷 (通常 < 1-2%)，生產環境普遍建議開啟。
• 查詢文本正規化會移除常數，相同結構不同參數的查詢會合併統計。
• 搭配 `auto_explain` (自動記錄慢查詢執行計畫) 可構建完整觀測體系。