想抓PostgreSQL里的慢SQL？pg_stat_statements基础黑匣子和pg_stat_monitor时间窗，谁能帮你更准揪出性能小偷？

1. pg_stat_statements：基础性能统计模块

1.1 什么是pg_stat_statements？

pg_stat_statements是PostgreSQL官方提供的核心性能监控模块，用于跟踪所有SQL语句的计划与执行统计信息。它能帮你回答：

• 哪些SQL执行次数最多？
• 哪些SQL总执行时间最长？
• 哪些SQL的缓存命中率最低（IO开销大）？
• 某条SQL的平均执行时间是多少？

简单来说，它是PostgreSQL性能优化的“黑匣子”——记录所有SQL的运行痕迹，帮你定位瓶颈。

1.2 安装与启用

pg_stat_statements需要预加载（因为它需要共享内存），安装步骤分3步：

步骤1：修改配置文件

编辑postgresql.conf（通常在/var/lib/postgresql/17/main/或$PGDATA目录）：

# 1. 预加载模块（必须）
shared_preload_libraries = 'pg_stat_statements'

# 2. 启用查询ID计算（必须，用于唯一标识相同结构的查询）
compute_query_id = on

# 3. 可选配置（根据需求调整）
pg_stat_statements.max = 10000       # 最多跟踪10000条不同的SQL
pg_stat_statements.track = all       # 跟踪顶级+嵌套语句（比如函数内的SQL）
pg_stat_statements.track_utility = on # 跟踪工具命令（如CREATE TABLE）

步骤2：重启PostgreSQL

修改配置后需要重启数据库使生效：

sudo systemctl restart postgresql

步骤3：创建扩展

登录数据库（如psql -U postgres），执行以下命令启用扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1.3 核心配置参数解析

1.4 关键视图与字段说明

pg_stat_statements提供两个核心视图：

1.4.1 pg_stat_statements：SQL统计详情

这个视图是性能分析的核心，每一行对应一条不同结构的SQL（用queryid标识）。关键字段如下：

示例：计算缓存命中率（越高越好）：

SELECT 
  query,
  calls,
  total_exec_time,
  100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

1.4.2 pg_stat_statements_info：模块自身统计

这个视图只有1行，记录模块的运行状态：

1.5 实际使用示例

示例1：找最耗时的前5条SQL

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

结果解读：total_exec_time最高的SQL是性能优化的优先目标（比如总时间10秒的SQL，即使平均时间短，但执行次数多也会拖慢整体性能）。

示例2：找缓存命中率低的SQL

SELECT 
  query,
  calls,
  100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE hit_percent < 90  -- 命中率低于90%
ORDER BY hit_percent ASC;

优化思路：命中率低说明SQL经常读磁盘，可能需要添加索引或加大shared_buffers（数据库缓存）。

示例3：重置统计信息

如果统计信息太旧（比如测试环境），可以重置：

-- 重置所有统计（仅超级用户可执行）
SELECT pg_stat_statements_reset();

-- 重置某条SQL的统计（需指定queryid）
SELECT pg_stat_statements_reset(0, 0, '1234567890'); -- 0表示不限制用户/数据库，queryid替换为实际值

2. pg_stat_monitor：增强型性能监控工具

pg_stat_statements是基础，但有个明显局限——统计是累计的（比如某条SQL的总执行时间是从启动到现在的总和），无法看到时间维度的变化（比如“最近1小时这条SQL的执行时间是否变长？”）。

pg_stat_monitor是Percona开发的增强版模块，解决了这个问题，适合持续监控。

2.1 核心特性（对比pg_stat_statements）

2.2 安装与配置

pg_stat_monitor需要从Percona仓库安装（或编译源码）：

# 安装Percona仓库（以Debian/Ubuntu为例）
sudo apt install percona-postgresql-17-pg_stat_monitor

修改postgresql.conf：

shared_preload_libraries = 'pg_stat_monitor'  # 替换或新增
pg_stat_monitor.interval = 60                 # 时间窗口大小（秒，默认60）
pg_stat_monitor.max = 10000                   # 最多跟踪10000条SQL

重启数据库后创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;

2.3 常用查询示例

示例：看最近1小时每条SQL的平均执行时间

SELECT 
  query,
  sum(calls) AS total_calls,
  avg(mean_exec_time) AS avg_mean_exec_time,
  time
FROM pg_stat_monitor
WHERE time >= NOW() - INTERVAL '1 hour'
GROUP BY query, time
ORDER BY avg_mean_exec_time DESC;

3. 持续优化工作流：从监控到优化

性能优化不是“一次性操作”，而是持续循环。结合pg_stat_statements和pg_stat_monitor，流程如下：

3.1 步骤1：定位瓶颈SQL

用pg_stat_statements找总执行时间最长或缓存命中率最低的SQL；用pg_stat_monitor看时间维度的性能变化（比如某条SQL的执行时间从10ms涨到100ms）。

3.2 步骤2：分析执行计划

对瓶颈SQL运行EXPLAIN ANALYZE，看是否缺少索引、是否全表扫描：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

结果解读：如果看到Seq Scan on orders（全表扫描），说明缺少customer_id的索引。

3.3 步骤3：优化SQL或索引

比如给orders表的customer_id添加索引：

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

3.4 步骤4：验证优化效果

优化后，用pg_stat_statements重新查询该SQL的total_exec_time和shared_blks_read，看是否下降；用pg_stat_monitor看时间窗口内的执行时间是否恢复正常。

4. 最佳实践与注意事项

1. 定期重置统计信息：比如每周重置一次（SELECT pg_stat_statements_reset();），避免旧数据干扰分析。
2. 设置合适的pg_stat_statements.max：如果dealloc值很大（看pg_stat_statements_info），说明max太小，需要增大（比如从5000改到10000）。
3. 开启track_planning谨慎：track_planning会跟踪计划时间，但会增加性能开销，仅在需要分析计划问题时开启。
4. 权限控制：pg_stat_statements的query字段包含SQL文本，仅超级用户和pg_read_all_stats角色能看其他用户的SQL（避免敏感信息泄露）。

5. 课后Quiz

问题1：如何用pg_stat_statements找出最耗时的前3条SQL？

答案：

SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;

问题2：pg_stat_statements.track设置为all会跟踪哪些语句？

答案：会跟踪顶级语句（如客户端直接执行的SQL）和嵌套语句（如函数或存储过程内的SQL）。

问题3：如果pg_stat_statements_info中的dealloc值很大，说明什么？

答案：说明pg_stat_statements.max设置太小，导致很多SQL条目被丢弃，需要增大max值。

6. 常见报错与解决方法

报错1：ERROR: could not access file "pg_stat_statements": No such file or directory

原因：没有安装pg_stat_statements扩展，或没有预加载模块。
解决：

1. 安装扩展（如apt install postgresql-17-pg-stat-statements）。
2. 修改shared_preload_libraries为pg_stat_statements并重启数据库。

报错2：ERROR: permission denied for function pg_stat_statements_reset

原因：当前用户没有执行pg_stat_statements_reset的权限。
解决：

1. 切换到超级用户（如postgres）执行。
2. 给用户授予权限：GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO your_user;

报错3：ERROR: pg_stat_statements must be loaded via shared_preload_libraries

原因：没有在postgresql.conf中预加载pg_stat_statements。
解决：修改shared_preload_libraries并重启数据库。

7. 参考链接

1. PostgreSQL官方文档：pg_stat_statements模块
   www.postgresql.org/docs/17/pgs…
2. Percona文档：pg_stat_monitor模块
   docs.percona.com/pg-stat-mon…
3. PostgreSQL配置参数：compute_query_id
   www.postgresql.org/docs/17/run…