pg_stat_statements：找出"哪条 SQL 拖慢了 PostgreSQL"

起因

PG 慢。CPU 跑满。但具体是哪条 query / 哪个业务模块？
slow query log 能记慢的，但漏掉"单次快、调用极多次"的；也不容易聚合
看"Top N 总耗时 query"。

pg_stat_statements 是 PG 自带 extension，按"query 模板" 累计每条
SQL 的总耗时 / 调用次数 / 平均时间。性能分析的瑞士军刀。

启用

postgresql.conf：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all          # all / top / none
pg_stat_statements.track_utility = on

需要重启 PG：

sudo systemctl restart postgresql

加载 extension（一次性）：

CREATE EXTENSION pg_stat_statements;

经典 Top 查询

Top 10 总耗时 query

SELECT
    substring(query, 1, 100) AS query_short,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 1) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

输出：

query_short                                 | calls   | total_ms   | mean_ms | pct
SELECT * FROM orders WHERE user_id = $1     | 1234567 | 245678.12  | 0.20    | 32.5
SELECT * FROM products WHERE category = $1  | 234567  | 123456.78  | 0.53    | 16.3
UPDATE sessions SET ... WHERE id = $1       | 5678901 | 89012.34   | 0.016   | 11.8

pct 列告诉你"这条 query 占总 DB 时间 32.5%"。显著大头一目了然。

Top 10 平均最慢

SELECT
    substring(query, 1, 100),
    calls, round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 100               -- 调用 < 100 次的忽略（统计意义弱）
ORDER BY mean_exec_time DESC
LIMIT 10;

找"单次很慢但调用不多"的 query。

Top IO 消耗

SELECT
    substring(query, 1, 100),
    calls,
    shared_blks_hit, shared_blks_read,
    round((shared_blks_hit::numeric * 100 / (shared_blks_hit + shared_blks_read + 1)), 1) AS hit_pct,
    round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

shared_blks_read 高 = 经常从磁盘读（cache miss）。
hit_pct 低 = working set 不在 shared_buffers 内。

Top temp 文件用量

SELECT
    substring(query, 1, 100),
    calls, temp_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

temp_blks_written > 0 = work_mem 不够大，PG 用磁盘做 sort / hash。
调大 work_mem 或者改 query 让 sort 集少点。

让 query 文本可读

默认 query 字段把字面值替换成 $1 $2 等：

SELECT * FROM orders WHERE user_id = $1 AND status = $2

同一 query 模板不同参数算同一条。
分析 perspective 想看完整 SQL：

ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.save = on;
SELECT pg_reload_conf();

不过 $1/$2 模板化是 feature，便于聚合分析。

reset stats

SELECT pg_stat_statements_reset();
-- 清零，重新开始统计

定期 reset 让数据反映最近的负载。
跑性能优化前 reset → 跑业务 1 小时 → 看 stats。

跟 EXPLAIN ANALYZE 配合

pg_stat_statements 告诉你"哪条 query 是瓶颈"。
EXPLAIN ANALYZE 告诉你"这条 query 为什么慢"。
组合用：

-- 1. pg_stat_statements 找 Top 1
-- 2. 拿完整 SQL（替换 $1 等占位符）
-- 3. EXPLAIN ANALYZE 跑一遍

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

-- 4. 看 plan：
-- Seq Scan?  → 加索引
-- 估算 rows 偏差大?  → ANALYZE 表
-- 大 Hash join?  → work_mem 不够

生产监控仪表盘

Grafana + postgres_exporter 自动暴露 pg_stat_statements 数据：

# postgres_exporter 配置
queries:
  - pg_stat_statements_top:
      query: |
        SELECT queryid::text, query, total_exec_time, calls
        FROM pg_stat_statements
        ORDER BY total_exec_time DESC LIMIT 20
      metrics:
        - queryid: { usage: LABEL }
        - query: { usage: LABEL }
        - total_exec_time: { usage: GAUGE }
        - calls: { usage: GAUGE }

Grafana 仪表盘 panel：

Top 10 query by total time（pie chart）
Top 10 query by mean time
query 数 / 慢 query 数趋势线

真实 case：减少 90% DB 时间

我们一个 web app 用 pg_stat_statements 跑一周后看 Top 5：

1. SELECT COUNT(*) FROM users WHERE deleted_at IS NULL    | 38% pct
2. SELECT * FROM products JOIN ...                          | 24% pct
3. SELECT * FROM sessions WHERE expires > now()             | 18% pct
4. SELECT * FROM logs WHERE ...                              | 8% pct
5. ...

行动：

SELECT COUNT(*) FROM users WHERE deleted_at IS NULL：被首页用了
N 次。改成 cache 5 分钟（Redis），从 38% → 0.5%。
SELECT * FROM products JOIN ...：N+1 query。改成 prefetch +
单次 JOIN。
SELECT * FROM sessions ...：缺索引。CREATE INDEX ON sessions(expires)。

reset stats + 跑一周后 Top 5 完全变化，DB CPU 从 70% → 12%。

限制 + 注意

1. plan 不存

pg_stat_statements 只记 query 文本 + 计数 / 时间，不存 query plan。
要看 plan 仍要 EXPLAIN。

2. session 级变量影响

SET LOCAL work_mem = '256MB' 等 session 设置影响 query 但 stats
不区分。

3. 安全（敏感数据）

extension 默认 query 文本会被截短（参数 track_activity_query_size）。
完整 SQL 可能含 schema 名 / 表名 / 业务逻辑细节。访问 pg_stat_statements
要 superuser 权限。

4. 性能 overhead

收集统计本身极轻（< 1% CPU）。生产开它是标准做法。

几个查询模板我经常用

-- 找"慢但调用少" 可优化但可能 ROI 低
SELECT substring(query, 1, 80), calls, round(mean_exec_time::numeric, 2)
FROM pg_stat_statements
WHERE mean_exec_time > 1000 AND calls < 100
ORDER BY total_exec_time DESC LIMIT 20;

-- 找"非常频繁的快 query"（可能 cache 化）
SELECT substring(query, 1, 80), calls, round(mean_exec_time::numeric, 3)
FROM pg_stat_statements
WHERE calls > 10000 AND mean_exec_time < 5
ORDER BY calls DESC LIMIT 20;

-- 找写操作
SELECT substring(query, 1, 80), calls, round(total_exec_time::numeric, 2)
FROM pg_stat_statements
WHERE query ILIKE 'UPDATE%' OR query ILIKE 'INSERT%' OR query ILIKE 'DELETE%'
ORDER BY total_exec_time DESC LIMIT 20;

与 auto_explain

auto_explain 是另一个 extension，自动对慢 query 跑 EXPLAIN 并 log：

shared_preload_libraries = 'pg_stat_statements, auto_explain'
auto_explain.log_min_duration = 1000     # > 1s 自动 EXPLAIN
auto_explain.log_analyze = true

慢 query 自动留下 plan，不需要事后复现。
但 log_analyze 让 query 跑两次（一次正常 + 一次 ANALYZE），有性能开销。

踩过的坑

shared_preload_libraries 改了忘重启：extension 不加载。
CREATE EXTENSION 也会报错 library not loaded。
pg_stat_statements.max 太小：超过后老 query 被 evict。
显著负载多种 query 的应用建议 10000+。
reset 频繁 → 失去历史趋势。生产建议每月 reset 一次 + 之前
dump 数据到分析 DB。
生产 query 含动态 IN ：WHERE id IN ($1, $2, $3) 跟 IN ($1, $2)
被算作不同 query（IN 元素数不一样模板就不同）。考虑改成 = ANY($1)
传数组。
跨 DB：pg_stat_statements 是 DB 级别。每个 DB 都要单独装
extension + 查看。