auto_explain 自动执行计划
慢查询日志回答了「哪条 SQL 慢」，但没有回答「为什么慢」。要回答「为什么」，必须看到当时那条语句使用的执行计划。人工的做法是拿到 SQL 后立刻 EXPLAIN ANALYZE 复现——但问题在于：慢查询往往是偶发的，复现时数据分布、缓存状态、统计信息都可能已经变化，手工 EXPLAIN 看到的计划未必是案发时的计划。
auto_explain 就是为这个缺口准备的内核自带扩展：它像一个常驻的「执行计划行车记录仪」，当某条语句的执行时间超过阈值时，自动把该语句当时的真实执行计划写入日志。它解决了三个手工 EXPLAIN 无法解决的问题：偶发慢查询难以复现、生产环境不能随便跑 ANALYZE（副作用真实执行）、以及计划劣化（plan regression）需要「案发当时」的证据。
本页讲清楚它的加载方式、全部配置参数（官方一共十几个 GUC，逐一说明默认值与用途）、log_analyze 的性能陷阱、JSON 格式如何对接日志采集器，以及它与 pg_stat_statements 联动的完整定位工作流。学完本页，你应当能为任何一套生产库设计「超 3 秒的语句自动留下执行计划」的监控方案。
阅读本页时请对照上一页《慢查询日志》：auto_explain 的阈值语义、日志载体、采集链路与之完全同构，差异只在「多记了一份计划」。把这个同构关系记牢，两个工具的配置与运维就变成一套肌肉记忆。
前置知识
基础概念
它在慢查询工具链中的位置
把上一页的三层工具表补全，auto_explain 属于「现场层」里最深的一档：
| 工具 | 记录内容 | 丢失的信息 |
|---|---|---|
log_min_duration_statement | 语句耗时 + 文本 | 为什么慢（没有计划） |
auto_explain | 语句耗时 + 文本 + 当时的执行计划 | 计划之外的等待事件（锁、IO） |
pg_stat_statements | 每类 SQL 的累计统计 | 单次执行的参数、计划 |
pg_stat_activity | 当下正在发生什么 | 历史信息 |
auto_explain 的独特价值在于「计划快照」：它记录的是该语句实际使用的计划，而不是复现时推算的计划。执行计划是优化器根据统计信息、参数值、成本参数现场计算的，同一条 SQL 在不同时间可能生成不同计划——这正是「昨天还快今天变慢」的最常见原因，也是 auto_explain 不可替代的原因。
它由 Takahiro Itagaki 编写并自 PostgreSQL 9.0 起进入内核发行版，是最老牌的 contrib 模块之一——十余年生产验证意味着它的行为边界（官方文档一页就讲完的全部参数）非常清楚，不存在「社区扩展版本碎片化」的问题。
模块形态与加载方式
auto_explain 是 contrib 扩展，但与 pg_stat_statements 不同——它不提供任何 SQL 接口（没有函数、没有视图），纯粹是一个「加载进后端进程就开始工作」的模块。官方文档的表述是：它不提供 SQL 可访问的函数，使用方式就是把它加载进服务端。
三种加载方式，适用场景不同：
| 方式 | 生效范围 | 是否需要重启 | 典型场景 |
|---|---|---|---|
shared_preload_libraries | 所有会话 | 是（需重启） | 生产库常态监控 |
session_preload_libraries | 所有会话（每会话连接时加载） | 否（reload 生效） | 免重启的常态监控（PG14+ 语义良好） |
LOAD 'auto_explain' | 仅当前会话 | 否 | 临时诊断、复现问题 |
LOAD 方式需要 superuser 权限。官方文档的典型示例用的正是 preload 方案：
# postgresql.conf —— 官方示例风格的最小可用配置
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'注意官方强调的一点：默认行为是什么都不做。模块加载后必须至少设置 auto_explain.log_min_duration，否则没有任何输出——排障时「明明加载了却看不到计划」的第一检查项就是它。
与手工 EXPLAIN 的对照
| 维度 | 手工 EXPLAIN (ANALYZE, BUFFERS) | auto_explain |
|---|---|---|
| 计划来源 | 现场重新规划 | 语句实际使用的计划 |
| 副作用 | 真实执行一遍（写场景危险） | 无额外执行（只是观察原执行） |
| 时机 | 事后复现 | 案发当时 |
| 覆盖面 | 逐条人工 | 全量自动（可采样） |
| 依赖 | 无 | 需要加载模块 |
一句话总结：手工 EXPLAIN 是「案情重演」，auto_explain 是「行车记录仪」。两者的输出格式完全一致（都是 EXPLAIN 的输出），因此本站《EXPLAIN ANALYZE》一页的所有解读技巧在 auto_explain 日志上同样适用。
同一条 SQL 的计划为什么会变
理解 auto_explain 的价值，先要理解计划的「易变性」。优化器为每条语句现场计算计划，输入包括：
| 输入 | 变化来源 | 对计划的典型影响 |
|---|---|---|
| 统计信息 | ANALYZE 定期刷新、数据写入漂移 | 估算行数变化 → 算子选择翻转 |
| 绑定参数 | 每次执行的参数值不同 | 选择性差异 → 索引扫描/顺序扫描切换 |
| 缓存与内存状态 | 共享缓冲命中率、work_mem 余量 | 并行度、溢出盘与否 |
| 表物理形态 | 膨胀、页顺序、索引碎片 | I/O 估算失真 |
| 会话与全局参数 | SET、参数组变更 | 成本模型整体偏移 |
其中「绑定参数」最隐蔽：预编译语句的计划在规划时可能看不到参数值（generic plan），而首次执行用的是 custom plan，两者可能走完全不同的索引。这就是「测试环境飞快、生产偶发超慢」的常见根因。auto_explain 记录的正是「某次真实参数下的真实计划」，是唯一能坐实这类问题的证据源。
语法与用法
安装与预加载
auto_explain 随 PostgreSQL 发行，无需单独编译安装。配置预加载：
# 方案 A：shared_preload_libraries（经典方案，需重启）
# 若已有其他库，用逗号追加
shared_preload_libraries = 'pg_stat_statements,auto_explain'
# 方案 B：session_preload_libraries（免重启）
session_preload_libraries = 'auto_explain'# 修改 shared_preload_libraries 后需要重启
$ systemctl restart postgresql
# 验证已加载
$ psql -c "SHOW shared_preload_libraries;"
$ psql -c "SELECT * FROM pg_available_extensions WHERE name = 'auto_explain';"两种 preload 的取舍：shared_preload_libraries 在服务启动时加载一次、所有后端共享，开销最小，但改配置要重启；session_preload_libraries 在每个后端建立时加载，开销略高（仅一次 dlopen），但可以 reload 生效、适合「临时开启一段时间再关」的运维节奏。生产库如果决定长期开启，用 A；如果只是阶段性排查，用 B。
单会话加载适合排障现场：
-- superuser 会话内临时加载并调参，只影响当前会话
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = on;
-- ...执行待观察的语句...核心参数速查表
官方参数全集如下（均为 superuser 级别可调）：
| 参数 | 默认值 | 作用 |
|---|---|---|
auto_explain.log_min_duration | -1 | 记录计划的耗时阈值（ms），0 全记，-1 关闭 |
auto_explain.log_analyze | off | 输出 EXPLAIN ANALYZE 级别信息（实际行数、循环次数、耗时） |
auto_explain.log_verbose | off | 输出 EXPLAIN VERBOSE 级别信息（列/表达式细节） |
auto_explain.log_buffers | off | 输出缓冲区使用统计（需 log_analyze） |
auto_explain.log_wal | off | 输出 WAL 使用统计（需 log_analyze） |
auto_explain.log_timing | on | 每个计划节点的时间统计（需 log_analyze，关闭可大幅降开销） |
auto_explain.log_triggers | off | 输出触发器执行统计（需 log_analyze） |
auto_explain.log_settings | off | 输出影响规划的、非默认值的配置项 |
auto_explain.log_format | text | 计划输出格式：text / xml / json / yaml |
auto_explain.log_level | LOG | 计划写入日志时的日志级别（DEBUG5~LOG、INFO、NOTICE、WARNING） |
auto_explain.log_nested_statements | off | 记录函数内部嵌套语句的计划 |
auto_explain.log_parameter_max_length | -1 | 语句文本中绑定参数值的截断长度（字节） |
auto_explain.sample_rate | 1.0 | 会话内语句的采样比例 |
auto_explain.max_plan_depth | 30 | 计划树打印的最大深度（超深层不打印） |
记住三个「强制搭配」：log_buffers / log_wal / log_timing / log_triggers 都只在 log_analyze 开启时生效；而 log_analyze 开启有全局性能代价（后文详述）。
推荐的起步配置
# postgresql.conf —— 生产库推荐起步配置
shared_preload_libraries = 'auto_explain'
# 超过 3 秒的语句记录执行计划
auto_explain.log_min_duration = '3s'
# 记录实际行数与节点耗时（注意开销，见下文）
auto_explain.log_analyze = on
auto_explain.log_buffers = on
# JSON 格式便于采集器解析
auto_explain.log_format = 'json'log_min_duration 的取值语义与 log_min_duration_statement 完全一致：正数毫秒阈值、0 全量、-1 关闭；支持 '3s' 这类带单位写法。阈值可以（也建议）设置得高于慢查询日志的阈值——计划比一行 duration 贵得多，只给真正严重的语句留计划。
上线验证清单
配置完成后，按这张清单逐项验证（顺序即排障顺序）：
# 1. 模块已加载（两种 preload 任一可见即可）
psql -c "SHOW shared_preload_libraries;"
psql -c "SHOW session_preload_libraries;"
# 2. 参数已下发且来源正确（source 应为 configuration file）
psql -c "SELECT name, setting, source FROM pg_settings WHERE name LIKE 'auto_explain%';"-- 3. 制造一条超阈值语句，确认日志产出
SELECT pg_sleep(4);LOG: duration: 4003.512 ms plan:
Query Text: SELECT pg_sleep(4);
Result (actual rows=1.00 loops=1)# 4. 确认日志文件在增长且格式符合预期
$ tail -f /var/lib/postgresql/log/postgresql-*.log四步全绿才算部署完成。第 2 步的 source 列特别重要：如果显示 session 或 override，说明有更高优先级的配置在起作用（可能是云平台参数组或 ALTER SYSTEM 残留）。
观察输出
开启后，超过阈值的语句会在服务器日志中产生这样的条目（官方文档示例）：
LOG: duration: 3.651 ms plan:
Query Text: SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1.00 loops=1)
-> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92.00 loops=1)
Hash Cond: (pg_class.oid = pg_index.indrelid)
-> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255.00 loops=1)
-> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92.00 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92.00 loops=1)
Filter: indisunique结构解读：
duration: 3.651 ms plan:——与慢查询日志同源的耗时前缀，后跟plan:标记。Query Text: ...——语句原文。- 以下是标准 EXPLAIN 输出。有
actual time=...说明log_analyze已开启；有Buckets/Batches说明节点带缓冲区细节。
详细说明
log_min_duration 与两级日志的关系
auto_explain.log_min_duration 与 log_min_duration_statement 相互独立、各管各的。同一条语句可能同时触发两者（一条 duration 行 + 一条 plan 行），也可能只触发其一。设计两级阈值是标准做法：
# 一级：1 秒的语句记耗时（用于统计与趋势）
log_min_duration_statement = 1000
# 二级：3 秒的语句额外记计划（用于确诊）
auto_explain.log_min_duration = '3s'这样做的理由：计划输出动辄几十上百行，只给 1% 最严重的语句留计划，日志量与可读性都可控。个别团队反过来用 auto_explain 完全替代慢查询日志（log_format = json 后两者格式统一），代价是所有慢语句都带计划、日志体积膨胀数倍——是否接受取决于日志管道的处理能力。
log_analyze 的性能陷阱（必读）
这是 auto_explain 最重要的一页警告，官方原文的措辞是「extremely negative impact on performance（对性能可能有极严重的负面影响）」：
当
log_analyze开启时，所有被执行的语句都会进行逐计划节点计时——无论它们最终是否达到阈值、是否会被记录。
换句话说，计时开销不是「慢查询的开销」而是「全库所有查询的开销」。逐节点计时需要在每个算子进出时读取系统时钟，在某些系统上时钟读取本身就很贵。由此得出两条铁律：
铁律一：生产库长期开启时，必须关闭 log_timing。
# 生产长期开启的安全形态：要行数不要逐节点计时
auto_explain.log_analyze = on
auto_explain.log_timing = off
auto_explain.log_buffers = onlog_timing = off 后计划里没有每个节点的 actual time，但仍有 rows、loops 与整条语句的总耗时——对「判断估算偏差、发现行数失控」这一主要用途完全够用，而计时开销消失。官方文档明确说明这是降低性能代价的推荐手段。
铁律二：需要完整逐节点耗时时，改用限时诊断窗口。
# 诊断窗口（分钟级）：完整信息
auto_explain.log_analyze = on
auto_explain.log_timing = on # 窗口结束后立即关回与上一页「全量诊断窗口」的 runbook 一样：开窗 → 复现 → 关窗 → 分析。
log_buffers 与 log_wal：从「慢」到「贵」
log_analyze 开启后，这两个开关把计划从「时间维度」扩展到「资源维度」：
log_buffers：等价于EXPLAIN (BUFFERS)。顶层汇总shared hit/read/written、temp read/written等块数。它回答的是「这次执行到底碰了多少页」——temp高说明排序/哈希溢出到磁盘（work_mem不足），shared read高说明缓存未命中（内存不足或顺序扫大表）。log_wal：等价于EXPLAIN (WAL)。记录该语句产生的 WAL 量（records、bytes、fpi 页数）。它回答「这次执行写了多少 WAL」——批量 UPDATE 的 WAL 放大、检查点压力都从这里看。
这两个统计的采集成本远低于逐节点计时（块计数本来就在缓冲区管理中发生），可以放心长期开启。
log_format：选 text 还是 json
auto_explain.log_format 支持四种格式：text（默认）、json、xml、yaml。选择标准只有一个——谁来读这份日志：
| 消费方 | 推荐格式 | 理由 |
|---|---|---|
| 人肉阅读、邮件告警摘录 | text | 与 EXPLAIN 手册、各类文档对照最直观 |
| 日志采集器（JSON 管道） | json | 与 jsonlog 无缝配合，结构化解析 |
| 既有 XML 工具链 | xml | 少见，兼容性需求 |
| YAML 工具链 | yaml | 少见，兼容性需求 |
对接采集器时，json 格式的计划是一棵标准的嵌套 JSON 树（Plan 节点内嵌 Plans 数组），采集端可以直接按节点做统计与告警，例如「发现某模板语句出现 Seq Scan 节点即告警」。这正是官方把 json 列为一等格式的原因。
sample_rate：高并发下的采样
auto_explain.sample_rate（默认 1.0）控制每个会话内多大比例的语句参与「计划记录候选」。它的定位与 log_statement_sample_rate 不同：前者按会话抽样语句，后者按语句抽样。当慢查询日志显示某模板语句每分钟触发数百次、计划几乎相同时：
# 只记录 10% 会话的语句计划
auto_explain.sample_rate = 0.1官方说明：对于嵌套语句，要么整段都参与采样、要么整段都不参与，不会出现半个函数被采样的情况。
max_plan_depth 与深计划
PG16 引入 auto_explain.max_plan_depth（默认 30），限制打印的计划树深度。超深计划（大量嵌套子查询、分区裁剪后的超多分区计划）可能产出数万行日志，压垮采集管道。当 pgbadger 或采集端报出「单条日志过大」时，降低该值即可截断；日常 30 的默认值对绝大多数业务计划绰绰有余。
log_nested_statements：函数内部慢在哪
PL/pgSQL 函数中的嵌套语句默认不产生独立的计划记录（整条函数调用算一条语句）。排查「函数整体慢、不知道哪一句慢」时：
auto_explain.log_nested_statements = on开启后，函数内每条语句的计划都会作为附属输出记录在调用语句的日志条目之下。注意它与 sample_rate 的交互：官方说明嵌套语句要么全采要么全不采，因此开启此参数时建议把 sample_rate 保持为 1。
log_verbose 与 log_settings 的用途
两个低开销、按需开启的辅助开关：
log_verbose（默认 off）：等价于EXPLAIN VERBOSE，在每个节点上输出输出列与表达式的细节。排查「这个排序排的是什么列」「这个 Filter 条件被重写成什么样」时有用；代价是每行计划变长，日志体积明显增大，不建议长期开启。log_settings（默认 off）：在计划头部列出与内置默认值不同、且影响查询规划的配置项。它专治「同一份 SQL 在两套环境计划不同」的疑难杂症——把日志头一翻，work_mem、random_page_cost、effective_cache_size、max_parallel_workers_per_gather的当前值一目了然，省去逐台登机检查的功夫。
LOG: duration: 4218.992 ms plan:
Query Text: ...
Settings: effective_cache_size = '2GB', random_page_cost = '4', work_mem = '8MB'
...log_level：控制计划写到哪里去
auto_explain.log_level（默认 LOG）决定计划以什么日志级别写出。合法取值从 DEBUG5 到 WARNING（以及 LOG）。用途有两类：其一，把计划级别调高到 WARNING，让只收集 WARNING 以上级别的采集管道也能带上计划；其二，调低到 DEBUG 系列，让计划只在 debug 排障会话中出现，避免进入常规日志流。绝大多数场景保持默认 LOG 即可，与 log_min_messages 的默认配置兼容。
与 pg_stat_statements 的配合：定位计划劣化
两者组合是排查「计划劣化」的标准工作流。场景：某接口昨天 50ms、今天 3s，怀疑计划变了：
- pg_stat_statements 圈嫌疑：按
mean_exec_time环比找出劣化最明显的语句模板，拿到它的queryid与语句文本（见《pg_stat_statements 语句统计》一页）。 - auto_explain 拿证据：确认
auto_explain.log_min_duration低于该语句当前的均值（保证会命中），等它再次被记录——日志里就是案发时的真实计划。 - 对比历史计划：从变更管理记录或上次诊断窗口的日志里找出「快的时候」的计划，diff 两份计划的差异（通常是
Index Scan变Seq Scan、Nested Loop变Hash Join、估算行数与实际行数偏差悬殊）。 - 归因修复：估算偏差 →
ANALYZE表或调整统计目标；索引失效 → 检查参数类型、表达式写法；随机参数劣化 → 考虑plan_cache_mode或查询改写。
没有 auto_explain，第 2 步只能手工复现；而偶发劣化恰恰是最难复现的。
JSON 计划树的结构剖析
log_format = 'json' 时，计划以标准的嵌套 JSON 输出。理解它的骨架，采集端与自动化脚本才有据可依：
{
"Query Text": "SELECT ...",
"Plan": {
"Node Type": "Aggregate",
"Actual Rows": 1,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Relation Name": "orders",
"Actual Rows": 1000000,
"Actual Loops": 1,
"Plans": []
}
]
}
}字段与 text 格式的对应关系一目了然：Node Type 即算子类型、Actual Rows 对应 text 输出的 actual ... rows=、子节点收纳在 Plans 数组中。基于这个结构可以做的自动化：递归遍历 Plans 统计节点类型分布；计算每个节点 Plan Rows 与 Actual Rows 的偏差率；检测 Node Type = "Seq Scan" 且 Relation Name 属于大表的组合并触发告警。注意字段的精确命名以所用版本的实际输出为准，不同大版本之间偶有增补。
log_triggers 详解
log_triggers（需 log_analyze）把触发器的执行统计纳入计划输出：每个触发器的名称、关系与耗时单独成行。触发器热点表的排查价值很大——业务上「这条 UPDATE 慢」，真相可能是 AFTER 触发器里的 DML 或审计逻辑占了大头：
LOG: duration: 812.441 ms plan:
...
Trigger for constraint fk_check: time=1.204 calls=1
Trigger audit_log_trigger: time=796.512 calls=1没有这个开关时，触发器耗时被合并进语句总耗时，主查询的计划看起来平平无奇，排查会兜一个大圈子。对触发器密集的表（审计、级联汇总场景）建议常态化开启；它同样要求 log_analyze = on，因此遵循与 log_buffers 相同的开销模型。
JSON 格式对接采集器
log_format = 'json' 与 PG15+ 的 log_destination = 'jsonlog' 组合时，整条日志本身就是 JSON，计划作为字段值嵌入。采集链路：
PostgreSQL (jsonlog + auto_explain json)
→ Filebeat / Vector
→ Loki / Elasticsearch
→ Grafana / Kibana 看板在采集端可以做的自动化包括：解析 Plan 树提取 Node Type 分布、对比 Plan Rows 与 Actual Rows 的偏差率（超过 10 倍标红）、按模板聚合计划变更事件。计划劣化的发现从「人看日志」升级为「机器盯偏差」。
采样与限流的组合拳
把本页所有「量控」参数组合起来，形成一套完整的输出预算。目标：每天计划日志不超过 200MB。
# 组合方案示例
auto_explain.log_min_duration = '3s' # 只有 3s 以上才有资格
auto_explain.sample_rate = 0.25 # 再砍掉 3/4
auto_explain.log_timing = off # 不逐节点计时（省性能）
auto_explain.max_plan_depth = 30 # 截断超深计划
auto_explain.log_format = 'json' # 可压缩、可解析估算方法：观察慢查询日志中「3 秒以上语句」的每日条数 N，计划日志大小 ≈ N × 采样率 × 单条计划体积（text 约 2~10KB，JSON 约为其 3~5 倍）。数据驱动地调整 sample_rate，而不是拍脑袋。日志侧再叠加轮转与压缩（见《日志轮转与归档》），预算闭环。
实战示例
示例一：捕获 3 秒以上的语句计划
完整走一遍从配置到拿到计划的流程。
# 1. 配置预加载（重启生效）
$ sudo -u postgres vi $PGDATA/postgresql.conf
# session_preload_libraries = 'auto_explain'
# auto_explain.log_min_duration = '3s'
# auto_explain.log_analyze = on
# auto_explain.log_timing = off
# auto_explain.log_buffers = on
$ sudo systemctl reload postgresql
# 2. 验证参数已生效
$ psql -c "SHOW session_preload_libraries;"-- 3. 制造一条超过 3 秒的语句（大表模拟）
SELECT count(*) FROM big_table t1, big_table t2
WHERE t1.id = t2.id AND t1.payload LIKE '%x%';LOG: duration: 4218.992 ms plan:
Query Text: SELECT count(*) FROM big_table t1, big_table t2
WHERE t1.id = t2.id AND t1.payload LIKE '%x%';
Aggregate (actual rows=1.00 loops=1)
-> Hash Join (actual rows=1000000.00 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on big_table t1 (actual rows=1000000.00 loops=1)
Filter: (payload ~~ '%x%'::text)
Rows Removed by Filter: 9000000
-> Seq Scan on big_table t2 (actual rows=10000000.00 loops=1)即使没有逐节点计时（log_timing = off），rows 与 Rows Removed by Filter 也足以定位问题：两次全表扫描 + 过滤器丢弃了 900 万行。
示例二：会话内临时抓计划（零配置改动）
不能改配置文件的环境（如受限的云托管库、紧急排障），用会话级方案：
-- superuser 会话
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = on;
-- 在同一会话中执行业务代码路径（例如调用存储过程）
CALL process_pending_orders();
-- 用完关闭
SET auto_explain.log_min_duration = -1;LOAD 只影响当前会话，log_min_duration 的 SET 也只影响当前会话——这是把影响面降到最小的排查方式。非 superuser 用户不能 LOAD，需要管理员协助或使用 session_preload_libraries 方案。
示例三：JSON 格式对接采集
# PostgreSQL 侧
log_destination = 'jsonlog'
logging_collector = on
auto_explain.log_format = 'json'
auto_explain.log_min_duration = '2s'{"timestamp":"2026-10-07 15:02:11.301 CST","pid":40211,"error_severity":"LOG",
"message":"duration: 2318.442 ms plan: {\"Query Text\":\"SELECT ...\",\"Plan\":{\"Node Type\":\"Aggregate\",...}}",
"query":"..."}# 采集侧：抽出计划 JSON 交给 jq 分析节点类型
jq -r '.message' postgresql.json \
| sed 's/^duration: [0-9.]* ms plan: //' \
| jq -r '[.. | objects | .["Node Type"]?] | map(select(. != null)) | join(",")'拿到每条慢语句的节点类型序列后，就能在告警规则里写「该模板语句出现 Seq Scan」这类结构化条件。
示例四：计划劣化的完整排查
-- 第 1 步：pg_stat_statements 环比找劣化语句（详见下一页）
SELECT queryid,
calls,
round(mean_exec_time::numeric, 1) AS mean_ms,
round(total_exec_time::numeric / 1000, 1) AS total_s
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;# 第 2 步：确认 auto_explain 阈值能覆盖该语句
psql -c "SHOW auto_explain.log_min_duration;"
# 若当前均值为 3s，阈值 3s 只有一半命中，临时下调到 1s：
psql -c "ALTER SYSTEM SET auto_explain.log_min_duration = '1s';"
psql -c "SELECT pg_reload_conf();"# 第 3 步：等命中后从日志拿计划，与上周同语句的计划 diff
LOG: duration: 2874.112 ms plan:
Query Text: SELECT * FROM orders WHERE user_id = $1 AND status = 'pending' ORDER BY created_at DESC;
Index Scan using idx_orders_user_created on orders (actual rows=3.00 loops=1)
...-- 第 4 步：核对统计信息新鲜度（估算偏差的最常见根因）
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'orders';整个流程中，auto_explain 的产出（案发计划）是唯一不可替代的证据；其余步骤都可以在数据库之外的观察完成。
示例五：log_settings 定位环境差异
两套环境（预发与生产）同一条 SQL，预发 80ms、生产 4s，语句文本与表结构一致。开启 log_settings 后对比两边日志：
# 预发
LOG: duration: 82.441 ms plan:
Query Text: SELECT ... FROM orders WHERE created_at > now() - interval '7 days';
Settings: effective_cache_size = '8GB', random_page_cost = '1.1', work_mem = '64MB'
Index Scan using idx_orders_created on orders (actual rows=52310.00 loops=1)
# 生产
LOG: duration: 4218.992 ms plan:
Query Text: SELECT ... FROM orders WHERE created_at > now() - interval '7 days';
Settings: effective_cache_size = '512MB', random_page_cost = '4', work_mem = '8MB'
Seq Scan on orders (actual rows=52310.00 loops=1)Settings: 行直接暴露了根因：生产库的 random_page_cost = 4、effective_cache_size = 512MB 让优化器认为索引扫描不划算。修正参数后计划回归 Index Scan。这个例子也说明了 auto_explain 输出的另一个隐性价值——Settings 行的存在让「参数差异」这种跨环境排查从人肉登机变成读日志。
注意事项
log_analyze 的计时开销作用于全库所有语句，不是只有被记录的语句。 官方警告原文是「extremely negative impact」。长期开启必须配 log_timing = off；需要完整计时时使用限时窗口。这是本页最重要的一条。
shared_preload_libraries 修改后必须重启。 改配置文件后只 reload 是不够的，SHOW shared_preload_libraries 确认加载成功再排下一步。可以免重启的场景用 session_preload_libraries 或会话内 LOAD。
默认一切都不输出。 模块加载只是前提，auto_explain.log_min_duration 必须显式设置（默认 -1 完全关闭）。排查「加载了但没日志」时第一件事就是查这个参数的值与来源。
计划日志的体积可能是语句日志的几十倍。 复杂计划的 text 输出可达数百行，JSON 更是体积惊人。务必让 auto_explain.log_min_duration 高于 log_min_duration_statement，并确认采集管道与磁盘轮转配置能承受。
auto_explain 不提供 SQL 接口。 它没有视图、没有函数，一切输出都走服务器日志。想用 SQL 查历史计划的场景，应使用 pg_stat_statements 的统计配合日志归档，或第三方方案（如 pgwatch 的计划留存能力，见 17.4 节）。
嵌套语句与函数调试注意组合。 log_nested_statements 开启后函数内语句逐条记录，配合 log_analyze 时开销同样作用于函数内所有语句；sample_rate < 1 时嵌套语句整体采或不采，不要指望函数内部分语句被采样。
参数截断要单独设置。 auto_explain.log_parameter_max_length 独立于全局的 log_parameter_max_length，默认 -1（完整记录绑定参数）。含敏感参数的语句要在模块级显式截断，防止计划日志泄露数据。
云托管环境的可用性要单独确认。 多数云厂商允许在参数组里设置 shared_preload_libraries 与 auto_explain.*，但少数托管产品限制了预加载库清单或对 LOAD 权限有额外约束，动手前先确认所在平台的说明。
与 pg_stat_statements 共用预加载配置时注意顺序与重复。 两个库都写入同一个 shared_preload_libraries 列表，逗号分隔、不重复；改这条列表是最常见的「需要重启却只做了 reload」的失误点。上线前用 SHOW shared_preload_libraries; 核对最终形态。
计划日志记录的是「案发时刻」的快照，不会自动长期留存。 日志轮转后旧计划随之消失。重要的劣化证据要复制归档（贴进工单或事故报告），不要指望日志文件长期保留——它与慢查询日志共用同一套轮转策略。
复盘时计划要与统计信息快照一起留。 只有计划没有当时的 pg_stats（last_analyze 时间、n_distinct、相关性）与表行数，事后很难判断估算偏差的成因。事故归档时把计划、统计摘要、修复动作三者打包在一起。
最佳实践
- 生产库用
session_preload_libraries起步，验证一个迭代后再改shared_preload_libraries。 免重启意味着可以随时一键关闭，把「引入新监控组件」的风险降到最低；确认稳定后再固化到重启窗口。 - 两级阈值分层：慢查询日志 1s、计划日志 3s。 计划输出很贵，只给最严重的 1% 语句留计划。两个阈值都要写进 runbook，而不是散落在某台机器的配置里。
- 长期开启的配置必须是
log_analyze = on+log_timing = off+log_buffers = on。 这是官方文档推荐的低开销形态：拿得到行数偏差与缓冲区统计，付得起计时开销的缺席。 - 计划格式统一用 JSON 并接入采集管道。 text 格式留给人肉应急；常态监控下结构化的计划才能被自动分析（节点类型统计、估算偏差率、计划变更事件）。
- 把「auto_explain 计划留存」纳入版本升级验收。 大版本升级后计划可能整体变化，升级窗口开启
log_min_duration = 1s的 auto_explain，对比升级前后的 Top SQL 计划，是最快的回归发现手段。 - 与 pg_stat_statements 组成固定工作流：统计层找嫌疑、计划层拿证据。
mean_exec_time环比异常 → 等待 auto_explain 命中 → 计划 diff → 归因修复。四步都写进故障处理手册。 - 触发器热点表开启
log_triggers。 业务表触发器逻辑复杂时，触发器自身耗时可能占语句大头；log_triggers（需log_analyze）能把每个触发器的耗时单独列出，避免冤枉主查询。 - 高并发大表集群用
sample_rate控制量。 慢语句每分钟数百条时，sample_rate = 0.1足以收集到代表性计划；同类语句计划高度一致时无需全量留存。 - 敏感字段语句显式设置
log_parameter_max_length。 计划日志默认记录完整绑定参数，比慢查询日志更容易携带敏感数据；在模块级截断，并把计划日志纳入含敏数据管理。 - 深计划场景调低
max_plan_depth。 数千分区的表、超复杂视图嵌套可能产出巨型计划；采集端出现超大日志告警时优先调整该参数，而不是盲目加磁盘。 - 建立「计划档案」习惯。 把每次事故的 auto_explain 计划、当时的统计信息快照、修复动作归档到一起。下次同类问题直接对照档案，定位时间从小时级降到分钟级。
- 记住它只是「现场层」工具。 auto_explain 给出的是计划证据，锁等待、连接堆积、硬件瓶颈不在它的视野内；完整定位必须回到
pg_stat_activity/pg_locks与系统指标，最终汇入本节第四页的优化流程。
下一节
参考链接
- PostgreSQL 官方文档：F.3 auto_explain — log execution plans of slow queries
- PostgreSQL 官方文档：EXPLAIN 输出格式（计划解读）
- PostgreSQL 官方文档：Using EXPLAIN（计划解读教程）
- PostgreSQL 官方文档：Error Reporting and Logging（日志载体配置）
- PostgreSQL 官方文档：Using JSON-Format Log Output
- PostgreSQL 官方文档：Client Connection Defaults（session_preload_libraries / shared_preload_libraries）
- PostgreSQL 官方文档：Planner Cost Constants（计划相关参数）
- PostgreSQL 官方文档：pg_stat_statements 扩展
- PostgreSQL 官方文档：Routine Database Maintenance Tasks（ANALYZE 与统计）
- PostgreSQL Wiki：Performance Optimization（性能排查入口）