PostgreSQL 查询优化器

通过解析 EXPLAIN ANALYZE 输出，分析慢查询，识别性能瓶颈，推荐索引，建议重写查询，并提供配置调优建议。本技能扮演专家级数据库管理员（DBA）的角色，审查您的查询执行计划。

使用方法

在遇到慢速 PostgreSQL 查询并需要理解其原因及修复方案时，调用此技能。

基础调用方式：

优化这个查询：SELECT * FROM orders WHERE created_at > '2026-01-01' AND status = 'pending'

分析这个 EXPLAIN ANALYZE 输出：[粘贴输出内容]

为什么这个查询这么慢？[粘贴查询语句和/或执行计划]

带上下文的调用：

这是我的表结构和慢查询，请建议添加索引

我有这个查询执行计划，请解释每个节点的含义以及瓶颈所在

这个查询耗时 12 秒，目标是低于 200 毫秒 —— 帮我实现目标

该代理会分析查询语句、执行计划（如有提供）以及表结构，生成可操作的优化建议。

工作原理

第一步：理解查询与上下文

代理首先读取 SQL 查询并收集相关信息：

• 解析查询：识别涉及的表、连接方式、WHERE 条件、GROUP BY、ORDER BY、子查询、CTE 和窗口函数
• 识别表结构：请求或查找 \d table_name 的输出，了解字段、数据类型、现有索引、约束和外键关系
• 检查表统计信息：若可用，查看行数、数据分布情况以及表膨胀程度

-- 代理可能要求您运行以下命令以获取更多上下文：
\d+ table_name                           -- 显示表结构及其大小
SELECT reltuples::bigint FROM pg_class WHERE relname = 'table_name';  -- 获取行数
SELECT * FROM pg_stats WHERE tablename = 'table_name' AND attname = 'column';  -- 字段统计信息

-- 或检查现有索引：
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'table_name';

-- 检查表膨胀情况：
SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_size,
       pg_size_pretty(pg_relation_size('table_name')) AS table_size,
       pg_size_pretty(pg_indexes_size('table_name')) AS index_size;

第二步：分析 EXPLAIN ANALYZE 输出

如果用户提供了 EXPLAIN ANALYZE 输出，代理将对每个执行节点进行深入分析。若未提供，代理会请求该输出或基于常见模式进行推断。

-- 代理建议运行：
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <你的查询>;

-- 如需更详细信息：
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL, FORMAT TEXT) <你的查询>;

逐节点分析：

代理读取执行计划树，并评估每个节点的表现：

代理提取的关键指标示例：

节点：Sort
  实际耗时：1,247ms（占总查询时间的 89%） <-- 瓶颈已识别
  处理行数：1,200,000
  排序方法：external merge  <-- 磁盘排序（work_mem 设置过低）
  排序空间使用量：98MB
  建议：将 work_mem 提升至 128MB，或在排序列上创建索引

第三步：识别性能瓶颈

代理按影响程度对瓶颈进行排序：

1. 大表上的顺序扫描

问题：orders 表的 Seq Scan（处理行数：2,400,000）
  过滤条件：(status = 'pending' AND created_at > '2026-01-01')
  被过滤掉的行数：2,350,000
  选择性：2.1% —— 建议创建索引

解决方案：CREATE INDEX idx_orders_status_created
  ON orders (status, created_at)
  WHERE status = 'pending';  -- 若 status='pending' 是高频查询，可使用部分索引

2. 外部结果集较大的嵌套循环

问题：Nested Loop（外部行数：50,000，每次内部循环约 200 行）
  内部执行总次数：50,000 × 索引扫描 = 10,000,000 次行查找

解决方案：
  - 考虑改写为 Hash Join：
    • 确保内表有足够统计信息（执行 ANALYZE）
    • 检查 join_collapse_limit 参数（多表连接时）
    • 通过 CTE 重构显式加入连接提示

3. 磁盘排序与溢出

问题：排序方法为 external merge，磁盘使用 245MB
  work_mem 设置为 4MB，但排序需求达 245MB

解决方案：
  - SET work_mem = '256MB';  -- 当前会话/查询级别设置
  - 或创建索引：CREATE INDEX idx_orders_created ON orders (created_at DESC)
  - 若只需前 N 条结果，可采用 LIMIT + 子查询模式

4. 相关子查询

问题：SubPlan 每次外层行都执行一次
  SELECT *, (SELECT max(amount) FROM payments p WHERE p.order_id = o.id)
  FROM orders o;
  -- SubPlan 执行了 500,000 次

解决方案：改写为 JOIN：
  SELECT o.*, p.max_amount
  FROM orders o
  LEFT JOIN (SELECT order_id, max(amount) AS max_amount
             FROM payments GROUP BY order_id) p
  ON p.order_id = o.id;

5. 统计信息不佳导致基数估算错误

问题：预计行数：100，实际行数：450,000
  计划器因估算不准选择了 Nested Loop

解决方案：
  - ANALYZE table_name;  -- 更新统计信息
  - ALTER TABLE table_name ALTER COLUMN col SET STATISTICS 1000;
  - 检查是否存在相关列：使用 CREATE STATISTICS

第四步：推荐索引

技能：Postgres 查询优化器

版本：1.0.1

分块：2/3

该代理根据查询模式推荐索引：

索引推荐逻辑：

1. 复合索引中优先考虑等值条件（WHERE status = 'active'）
2. 次要考虑范围条件（WHERE created_at > '2026-01-01'）
3. 在可能的情况下，将 ORDER BY 列作为索引的尾部列
4. 使用 INCLUDE 列创建覆盖索引，避免堆表访问（heap fetch）
5. 当查询始终过滤特定值时，使用部分索引（Partial Index）
6. 对 WHERE 子句中的函数调用使用表达式索引（Expression Index）

-- 针对常见查询模式的复合索引
CREATE INDEX idx_orders_status_created_at
  ON orders (status, created_at DESC);

-- 覆盖索引以支持仅索引扫描（Index Only Scan）
CREATE INDEX idx_orders_covering
  ON orders (status, created_at DESC)
  INCLUDE (total_amount, customer_id);

-- 针对高频路径的部分索引
CREATE INDEX idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

-- 针对计算字段的表达式索引
CREATE INDEX idx_users_lower_email
  ON users (lower(email));

-- JSONB 查询使用的 GIN 索引
CREATE INDEX idx_events_metadata
  ON events USING gin (metadata jsonb_path_ops);

-- 适用于追加型时间序列表的 BRIN 索引
CREATE INDEX idx_logs_created
  ON application_logs USING brin (created_at);

索引成本效益分析：

该代理估算以下指标：

• 写入开销：每个索引增加约 10%-15% 的写延迟
• 存储成本：基于列类型和行数估算索引大小
• 读取性能提升：从全表扫描（Seq Scan）到索引扫描（Index Scan）的预期加速
• 索引是否实际被使用（基于选择性和规划器行为判断）

第五步：建议查询重写

该代理建议进行结构优化：

常见重写模式：

示例重写 — 分页优化：

-- 慢：基于偏移量的分页
SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 500000;
-- 扫描并丢弃 500,000 行

-- 快：键集分页
SELECT * FROM events WHERE id > 500000 ORDER BY id LIMIT 20;
-- 直接通过索引定位到目标位置

第六步：配置调优

该代理检查 PostgreSQL 配置是否影响性能：

-- 代理评估的关键设置：
SHOW work_mem;              -- 排序/哈希内存（默认 4MB 常常过低）
SHOW shared_buffers;        -- 应约为内存的 25%
SHOW effective_cache_size;  -- 应为内存的 50%-75%
SHOW random_page_cost;      -- SSD 为 1.1，HDD 为 4.0
SHOW effective_io_concurrency; -- SSD 为 200，HDD 为 2
SHOW max_parallel_workers_per_gather; -- 并行查询工作进程数
SHOW jit;                   -- JIT 编译（可能影响短查询性能）

常见配置建议：

发现：work_mem = 4MB，但你的查询需排序 245MB 数据
  建议：在本会话中设置 work_mem = '256MB'
  注意：不要全局设得过高 —— 应乘以 max_connections

发现：random_page_cost = 4.0，但你使用的是 SSD
  建议：设置 random_page_cost = 1.1
  影响：规划器将更倾向于使用索引扫描而非顺序扫描

发现：effective_cache_size = 4GB，但服务器有 32GB 内存
  建议：设置 effective_cache_size = '24GB'
  影响：规划器将更信任数据可能已被缓存

第七步：生成优化报告

该代理输出结构化报告：

# 查询优化报告

## 查询
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC LIMIT 50;

## 当前性能
  执行时间：3,847ms
  计划时间：2.1ms
  返回行数：50
  扫描行数：2,400,000（比返回行数多 48,000 倍）

## 性能瓶颈（按影响程度排序）
  1. orders 表上的全表扫描（240 万行）—— 占比 78% 的查询时间
  2. created_at 上的排序 —— 因 work_mem 过低导致溢出到磁盘
  3. 连接无问题（customers 表较小，哈希连接表现良好）

## 优化建议
  1. CREATE INDEX idx_orders_pending_created
     ON orders (created_at DESC)
     WHERE status = 'pending';
     预期提升：3,847ms → ~15ms

  2. SET work_mem = '64MB'（用于排序操作）
     预期提升：消除磁盘排序

## 优化后预期结果
  索引扫描 idx_orders_pending_created → ~5ms
  与 customers 的嵌套循环连接 → ~10ms
  不再需要排序（索引已提供排序顺序）
  总耗时：~15ms（提升约 256 倍）

输出内容

该代理生成以下内容：

• 查询分析：对查询结构的解析分解
• 执行计划解释：对每个 EXPLAIN 节点的通俗语言说明
• 瓶颈排序：按影响程度排列的性能问题列表
• 索引建议：包含理由的具体 CREATE INDEX 语句
• 查询重写建议：实现相同结果但更快的 SQL 替代方案
• 配置建议：影响此查询的 PostgreSQL 设置
• 预期改进：优化后的预计执行时间
• 警告提示：推荐变更可能带来的风险（写入开销、索引膨胀、计划回归）

常见场景

“我的查询很慢，但不知道原因”

请提供查询语句和表结构。该代理将解释可能的执行计划，并建议运行 EXPLAIN ANALYZE 进行诊断。

我有一个 EXPLAIN ANALYZE 的输出，它是什么意思？

请粘贴完整的输出内容。该代理将解释每个执行节点，识别性能瓶颈，并提出优化建议。

我的应用应该创建哪些索引？

提供最常见的查询语句（或让代理分析 pg_stat_statements）。代理将推荐一组最小但能覆盖关键路径的索引。

我的查询之前很快，现在变慢了

代理会检查以下可能原因：统计信息过期、表膨胀、ANALYZE 后执行计划退化、数据量增长突破规划器阈值、索引损坏等。建议执行 ANALYZE、REINDEX 或使用计划绑定（plan pinning）。

我需要优化一个涉及数百万行数据的迁移查询

代理将推荐分批处理策略、临时表使用模式以及锁机制的注意事项。

获得最佳结果的建议

• 尽可能提供 EXPLAIN (ANALYZE, BUFFERS) 的输出——如果没有此信息，代理只能基于查询语句进行推理
• 包含表结构定义（使用 \d+ table_name），以便代理了解现有索引情况
• 说明所使用的 PostgreSQL 版本——不同版本的优化器能力差异较大（例如并行查询、JIT 编译、增量排序等功能）
• 提供表中的行数，以便代理评估选择性
• 如果查询通过 ORM 执行（如 Django、Rails、SQLAlchemy），请提供生成的 SQL 语句，而非 ORM 代码
• 对于持续的性能优化，可导出 pg_stat_statements 的输出，用于工作负载级别的分析