pg_stat_statements 找慢查询：PostgreSQL 慢查询先查哪条，Top-20 只读脚本与判读顺序
一条只读 SQL 拿到按总耗时排序的前 20 条语句，附 PostgreSQL 17 实测输出、加索引前后 EXPLAIN 对比和判读顺序。
按 total_exec_time 总耗时排，不按单次：实测一条 count(*) 跑 30 次共 340.5ms、占 35.2%；加索引后单次从 13.4ms 降到 1.1ms。先确认 pg_stat_statements 在采集，再用 EXPLAIN (ANALYZE, BUFFERS) 看扫描方式，最后才动索引。
适用对象
- 排序看 total_exec_time，不看 mean：一条 11ms 但每分钟跑几千次的语句，比一条 2 秒的月报更值得先处理。
- pct_total_time 告诉你“优化它最多能省多少”，cache_hit_pct 告诉你瓶颈更像 CPU 还是磁盘。
- 脚本只读，设置了 5 秒超时，不会调用 pg_stat_statements_reset()。
- query_head 会带出语句骨架（参数已被替换成 $1），外发前仍要自己检查表名是否敏感。
确认 pg_stat_statements 已经在采集
先执行 `SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';`。没有结果，说明扩展没装；装扩展需要在 postgresql.conf 里把它加进 `shared_preload_libraries` 并重启实例，再在目标库执行 `CREATE EXTENSION pg_stat_statements;`。重启是生产变更，要走你们自己的审批，不要为了跑一次脚本临时去改。
执行账号给 `pg_read_all_stats` 角色就够了，不需要超级用户。没有这个角色时，别人的语句会显示成 `<insufficient privilege>`。
还要记一下上次重置时间：PG 14+ 可以查 `SELECT stats_reset FROM pg_stat_statements_info;`。统计是从那个时间点累计的，如果是三个月前重置的，排第一的可能是早已下线的旧接口。
Top-20 只读 SQL 输出什么
文件开头有 `SET statement_timeout = '5s'`，然后从 pg_stat_statements 关联 pg_database，按 total_exec_time 降序取前 20 行。用 `psql -X -A -F, -f pg-slow-query-topn-readonly-v1.0.sql > topn.csv` 可以直接存成 CSV。
| 列 | 含义 | 我怎么用 |
|---|---|---|
| calls / total_ms / mean_ms | 调用次数、累计耗时、平均耗时 | 先看 total_ms 排名，再看是“次数多”还是“单次慢” |
| stddev_ms | 耗时标准差 | 远大于 mean 说明时快时慢，常见于参数分布不均或锁等待 |
| pct_total_time | 占前述所有语句总耗时的百分比 | 优化上限：占 5% 的语句优化到 0 也只省 5% |
| cache_hit_pct | shared buffer 命中率 | 明显低于 99% 时，先怀疑数据量和内存，而不只是索引 |
| temp_blks_written | 写临时文件的块数 | 不为 0 通常是排序/哈希超过 work_mem |
| query_head | 语句前 80 字符，参数已规范化 | 用来定位代码位置；外发前检查表名 |
一次测试输出，以及我是怎么读的
测试库里我让 `select count(*) from orders where uid=$1` 跑了 30 次。脚本输出中它排第二：calls=30，total_ms=340.5，mean_ms=11.35，占 35.2% 的总时间，cache_hit_pct=100。排第一的是一次性的建数 INSERT，生产环境里对应的就是“批处理/导入”，这种要和业务确认是否是预期负载，不在优化清单里。
命中率 100% 而单次还要 11ms，说明数据都在内存里、只是读得太多。于是看执行计划：`EXPLAIN (ANALYZE, BUFFERS) select count(*) from orders where uid=7;` 显示 Seq Scan，Buffers: shared hit=1274，也就是每次把整张表扫一遍。
在 uid 上加索引后再看同一条：变成 Bitmap Index Scan + Bitmap Heap Scan，读取的块从 1274 降到约 202，执行时间从 13.4ms 降到 1.1ms（单次测量，测试机数据，仅说明方向）。
| 指标 | 之前 | 之后 |
|---|---|---|
| 扫描方式 | Seq Scan | Bitmap Index Scan → Bitmap Heap Scan |
| 读取块数 | shared hit=1274 | shared hit=200 read=2 |
| actual time | 13.4 ms | 1.1 ms |
拿到 Top-20 之后的五步
这几步我在每次排查里都按顺序走，前一步没确认就不进下一步。
从排名到改动
- 01
01
确认统计窗口
查 stats_reset，排除早已下线的语句。
- 02
02
剔除预期负载
批处理、备份（COPY ... TO stdout 就是 pg_dump）、建数，先和业务确认。
- 03
03
分清次数和单次
calls 大就先找应用侧缓存或合并请求，mean 大才去看计划。
- 04
04
看真实计划
在从库或低峰用 EXPLAIN (ANALYZE, BUFFERS)，注意 ANALYZE 会真的执行语句，写语句要包在事务里回滚。
- 05
05
再动索引
生产上用 CREATE INDEX CONCURRENTLY，并评估写入放大和磁盘。
不要在生产上为了“看最新数据”随手执行 pg_stat_statements_reset()，它会清掉所有人的历史。
脚本与测试样例
SQL 和样例 CSV 都可以直接下载。样例 CSV 就是上面测试库的原始输出，可以用来对照列含义。
本节判断
- 适用：PostgreSQL 13–17（已在 17.11 测试）；12 及以下需替换列名。
- 权限：pg_read_all_stats。
- 边界：只读、5 秒超时、最多 20 行；不重置统计。
觉得有用？转给正在做这件事的同事或朋友
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
为什么 Top-20 里有 COPY ... TO stdout？
那是 pg_dump 备份产生的语句。备份窗口落在业务高峰时它会挤占 IO，需要调整备份时间，而不是去“优化”它。
阿里云 RDS / 腾讯云 PostgreSQL 能用吗？
可以，大多数云版本默认已加载 pg_stat_statements，只需在目标库 CREATE EXTENSION，并确认账号有 pg_read_all_stats 或同等权限。具体以云厂商文档为准。
排名靠前的都是 mean 很小的语句怎么办？
那说明压力来自调用量。优先在应用侧查 N+1 查询、缺失缓存和重复轮询，数据库侧能做的有限。