PHP小码农

Appearance

pg_stat_statements 慢查询分析 ​

慢查询日志能告诉你「某一次执行慢」，但它回答不了两个更重要的问题：这条 SQL 是偶发慢还是常态慢？在全库所有 SQL 里，它算老几？ 这两个问题的答案在 pg_stat_statements 手里。它是 PostgreSQL 最重要的性能统计扩展：对执行过的每一条语句（按「语句模板」归一化聚合）持续累计调用次数、总耗时、平均耗时、行数、缓冲区与 WAL 使用量，让你用一句 SQL 就能排出「全库最吃资源的 Top 10 语句」。

本页从慢查询视角展开：不重复视图字段的逐个罗列（那是《pg_stat_statements 语句统计》一页的任务），而是聚焦「怎么用它的统计口径找到慢查询、怎么解读 queryid、怎么用 rows/calls 比值发现失控查询、reset 策略怎么定」。上一页的 auto_explain 提供「案发计划」，本页的 pg_stat_statements 提供「案底档案」，两者在第四页汇成完整的优化流程。

顺带回答一个高频疑问：为什么有了慢查询日志还要装扩展？因为日志只能记录「超过阈值的执行」，而统计覆盖全部执行——一条每秒百次、单次 50ms 的语句永远不会进日志，却可能是全库总耗时第一名。统计层与日志层的关系不是重复，而是互补的采样窗口不同。

前置知识

建议先阅读：auto_explain 自动执行计划

基础概念 ​

语句模板与归一化 ​

pg_stat_statements 的高明之处在于归一化（normalization）：它把语句里的常量替换成位置参数占位符，把同构语句合并成一条统计记录。

sql
-- 应用执行的 100 万条语句
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 99;
SELECT * FROM orders WHERE user_id = 7;

-- pg_stat_statements 里只有一条
SELECT * FROM orders WHERE user_id = $1;

这条记录带着合并后的统计：calls = 1000000、total_exec_time = 累计毫秒、rows = 累计返回行数。没有归一化，语句级统计就没有意义——每条字面量不同的 SQL 都是「新语句」，统计对象会无限膨胀。理解这一点后，很多行为就顺理成章：query 列里看到 $1 不是存储故障，而是设计本身。

与前两页工具的分工 ​

工具统计对象时间视角强项
慢查询日志单次执行事后逐条真实参数、原始文本
auto_explain单次执行事后逐条案发当时的执行计划
pg_stat_statements语句模板持续累计全局排名、趋势、命中率

日志是「证人证言」，统计是「前科档案」。排查事故时先查档案（谁前科最多），再调证言（当时的参数与计划）；日常巡检时只看档案就能发现趋势劣化。三个工具同时在线时，任何一条慢 SQL 都能拼出完整证据链。

归一化的边界 ​

归一化也不是万能的，四类情况会破坏模板合并，实践中要认得出它们的统计形态：

  • 字面量拼接 SQL：WHERE id = 42 与 WHERE id = 43 若没被驱动转成绑定参数，归一化后是同一模板——这是好事；但 IN (1,2,3) 与 IN (1,2,3,4,5) 因为解析树不同，会成为两个模板。
  • 仅注释/空格不同的语句：解析树相同，合并为一个模板（这正是归一化基于解析树而非文本的价值）。
  • 动态表名/列名：SELECT a FROM t1、SELECT b FROM t2 是不同模板，动态生成器会让模板数爆炸。
  • 不同 schema 的同名表：解析树不同，分别统计。

模板数异常增长（dealloc 上升、榜单抖动）时，按这四类排查，绝大多数是第 1 类和第 3 类的应用层问题。

核心统计口径 ​

慢查询视角最常用的列（完整字段表见《pg_stat_statements 语句统计》一页）：

列名含义慢查询视角的用法
queryid语句模板的哈希标识跨时间追踪同一模板
query归一化后的语句文本人肉辨认
calls调用次数区分「高频小慢」与「低频大慢」
total_exec_time累计执行耗时（ms）全库资源大头
mean_exec_time平均单次耗时（ms）直觉上的「慢」
rows累计返回/影响行数与 calls 相除得平均行数
shared_blks_read / shared_blks_hit读盘块数 / 缓存命中块数I/O 密集度
temp_blks_read / temp_blks_written临时文件块数work_mem 溢出信号
wal_records / wal_bytes产生的 WAL 量写放大

注意命名演进：PostgreSQL 13 起，total_time、mean_time 等列更名为 total_exec_time、mean_exec_time，并新增了 total_plan_time 系列（规划耗时单独统计）。旧文章、旧脚本里见到 total_time 即旧版字段，改造监控 SQL 时要一并处理。

版本特性时间线 ​

多版本环境做兼容时按这张表核对可用能力：

版本新能力慢查询分析的意义
PG9.4~扩展定型：归一化统计、queryid基础能力完整
PG13total_exec_time 等改名；pg_stat_statements.max；wal_records/wal_bytes规划与执行分离，可量化写放大
PG13pg_stat_statements_reset(userid, dbid, queryid)精准重置单个模板
PG14pg_stat_statements_info 视图；stats_since；minmax_stats_since统计健康度可监控
PG15temp_blk_read_time / temp_blk_write_time临时文件 I/O 计时
PG16/17计时精度与统计细节持续增强——

写监控 SQL 时要么锁定最低支持版本，要么用 SELECT version() 做特性探测——榜单 SQL 在 PG12 上跑会直接报列名不存在，这是老环境接入监控最常见的报错。

语法与用法 ​

安装与预加载 ​

安装是「重启一次 + 每库一条命令」的一次性成本，但顺序不能错：先改配置重启，再建扩展，最后在每个业务库验证统计在增长。

conf
# postgresql.conf —— shared_preload_libraries 必须包含（重启生效）
shared_preload_libraries = 'pg_stat_statements'

# 统计范围：top（默认，只统计顶层语句）/ all（含函数内嵌套语句）/ none
pg_stat_statements.track = top

# 最多保留的语句模板数（超过后淘汰最不活跃的条目）
pg_stat_statements.max = 10000
sql
-- 重启后，在每个需要统计的数据库中启用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
bash
# 验证
$ psql -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements';"
$ psql -c "SELECT count(*), max(stats_since) FROM pg_stat_statements;"

三个容易踩的坑：其一，shared_preload_libraries 不含它时 CREATE EXTENSION 会报错提示预加载；其二，扩展是按数据库启用的，只装了 postgres 库不代表业务库有统计；其三，pg_stat_statements.max 太小会导致高频新语句把旧统计挤出去（dealloc），Top 榜单不稳定。

常用采集查询集 ​

监控采集器轮询用的查询要「轻、稳、快」，一套可直接落地的最小采集集：

sql
-- ① Top 榜增量采集：只取前 20 条，避免全表搬运
SELECT queryid, calls, total_exec_time, mean_exec_time, rows,
       shared_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- ② 库级汇总：总量与命中率趋势
SELECT sum(calls)                      AS total_calls,
       sum(total_exec_time)            AS total_ms,
       sum(shared_blks_hit)::float8 / nullif(sum(shared_blks_hit) + sum(shared_blks_read), 0) AS hit_ratio
FROM pg_stat_statements;

-- ③ 健康度：dealloc 与统计起点
SELECT dealloc, stats_since FROM pg_stat_statements_info;

采集频率建议 15s~60s；查询 ① 的排序在模板数上万时仍很轻（哈希表内存扫描）。pgwatch、postgres_exporter 内置的采集逻辑与此同构，理解了这三条就理解了所有语句级监控产品的最小内核。

Top SQL 排序模板 ​

慢查询分析的第一件武器是三个排序视角的模板查询：

sql
-- 模板一：按总耗时（全库资源大头，优化收益最大）
SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1)   AS total_s,
       round(mean_exec_time::numeric, 1)           AS mean_ms,
       round(100 * total_exec_time::numeric
             / sum(total_exec_time::numeric) OVER (), 1) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
sql
-- 模板二：按平均耗时（单次最痛的语句，直接影响用户体验）
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 1)   AS mean_ms,
       round(max_exec_time::numeric, 1)    AS max_ms,
       round(min_exec_time::numeric, 1)    AS min_ms
FROM pg_stat_statements
WHERE calls > 100          -- 排除只跑过几次的长任务
ORDER BY mean_exec_time DESC
LIMIT 10;
sql
-- 模板三：按调用次数（高频语句，微小优化 × 巨大次数 = 大收益）
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows / calls                      AS avg_rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

三个视角的结论常常完全不同：总耗时榜第一的可能是一条报表 SQL（跑一次 40 秒、一天 50 次），平均耗时榜第一的可能是一条偶发劣化语句，调用榜第一的可能是每秒百次的点查。优化顺序的经验法则：先做总耗时榜（收益最确定），再看平均耗时榜（体验最直接），调用榜用于发现放大器。

定位「慢查询」的组合筛选 ​

sql
-- 业务语境下的慢查询圈定：平均 200ms 以上，或总耗时占全库 5% 以上
SELECT queryid, calls,
       round(mean_exec_time::numeric, 1)              AS mean_ms,
       round(total_exec_time::numeric / 1000, 1)      AS total_s,
       round(100 * total_exec_time::numeric
             / sum(total_exec_time::numeric) OVER (), 1) AS pct
FROM pg_stat_statements
WHERE mean_exec_time > 200
   OR total_exec_time > 0.05 * (SELECT sum(total_exec_time) FROM pg_stat_statements)
ORDER BY total_exec_time DESC;

详细说明 ​

queryid：跨时间的身份标识 ​

queryid 是对语句解析树计算的哈希值，同构语句（常量不同）共享同一个 queryid。这使它成为把「统计记录」「日志记录」「计划记录」串起来的钥匙：

  • pg_stat_statements.queryid ↔ 语句模板；
  • 慢查询日志/auto_explain 的语句文本 ↔（经归一化后）落到同一个模板。

但 queryid 有三个必须记住的边界：其一，它不跨大版本稳定——升级 PostgreSQL 后同一语句的 queryid 可能改变，以 queryid 为外键的长期基线表要在升级时重建映射。其二，它不跨数据库——同一个 queryid 在 pg_stat_statements 里按 (userid, dbid, queryid) 三元组区分。其三，语义相同的写法差异会得到不同 queryid——空格、注释、大小写的差异只要没改变解析树通常不影响，但把 SELECT * 改写成 SELECT col1, col2 就是一个新模板。PG14+ 引入了 compute_query_id 参数控制内核计算时机（auto 时由启用方触发），通常保持默认即可。

total_exec_time 与 mean_exec_time：两个口径的博弈 ​

PG13+ 的命名把「执行」与「规划」分开统计。解读时两个口径要分开看：

  • total_exec_time = Σ 每次执行的耗时。它衡量资源占用，榜单 = 优化收益榜。
  • mean_exec_time = total_exec_time / calls。它衡量单次体验，榜单 = 用户体验榜。

均值最大的陷阱是被极值拉偏：一条语句 calls = 1000、mean = 500ms，可能 990 次 10ms、10 次 48 秒（绑定参数选择性差异导致的计划劣化）。所以平均耗时榜必须配 max_exec_time / min_exec_time / stddev_exec_time 一起看：max/min 比值成百上千，就是「随机参数劣化」的指纹，此时把它交给 auto_explain 抓计划（见上一页示例四）。

rows / calls：发现失控查询 ​

rows 是语句累计返回（SELECT）或影响（DML）的行数。除以 calls 得到平均行数，这个不起眼的比值是「失控查询探测器」：

sql
-- 平均返回行数异常高的模板
SELECT queryid, calls, rows, rows / calls AS avg_rows,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 80) AS query_head
FROM pg_stat_statements
WHERE calls > 0
ORDER BY rows / calls DESC
LIMIT 10;

典型病灶各有一副面孔：

形态avg_rows 特征病因
avg_rows 巨大 + mean 高数十万以上报表/导出语句混进 OLTP 通道
avg_rows = 1 但 calls 爆炸恒为 1循环里逐行查询（N+1 问题）
avg_rows 远超预期视 WHERE 而定索引失效退化成大扫描

「N+1」形态特别值得盯：单次 5ms 的点查毫无威胁，但每秒 500 次的 avg_rows = 1 点查会吃掉大量连接与 CPU——它慢查询日志里可能根本不显眼（阈值都过不了），只有累计统计能暴露。

统计的时间锚点：stats_since 与 minmax_stats_since ​

PG14+ 的 pg_stat_statements_info 提供两个对账锚点：

sql
SELECT dealloc, stats_since, minmax_stats_since
FROM pg_stat_statements_info;
  • stats_since：当前全部统计的开始时间。任何基于 pg_stat_statements 的报表都应标注它——「Top 榜单的口径是自 stats_since 以来」，否则两张不同时间生成的榜单没有可比性。
  • minmax_stats_since：min/max 类列（min_exec_time 等）最近一次重新累计的起点。reset 之后 min/max 是全新累计，而 total/calls 是历史累计——两者起点不同是正常现象，不是 bug。
  • dealloc：条目被淘汰的次数。它是「归一化是否健康」的哨兵，后文细说。

典型病灶速查表 ​

把统计特征与病因对应起来，是本页的核心技能。一张速查表收尾：

统计特征最可能病因下一步
总耗时占比 > 20%资源大头拿文本进 EXPLAIN 复现
mean 高 + max/min 巨大随机参数劣化日志找参数 → auto_explain
calls 巨大 + avg_rows ≤ 2N+1 / 循环点查应用层改批量
temp_blks_written 高work_mem 溢出调参或改写查询
shared_blks_read 高 + hit 低缓存不友好扫描查索引与膨胀
wal_bytes 巨大写放大检查批量 DML 的行宽与索引
mean_plan_time 高复杂查询反复规划考虑 PREPARE / 连接内缓存
dealloc 增长快模板溢出修归一化或调大 max

track_utility 与实用语句 ​

pg_stat_statements.track_utility（默认 on）决定 PREPARE、EXPLAIN、VACUUM、CREATE INDEX 等实用语句是否进入统计。慢查询视角下的两个要点：其一，保持默认开启才能看到 VACUUM/CREATE INDEX 这类运维动作对总账的影响（一次全库 VACUUM 可能在榜单上霸屏）；其二，EXPLAIN 语句自身入榜会轻微污染统计，对精度要求高的采集可以在解析层过滤 query LIKE 'EXPLAIN%'。

I/O 维度：从「慢」追到「为什么慢」 ​

缓冲区与 WAL 列把耗时翻译成资源行为：

sql
-- 缓存命中率最差的语句（读盘大户）
SELECT queryid, calls,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       round(mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;
sql
-- 排序/哈希溢出到磁盘的语句（work_mem 不足指纹）
SELECT queryid, calls,
       pg_size_pretty((temp_blks_written * 8192)::bigint) AS temp_written,
       round(mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

两个查询分别回答「这条 SQL 慢是不是因为都在读盘」与「这条 SQL 是不是在把排序写到磁盘」。temp_blks_written 持续偏高的模板，几乎总能通过调大 work_mem 或改写查询（减少排序规模）获得立竿见影的改善。写侧还可以看 wal_records / wal_bytes（PG13+）识别写放大，blk_read_time / blk_write_time 需要 track_io_timing = on 才有数据，开销可接受时可开启。

定期 reset 策略 ​

统计会一直累计，直到被重置。pg_stat_statements 的重置与留存策略：

sql
-- 全量重置（统计从零开始）
SELECT pg_stat_statements_reset();

-- PG13+：精准重置某个库、某个用户、某个模板
SELECT pg_stat_statements_reset(0, (SELECT oid FROM pg_database WHERE datname = 'appdb'), NULL);

要不要定期 reset？ 社区两种流派的取舍：

流派做法优点缺点
不 reset让统计持续累计总账完整，趋势连续均值被历史稀释，看不出「最近变慢」
定期 reset（如每天/每周）周期性清零均值反映近期形态，榜单新鲜丢历史；reset 时机撞上监控抓取会产生假凹坑

生产上的常见折中是reset 交给外部基线系统：PostgreSQL 侧保持不 reset（或低频 reset），监控采集器（Prometheus 等）按固定间隔抓取增量快照，在监控系统里计算速率与环比。若选择定期 reset，注意两点：reset 后 pg_stat_statements_info 的 dealloc 与 stats_since 可用于对账；要把 reset 时刻写进变更记录，避免「周一的榜单突然清零」引发误会。

折中之外的第三条路是「低频 reset + 每日快照」：数据库侧每周 reset 一次防均值过度稀释，监控系统每日抓快照保住日内细节。选择哪条路取决于团队对「总账完整」与「近期敏感」的偏好，没有标准答案，但必须成文——统计口径不清是语句级监控数据不可信的第一原因。

查询模板数量与 max 的关系 ​

pg_stat_statements.max（默认 5000）决定内存中最多保留多少条模板。ORM 与动态 SQL 生成器是统计的大敌：拼接 SQL 让归一化失效（每条都是新模板），模板数顶到 max 后开始淘汰，高频模板被低频新语句反复挤出，榜单失真且 pg_stat_statements_info.dealloc 计数飙升。解法：应用侧统一走绑定参数（这是治本），监控侧把 dealloc 增速列为告警指标——它升高就意味着归一化出了问题或 max 不够。

与 auto_explain 的联动工作流 ​

两页工具的联动在「计划劣化」排查中闭环：

text
pg_stat_statements（统计层）            auto_explain（现场层）
┌─────────────────────────┐           ┌─────────────────────────┐
│ mean_exec_time 环比异常↑  │  嫌疑模板  │ 等待该模板下次超过阈值    │
│ max/min 比值巨大          │ ────────► │ 自动记录案发计划          │
│ 拿到 queryid 与文本       │           │ Plan Rows vs Actual Rows │
└─────────────────────────┘           └───────────┬─────────────┘
              ▲                                   │ 计划证据
              │            修复后回看统计           ▼
              └────────── mean_exec_time 回落 ◄── 归因修复

工作流四步：统计层发现异常 → 确认 auto_explain 阈值能覆盖 → 拿案发计划归因 → 修复后用统计层验证恢复。第四步常被忽略：修复是否有效，不是看单次 EXPLAIN，而是看 mean_exec_time 与 shared_blks_read 是否回落——这才是闭环。

在监控体系中的位置 ​

pg_stat_statements 的快照是几乎所有专业监控方案的标配输入：postgres_exporter 内置了它的采集查询（见 17.4 节 Prometheus 一页），pgwatch 的核心 preset 大量引用它，Datadog / pganalyze 等商业产品也以它为基础做语句级分析。因此本页的 Top SQL 思维会直接迁移到后续工具页——在这里学会读 total_exec_time 与 rows/calls，到任何监控平台都是同一套语义。

与缓存命中率、系统指标的结合 ​

语句统计回答「谁在消耗资源」，系统指标回答「资源还剩多少」，两者拼起来才是完整归因。举两条经典组合：

  • 库级缓存命中率低 + 单语句 shared_blks_read 高：内存不足或扫描风暴。先看库级命中率（pg_stat_database 的 blks_hit/blks_read）确认整体水位，再用本页的读盘榜单定位具体语句。
  • CPU 饱和 + 总耗时榜单无异常：耗时不涨但 CPU 满，通常是 calls 暴涨（流量放大）而非单条变慢。对比 calls 的环比增速即可证实——这正是「榜单要用增量视角看」的原因。

这些系统指标的采集与告警在 17.4 节（Prometheus/Zabbix）成体系展开，本页先建立「语句 ↔ 资源」的双向映射意识。

mean_plan_time：被忽略的规划维度 ​

PG13+ 把规划时间单独成列（total_plan_time / mean_plan_time），这是排查「简单语句偶发慢」的利器。规划耗时高的场景：

sql
SELECT queryid, calls,
       round(mean_plan_time::numeric, 2) AS mean_plan_ms,
       round(mean_exec_time::numeric, 2) AS mean_exec_ms,
       left(regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g'), 70) AS query_head
FROM pg_stat_statements
WHERE mean_plan_time > 1
ORDER BY mean_plan_time * calls DESC
LIMIT 10;

mean_plan_time 高的常见原因：分区表超多分区（每次规划都要遍历分区约束）、复杂视图多层嵌套、大量 IN 列表的常量内联。这类问题的诡异之处在于执行本身很快——mean_exec_time 平平无奇但语句整体慢。修复方向是减少规划负担：分区表合理设计、使用 PREPARE 复用计划、或 PG12+ 的 plan_cache_mode = force_generic_plan（权衡后使用）。

与 pg_stat_activity 的实时下钻 ​

统计是「历史累计」，发现异常后往往想知道「现在正在跑的是不是它」。标准动作是把榜单里的文本拿到 pg_stat_activity 里对号：

sql
-- 疑似模板此刻有没有在执行？跑了多久？
SELECT pid, usename, state,
       now() - query_start AS running_for,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query ILIKE '%orders%'    -- 替换为嫌疑模板的关键表名
ORDER BY query_start;

实时核对的价值在于「抓现行」：如果此刻正在执行的正是嫌疑模板且已运行数分钟，直接看它的 wait_event / wait_event_type（在等锁还是在读盘），比等它跑完再看日志快得多。视图字段详见《pg_stat_activity 活动会话》一页，这里只建立「统计层 → 视图层」的下钻意识。

手工基线表：最小可行的趋势系统 ​

不引入任何外部组件，两张表 + 两个定时任务就能得到语句级趋势：

sql
-- 基线快照表（每日凌晨执行）
CREATE TABLE IF NOT EXISTS pgss_snapshots (
  snapshot_at timestamptz NOT NULL,
  queryid     bigint      NOT NULL,
  dbid        oid         NOT NULL,
  calls       bigint      NOT NULL,
  total_exec_time double precision NOT NULL,
  mean_exec_time  double precision NOT NULL,
  rows        bigint      NOT NULL,
  shared_blks_read bigint NOT NULL,
  temp_blks_written bigint NOT NULL
);
CREATE INDEX ON pgss_snapshots (snapshot_at);

INSERT INTO pgss_snapshots
SELECT now(), queryid, dbid, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_read, temp_blks_written
FROM pg_stat_statements;

-- 保留策略：只留 90 天
DELETE FROM pgss_snapshots WHERE snapshot_at < now() - interval '90 days';

有了连续快照，环比、增速、劣化检测全部可在 SQL 内完成。这一小段脚手架是很多团队语句监控的起点，也是理解 pgwatch/Prometheus 等工具「到底在采集什么」的最好教材——它们做的事本质上相同，只是多了调度、存储与可视化。

实战示例 ​

示例一：全库 Top 10 与占比分析 ​

sql
-- 全库资源画像：Top 10 占总耗时的比例
WITH stats AS (
  SELECT queryid, calls, total_exec_time, mean_exec_time,
         left(regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g'), 90) AS query_head
  FROM pg_stat_statements
)
SELECT queryid, calls,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       round(mean_exec_time::numeric, 1)         AS mean_ms,
       round(100 * total_exec_time::numeric
             / (SELECT sum(total_exec_time) FROM stats), 1) AS pct,
       query_head
FROM stats
ORDER BY total_exec_time DESC
LIMIT 10;
text
      queryid       | calls | total_s | mean_ms | pct  |           query_head
--------------------+-------+---------+---------+------+----------------------------------
 382749113627984598 |   523 | 18402.3 | 35186.0 | 41.2 | SELECT ... FROM orders JOIN ...
 928347561234098712 | 92341 |  6210.9 |    67.3 | 13.9 | UPDATE user_sessions SET ...
 ...                |       |         |         |      |

Top 1 占了全库 41% 的资源——集中度越高，优化 ROI 越确定。

示例二：N+1 查询侦探 ​

sql
-- 高频 + 单行返回：N+1 指纹
SELECT queryid, calls, rows / calls AS avg_rows,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       left(regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g'), 80) AS query_head
FROM pg_stat_statements
WHERE calls > 10000 AND rows / calls <= 2
ORDER BY total_exec_time DESC
LIMIT 5;
text
      queryid       | calls | avg_rows | mean_ms | total_s |           query_head
--------------------+-------+----------+---------+---------+----------------------------------
 712398045629381120 | 482319|        1 |    2.31 |  1114.2 | SELECT * FROM products WHERE id = $1

单次 2.3ms 完全无辜，但一天 48 万次合计 1114 秒——它可能整页都进不了慢查询日志（阈值 200ms），却是全库第五大资源消耗者。修复方向在应用层：改成 WHERE id = ANY($1) 批量取回。

示例三：随机参数劣化的指纹 ​

sql
SELECT queryid, calls,
       round(min_exec_time::numeric, 2)  AS min_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2)  AS max_ms,
       round((max_exec_time / nullif(min_exec_time, 0))::numeric, 1) AS spread
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY spread DESC
LIMIT 5;
text
      queryid       | calls | min_ms | mean_ms |  max_ms  | spread
--------------------+-------+--------+---------+----------+--------
 542310987623401122 |  5312 |   0.45 |  412.77 | 12843.11 | 28540.2

max/min = 28540——同一条模板语句最快 0.45ms、最慢 12.8 秒。这是绑定参数选择性差异把计划带偏的教科书指纹。下一步：从慢查询日志找出慢那次的具体参数，交给 auto_explain 抓计划。

示例四：reset 前后的周报表 ​

sql
-- 周一上班第一件事：冻结上周统计并重置（示例策略：每周 reset）
CREATE TABLE pgss_weekly AS
SELECT now() AS snapshot_at, *
FROM pg_stat_statements;

SELECT pg_stat_statements_reset();

-- 周报表：上周总耗时 Top 5 与其周环比（与上一张快照表对比）
SELECT b.queryid,
       round((b.total_exec_time - COALESCE(a.total_exec_time, 0))::numeric / 1000, 1) AS week_s,
       b.calls - COALESCE(a.calls, 0) AS week_calls
FROM pg_stat_statements b
LEFT JOIN pgss_weekly a USING (queryid)
ORDER BY week_s DESC
LIMIT 5;

这份「每周增量 Top 榜」比累计榜单更能反映业务变化：新上线的功能、数据量增长带来的劣化，都会在环比里第一时间现形。把快照表换成定时任务写入历史表，就得到最小可行的语句级趋势系统。

示例五：work_mem 溢出排查 ​

报表查询偶发把磁盘打满、IO 告警。第一步先看谁在往磁盘写临时文件：

sql
SELECT queryid, calls,
       pg_size_pretty((temp_blks_written * 8192)::bigint) AS temp_total,
       pg_size_pretty((temp_blks_written * 8192 / nullif(calls, 0))::bigint) AS temp_avg,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       left(regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g'), 70) AS query_head
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 5;
text
      queryid       | calls | temp_total | temp_avg | mean_ms |              query_head
--------------------+-------+------------+----------+---------+----------------------------------------
 901234561209348112 |    87 | 28 GB      | 330 MB   | 42188.0 | SELECT ... ORDER BY created_at LIMIT 100

平均每次溢出 330MB 到磁盘——典型的「大排序 + work_mem 不足」。确认该语句属于低频报表后，用 ALTER ROLE report_ro SET work_mem = '512MB' 只为报表账号放宽内存，而不是全局调大。修复验收同样回到本页统计：temp_blks_written 归零、mean_exec_time 从 42s 降到 8s。这个案例也演示了统计层的另一个用法：很多「参数类」优化不需要看执行计划，统计特征已经给出处方。

注意事项 ​

扩展按数据库启用，别只装在 postgres 库。 每个业务库单独 CREATE EXTENSION；跨库语句（如 dblink 调用）归属发起库统计。监控多库实例时，采集查询要逐库执行或用 pg_stat_statements_info 汇总核对。

track = top 时函数内部语句不单独统计。 PL/pgSQL 里跑了十条慢 SQL，统计里只有那条 CALL。需要下钻函数内部时把 pg_stat_statements.track 调成 all（注意开销与模板数增长），或改用 auto_explain 的 log_nested_statements 定点排查。

均值会被时间稀释，长周期统计不反映「最近」。 运行三个月不 reset 的实例，mean_exec_time 是三个月的加权平均，最近一周的劣化会被摊薄。趋势判断要么定期 reset，要么采集器做增量快照——二选一，不能两头都要。

不要在生产把 track 调成 all 就不管了。 all 模式下嵌套语句模板数可能暴涨，pg_stat_statements.max 很快顶满触发 dealloc，反而把顶层统计挤掉。开启前估算模板量并相应调大 max，开启后盯 pg_stat_statements_info.dealloc。

query 列可能被截断，完整文本要靠日志。 pg_stat_statements.track_utility 控制实用语句（PREPARE、EXPLAIN 等）是否入榜；超长语句的 query 文本按内核截断逻辑处理，要看完整原文与真实参数，回慢查询日志找。

queryid 不是跨版本主键。 大版本升级、主备切换（不同小版本）、甚至某些扩展参与解析的场景都可能改变哈希值。把 queryid 当「本次运行期内的会话标识」用，不要当永久外键建库。

统计不包含并发竞争与排队时间之外的系统因素。 total_exec_time 是后端视角的语句执行时间，锁等待包含在内，但 CPU 调度延迟、存储抖动这类「语句之外」的时间只体现在总量波动里——资源类问题要结合系统指标看，单靠语句统计会误判。

reset 与监控抓取要做互斥协调。 定期 reset 撞上 Prometheus 抓取，会抓到半程数据产生毛刺。把 reset 安排在采集间隔之间的空档，或在采集端对 reset 时刻做标注。

内存敏感环境注意采集查询自身的开销。 模板数接近 max（如上万条）时，全量 SELECT * FROM pg_stat_statements 会搬运几十 MB 数据；采集端务必加 ORDER BY ... LIMIT。pg_stat_statements 的统计本体存于后端私有内存而非共享内存（每后端增量记账、采集时合并读取），这一点决定了「采集查询要短平快」的设计约束。

把 queryid 关联日志时注意归一化差异。 日志里的语句是原始文本（含常量），统计里是归一化文本（$1 占位）；用文本模糊匹配桥接两者时，先对日志文本做同样的常量替换，或干脆用 auto_explain 输出的 Query Text 按语义人工对齐。

最佳实践 ​

  1. 任何生产实例都应默认安装 pg_stat_statements。 它是慢查询分析的坐标系：没有它，日志只有碎片没有排名，优化优先级只能拍脑袋。安装成本（预加载 + 一条 CREATE EXTENSION）与收益完全不成比例。
  2. 用三个榜单分工：总耗时找收益、平均耗时找痛点、调用次数找放大器。 每张榜都配 calls、mean/max/min、占比列一起看，避免单指标误判。
  3. 把 rows/calls 比值纳入日常巡检。 N+1 与失控扫描在单次视角下隐形，在累计视角下现形；巡检 SQL 建议直接抄本页示例二。
  4. max/min 比值大的模板交给 auto_explain 深挖。 随机参数劣化是统计层只能发现、不能确诊的问题，确诊必须拿案发计划——两页工具的联动点。
  5. reset 策略写进 runbook 并与采集器协调。 推荐「数据库侧不 reset + 监控侧增量快照」；若坚持定期 reset，固定时刻（如每周一 00:05）并写入变更记录。
  6. 监控 pg_stat_statements_info 的 dealloc 与 stats_since。 dealloc 持续增长说明模板溢出（动态 SQL 未归一化或 max 不足），stats_since 说明发生过 reset——两者都是榜单可信度的健康指标。
  7. 让应用层全面使用绑定参数。 这是归一化生效的前提，也是 SQL 注入防御的底线； ORM/动态 SQL 场景下把「拼接 SQL 进 Top 榜单」当作代码质量事故处理。
  8. 需要 I/O 归因时开启 track_io_timing。 blk_read_time / blk_write_time 让「慢在读」变成可量化数据；官方注明其开销在现代系统上通常可忽略，但开启后应观察一轮 CPU 曲线确认。
  9. 每周固定输出「增量 Top 榜」并留档。 本页示例四的模式：快照表 + 环比 + 归档。语句级趋势是容量规划与回归发现的最便宜手段。
  10. 优化验证必须回到统计层。 任何索引与改写动作，验收标准是 mean_exec_time、shared_blks_read、temp_blks_written 回落，而不是单次 EXPLAIN 好看。闭环意识是把优化做实的关键。
  11. 升级大版本后重建 queryid 基线。 把「对比升级前后 Top 榜单」列入升级 runbook 的固定动作，用语句文本而非 queryid 做映射。
  12. 视图字段与进阶用法移步专题页。 本页聚焦慢查询视角；完整字段表（含 PG14+ 的 stats_since、minmax_stats_since 等）与更多示例见 pg_stat_statements 语句统计。

下一节

继续学习：慢查询优化流程

参考链接 ​