PostgreSQL EXPLAIN ANALYZE 入门指南

1. 总体概览

EXPLAIN ANALYZE 是 PostgreSQL 中最重要的工具，用来诊断查询性能问题。本文帮助你理解输出的各个部分：节点类型、成本、行数、实际执行时间，并了解如何识别有问题的查询。阅读完本文后，你将能够自信地读懂执行计划，并知道应该在哪里添加索引或修改查询。

2. EXPLAIN 与 EXPLAIN ANALYZE 的区别

EXPLAIN 只显示估计计划（estimated plan），它基于统计数据但不实际执行查询。EXPLAIN ANALYZE 则真正运行查询并测量实际执行时间。

-- 仅查看估计计划，不执行查询
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- 真正执行查询，测量实际时间
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

注意： EXPLAIN ANALYZE 会真正执行查询，所以如果是 UPDATE 或 DELETE，数据会被修改。如果只想测试，请在事务中使用并 ROLLBACK：

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. 计划中单个节点的结构

输出中的每一行都是一个节点 —— 处理数据的一个步骤。以 orders 表为例：

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

各部分解释如下：

Seq Scan on orders：节点类型，这里是顺序扫描整个表。
cost=0.00..1834.00：估计成本（启动成本 .. 总成本），是相对单位，不是毫秒。
rows=10：计划器估计会返回的行数。
width=72：每行的平均大小（字节）。
actual time=0.015..12.430：实际执行时间（毫秒） —— 返回第一行的时间 .. 完成整个节点的时间。
rows=8 loops=1：实际返回的行数，以及该节点执行的次数（loops）。
Rows Removed by Filter：扫描但不符合过滤条件的行数 —— 这个数字很大表示缺少索引。

4. 比较 Seq Scan 和 Index Scan

4.1. 没有索引时

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL 必须读取整个表然后逐行过滤。对于大表，这是导致查询速度慢的主要原因。

4.2. 添加索引后

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

比较：成本从 1834 下降到 8.31，执行时间从 12.465 ms 下降到 0.045 ms。这是一个经典的例子，说明索引如何帮助 PostgreSQL 直接跳转到需要的行，无需扫描整个表。

注意： 即使有可用的索引，PostgreSQL 也不一定会使用它。如果查询返回表的大部分数据（例如 > 20-30% 的行数），计划器通常会选择 Seq Scan，因为与通过索引随机跳跃然后返回表（随机 I/O）相比成本更低。

5. 阅读包含 JOIN 的计划

当查询包含 JOIN 时，计划会有多个嵌套节点。子节点先执行，结果传给父节点。

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

阅读方法：Hash Join 是最外层的节点（根节点），它从两个子节点接收数据（Seq Scan on orders 和包含 Seq Scan on customers 的 Hash 节点）。PostgreSQL 从已过滤的 customers 表构建哈希表，然后扫描 orders 并与哈希表匹配。父节点的 actual time 已经包含了内部子节点的执行时间。

6. 需要关注的警告信号

6.1. 估计值与实际值差异大

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

计划器估计 rows=10 但实际返回 9500 行。这样大的差异通常是由于统计数据过期。可以通过以下方式修复：

ANALYZE orders;

这个命令更新数据分布统计，帮助计划器做出更准确的估计。

6.2. 异常大的 Loops（嵌套循环）

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 意味着 Index Scan on orders 节点被调用了 1000 次（对应来自外部节点的 1000 行）。如果每次调用都需要花费可观的时间，总成本会很大，即使该节点的 actual time 单独看起来很小。请将 actual time 乘以 loops 来估计节点的真实总执行时间。

6.3. 使用 `BUFFERS` 查看 I/O

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 意味着 4 个块从缓存（内存）读取，没有触及磁盘。如果看到 shared read 很大，说明数据必须从磁盘读取 —— 缓存未命中的信号，可能是因为 shared_buffers 太小或查询扫描了过多的冷数据。

7. 在 Java/Spring 代码中使用 EXPLAIN

当在 Spring Boot 应用中调试性能时，可以通过 JdbcTemplate 直接记录计划：

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze 接收原始 SQL 语句，用 EXPLAIN (ANALYZE, FORMAT TEXT) 包装它，并以 String 的形式返回计划的每一行。在 dev/staging 环境中使用它来记录检测到查询缓慢时的计划。

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

这个测试不会断言具体数字（因为执行时间取决于机器），只是确认返回的计划包含 Execution Time 这一行 —— 足以验证语句成功执行并返回有效的计划。

注意： 对于包含 INSERT/UPDATE/DELETE 的查询，不应该在生产环境上运行 EXPLAIN ANALYZE 而不用事务 + ROLLBACK 包装，因为它会真正执行。

8. 何时不需要 EXPLAIN ANALYZE

查询已经很快且稳定（低于几毫秒，数据量小）—— 不需要进一步优化，投入不值得。
处于早期 schema 设计阶段，没有足够的真实数据让统计数据正确反映现实（行数少的话 Seq Scan 总是比 Index Scan 更优）。
查询在小的临时表（TEMP TABLE）上运行，只运行一次 —— 分析开销相比于收益不值得。

9. 总结

EXPLAIN ANALYZE 让你看到 PostgreSQL 认为会发生什么（成本、估计行数）和实际发生了什么（实际执行时间、实际行数）之间的差异。阅读计划时，要注意执行顺序（从里到外）、节点类型（Seq Scan 或 Index Scan），特别是估计值与实际值的偏差 —— 这是判断查询是否需要添加索引、是否需要重新 ANALYZE 表、或是否需要重写逻辑的关键。