由 John Doe 十月 10, 2025
某些 SQL 查询大多数情况运行性能表现良好，偶尔却执行极慢难以接受。那么，应该如何处理呢？
特性提交日志
让 auto_explain 在详细模式下输出查询标识符。
当auto_explain.log_verbose设为开启（on）时，auto_explain应在日志中输出与EXPLAIN (VERBOSE)等效的执行计划。然而，此前当compute_query_id开启时，查询标识符并未显示，该标识符仅会由EXPLAIN (VERBOSE)输出。本次修改使auto_explain在这一功能上与EXPLAIN保持一致。需注意，与EXPLAIN类似，当compute_query_id = regress时，auto_explain也不会显示查询标识符。
讨论：https://postgr.es/m/1ea21936981f161bccfce05765c03bee@oss.nttdata.com
为什么需要 auto_explain 输出查询标识符？
auto_explain 是 PostgreSQL 的一个官方扩展，可自动记录慢查询（或指定条件的查询）的执行计划到日志，无需手动执行EXPLAIN，是排查慢查询性能问题的一个重要工具。
设置参数compute_query_id后，PostgreSQL 会为每个查询生成唯一的 64 位标识，用于唯一标记逻辑相同的查询（如不同参数的 SELECT * FROM t WHERE id = ?），常与pg_stat_statements（查询性能统计视图）、pg_stat_activity（后端活动视图）联动。
在该特性之前，auto_explain存在一个关键局限。当auto_explain.log_verbose = on时，日志仅输出执行计划细节（如表名、索引名、过滤条件），但不包含查询标识符；而pg_stat_statements等工具仅通过查询标识符关联查询的性能统计（如总执行时间、调用次数、WAL 消耗）。若系统出现慢查询，运维人员无法通过pg_stat_statements中的查询标识符，直接定位该查询在auto_explain日志中输出的执行计划，需手动比对查询文本（易因参数不同导致匹配失败），排查效率低下。
本次提交恰好解决了这个问题，当开启详细日志模式（auto_explain.log_verbose = on）时，auto_explain会像EXPLAIN (VERBOSE) 一样输出查询标识符。
示例
例如，在生产环境中的某个业务操作出现性能抖动，我们需要定位 “时快时慢” 的不稳定查询。
首先在postgresql.conf中设置相关参数，重启数据库或重新加载配置：
shared_preload_libraries = 'auto_explain,pg_stat_statements' # 需包含这两个扩展
auto_explain.log_verbose = on # 开启详细模式，触发查询标识符打印
auto_explain.log_min_duration = 500 # 记录执行超 500ms 的慢查询计划
compute_query_id = on # 开启查询标识符生成
pg_stat_statements.track = all # 跟踪所有查询的统计数据
执行下面的查询，找出那条 “平均时间可接受，但偶尔卡顿” 的查询：
-- 筛选：平均时间 < 100ms，但标准差 > 50ms（波动大）的查询
SELECT
queryid,
query,
calls,
mean_exec_time,
stddev_exec_time,
-- 计算“标准差/平均值”比例，评估相对波动（比例越高越不稳定）
(stddev_exec_time / mean_exec_time)::numeric(5,2) AS ratio
FROM pg_stat_statements
WHERE
mean_exec_time < 100 -- 平均时间较短
AND stddev_exec_time > 50 -- 但波动较大
AND calls > 100 -- 执行次数足够多，统计有意义
ORDER BY ratio DESC
LIMIT 1;
在上面查询输出的结果中，queryid的值是 -839027140417230224。查看 PostgreSQL 日志，通过queryid的值，找到如下的auto_explain输出：
2025-01-01 10:30:00.123 UTC [12345] LOG: auto_explain:
Query Text: SELECT t1.id, t2.name, COUNT(t3.order_id) FROM orders t1 JOIN users t2 ON t1.user_id = t2.id LEFT JOIN order_items t3 ON t1.id = t3.order_id WHERE t1.create_time >= '2025-01-01' GROUP BY t1.id, t2.name;
Plan:
HashAggregate (cost=1200.00..1250.00 rows=5000 width=24)
Group Key: t1.id, t2.name
-> Hash Left Join (cost=800.00..1100.00 rows=20000 width=20)
Hash Cond: (t1.id = t3.order_id)
-> Hash Join (cost=400.00..600.00 rows=10000 width=16)
Hash Cond: (t1.user_id = t2.id)
-> Seq Scan on orders t1 (cost=0.00..200.00 rows=10000 width=8)
Filter: (create_time >= '2025-01-01 00:00:00'::timestamp without time zone)
-> Hash (cost=300.00..300.00 rows=8000 width=12)
Buckets: 16384 Batches: 1 Memory Usage: 400kB
-> Seq Scan on users t2 (cost=0.00..300.00 rows=8000 width=12)
-> Hash (cost=300.00..300.00 rows=10000 width=8)
Buckets: 16384 Batches: 1 Memory Usage: 300kB
-> Seq Scan on order_items t3 (cost=0.00..300.00 rows=10000 width=8)
Query Identifier: -839027140417230224
在上面输出的执行计划中，访问orders表时使用了顺序扫描，说明该表可能在create_time列上缺少索引。找出有问题的查询计划以后，我们就可以对查询进行对应的优化调整了。
总结
auto_explain输出查询标识符的特性，看似微小却解决了运维排查中的关键“断联”问题。它让auto_explain日志从“仅展示执行计划”升级为“连接计划与统计数据的桥梁”，大幅简化了慢查询从“发现”到“分析”再到“优化”的全流程。
若需使用该特性，只需确保auto_explain.log_verbose = on和compute_query_id = on，即可在日志中获取查询标识符，无缝衔接pg_stat_statements等统计工具，提升 PostgreSQL 性能排查效率。
非常不错的改进，感谢社区的所有相关人员。
参考
提交日志：https://git.postgresql.org/pg/commitdiff/9d2d9728b8d546434aade4f9667a59666588edd6