PostgreSQL 查询计划诊断

慢查询诊断的核心痛点

当 PostgreSQL 遇到复杂查询时，常出现「执行慢却找不到具体原因」的问题：

• 执行计划缺乏运行时实际数据（如实际扫描行数、执行时间），仅靠静态分析难以定位真实瓶颈；
• pg_stat_statements 等统计扩展的原始数据需要人工解读，容易遗漏高频慢查询模式；
• 多查询场景下，重复执行 EXPLAIN 并对比结果效率极低，且易因参数或环境差异导致结论偏差。

技能的核心工作路径

该技能围绕执行计划解析与统计关联分析展开，核心步骤如下：

1. 结构化执行计划采集：通过 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 获取包含运行时统计信息（实际耗时、内存使用、磁盘 I/O）与缓冲区访问情况的结构化计划，避免人工阅读文本格式的歧义；
2. 统计维度关联分析：结合 pg_stat_statements 的查询执行历史（如总调用次数、平均执行时间、共享缓存命中率），识别反复出现的慢查询模式，而非仅分析单次执行的偶发问题；
3. 瓶颈类型识别：自动标记三类常见性能问题——全表扫描（Seq Scan）导致的 I/O 瓶颈、嵌套循环（Nested Loop）在小表 / 大结果集场景下的效率问题、索引选择异常（如未命中预期索引）；
4. 索引优化建议生成：基于执行计划中的扫描方式与 pg_stat_statements 的历史数据，推荐针对性索引（如覆盖索引、部分索引），并提示需验证的场景（如高频更新的表）。

适用边界与注意事项

• 需提前开启 pg_stat_statements 扩展（需在 postgresql.conf 中配置 shared_preload_libraries 并重启数据库），否则无法获取历史执行统计；
• EXPLAIN (FORMAT JSON) 格式需 PostgreSQL 12 及以上版本支持，低版本需调整格式参数；
• 索引建议仅为初步诊断结果，需结合表数据分布（如 pg_stats）与写入负载验证，避免盲目添加索引导致写入性能损耗；
• 该技能基于 node-postgres 客户端，原生 libpq 绑定为可选依赖，纯 JavaScript 模式下对极复杂查询的执行计划解析可能存在性能限制。