📦 Postgres Query Optimizer — Postgres 查询优化器

PostgreSQL 查询优化器 通过解读 EXPLAIN ANALYZE 输出，定位慢查询瓶颈，推荐索引、改写 SQL 并提供配置调优建议，扮演资深 DBA 角色。

用法 遇到慢查询时调用： 基础调用： Optimize this query: SELECT FROM orders WHERE created_at > '2026-01-01' AND status = 'pending' Analyze this EXPLAIN ANALYZE output: [粘贴输出] Why is this query slow? [粘贴查询/计划]

带上下文：

• 这是我的表结构与慢查询，请建议索引
• 解释计划每个节点的含义及瓶颈
• 查询 12 秒，目标 <200 ms，如何优化

工作原理

• 理解查询与上下文

- 解析 SQL：表、连接、过滤、排序、子查询、CTE、窗口函数 - 获取表结构：\d table_name、列类型、索引、约束、外键 - 查看统计信息：行数、数据分布、膨胀

• 深度分析 EXPLAIN ANALYZE

逐节点检查： - Seq Scan：表大小是否合理，过滤选择性 - Index Scan：索引是否最优，能否覆盖 - Index Only Scan：VM 新鲜度，需 VACUUM？ - Bitmap Index/Heap Scan：多索引合并效率 - Nested Loop：外集大时警告 O(nm) - Hash Join：work_mem 是否足够，是否落盘 - Merge Join：是否预排序，排序是否瓶颈 - Sort：内存 or 磁盘，能否用索引避免 - Aggregate：HashAggregate vs GroupAggregate - Materialize/SubPlan：是否重复执行

关键指标示例： Sort Actual Time: 1,247 ms（占总耗时 89%） Sort Method: external merge Disk: 98 MB 建议：work_mem 提到 128 MB 或在排序列建索引

• 按影响排序瓶颈

1) 大表全表扫描 问题：orders 全表扫描 240 万行，过滤后仅 2.1% 解决：CREATE INDEX idx_orders_status_created ON orders (status, created_at) WHERE status='pending';

2) 大集 Nested Loop 问题：外 5 万行×内 200 次索引查找=1 千万次随机 IO 解决：改 Hash Join，确保统计信息最新，必要时调整 join_collapse_limit 或用 CTE 重构

3) 磁盘排序 问题：external merge 245 MB，work_mem 仅 4 MB 解决：SET work_mem='256MB' 或建排序列索引，或 LIMIT+子查询取 Top-N

4) 相关子查询 问题：SubPlan 每行执行一次 解决：改写成 JOIN 或 LATERAL，确保有合适索引

最终输出可执行的索引 DDL、参数调整、改写后的 SQL 及预期收益。