3. 执行计划深度解读

在 PostgreSQL 中，SQL 语句从提交到返回结果，会经历解析、重写、逻辑优化、物理计划生成、执行器执行等多个阶段。执行计划就是优化器为这条 SQL 选出的“物理执行路线”。EXPLAIN 展示优化器估算的计划，EXPLAIN ANALYZE 则真正执行语句并返回实际运行数据。两者结合，才能看清“优化器以为会怎样”和“实际发生了什么”之间的差距。

一、两种命令的输出差异

先看一个典型示例。假设有一张订单表 orders，包含 order_id、customer_id、status、created_at 等字段，并在 customer_id 上建有索引。

EXPLAIN
SELECT * FROM orders WHERE customer_id = 10086;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 10086;

EXPLAIN 输出类似：

Index Scan using idx_orders_customer on orders
  (cost=0.43..8.45 rows=1 width=120)
  Index Cond: (customer_id = 10086)

EXPLAIN ANALYZE 输出则多出实际执行信息：

Index Scan using idx_orders_customer on orders
  (cost=0.43..8.45 rows=1 width=120)
  (actual time=0.025..0.027 rows=3 loops=1)
  Index Cond: (customer_id = 10086)
Planning Time: 0.180 ms
Execution Time: 0.052 ms

关键差异在于：cost、rows、width 是估算值；actual time、rows、loops 是真实执行值。当估算行数与实际行数偏差很大时，优化器很可能选错了连接方式或扫描方式。

二、成本模型：优化器如何“算账”

PostgreSQL 的成本模型由一组代价参数控制，常见的有：

• seq_page_cost：顺序读取一个页面的代价，默认 1.0。
• random_page_cost：随机读取一个页面的代价，默认 4.0。
• cpu_tuple_cost：处理一行数据的 CPU 代价，默认 0.01。
• cpu_index_tuple_cost：处理一个索引项的 CPU 代价，默认 0.005。
• cpu_operator_cost：执行一次操作符或函数的代价，默认 0.0025。

优化器会为每个候选计划计算总成本，选择成本最低者。成本由 I/O 成本和 CPU 成本组成。例如全表扫描的成本大致为：

cost ≈ 页数 × seq_page_cost + 行数 × cpu_tuple_cost

而索引扫描的成本则包括索引页读取、索引项处理、回表随机读取以及过滤 CPU 成本。理解这些参数，有助于判断为什么优化器在某些场景下“宁愿全表扫描也不走索引”。

三、行数估算：统计信息决定计划质量

优化器依赖 pg_statistic 中的统计信息来估算行数。统计信息由 ANALYZE 收集，核心包括：

• 列的空值比例 null_frac。
• 列的平均宽度 avg_width。
• 不同值数量 n_distinct。
• 最常见值列表 most_common_vals 及其频率 most_common_freqs。
• 直方图边界 histogram_bounds。

例如，若 status 列有 90% 的行是 'paid'，优化器通过 MCV 知道 status = 'paid' 的选择率约为 0.9，从而估算出约 90% 的行需要返回，此时索引扫描通常不划算。反之，若查询条件是 status = 'refunded' 且只占 0.1%，索引扫描就更可能被选中。

估算行数不准的常见原因包括：统计信息过期、数据分布倾斜、多列相关性未被捕获、表达式或函数条件无法使用统计信息。此时可以通过 ANALYZE 更新统计信息，或对相关列创建扩展统计信息：

CREATE STATISTICS st_orders_customer_status
ON customer_id, status FROM orders;

ANALYZE orders;

四、估算与真实执行的差异定位

使用 EXPLAIN ANALYZE 时，重点观察每个节点的 rows 估算值与 actual rows 实际值。若两者相差一个数量级以上，通常意味着统计信息不足或条件选择率估算错误。此时应检查：

1. 该表最近是否执行过 ANALYZE？
2. 查询条件是否使用了函数或类型转换，导致无法利用统计信息？
3. 是否存在多列相关性，需要扩展统计信息？
4. 是否因为 work_mem 不足导致排序或哈希溢出到磁盘？

此外，loops 表示该节点被执行的次数。嵌套循环中内层节点的 actual time 是单次循环的平均时间，总时间需要乘以 loops。忽略 loops 会导致严重误判。

五、解读执行计划的关键顺序

阅读执行计划时，建议从内向外、从下向上看。最内层节点先执行，结果向上传递。重点关注：

• 扫描方式：Seq Scan、Index Scan、Index Only Scan、Bitmap Heap Scan。
• 连接方式：Nested Loop、Hash Join、Merge Join。
• 估算行数与实际行数的偏差。
• 是否有 Sort、Hash、Aggregate 等可能消耗内存的操作。
• 是否有 Rows Removed by Filter 很高的情况，说明扫描了大量无用行。

下面这张图概括了从 SQL 到执行计划、再到估算与真实差异定位的核心关系。

六、实操检查清单

面对一条慢 SQL，可以按以下顺序使用执行计划工具：

1. 先用 EXPLAIN 查看计划形态，确认扫描方式与连接方式是否合理。
2. 再用 EXPLAIN (ANALYZE, BUFFERS) 获取真实行数、时间与缓冲区命中情况。
3. 对比每个节点的估算 rows 与实际 rows，定位偏差最大的节点。
4. 检查该节点涉及的列统计信息是否最新，必要时执行 ANALYZE 或创建扩展统计信息。
5. 观察 Buffers: shared hit/read，判断是否因缓存不足导致大量物理读。
6. 若存在排序或哈希溢出，评估调整 work_mem 或改写 SQL。

执行计划不是一次性的诊断结果，而是持续优化的起点。只有把估算模型、统计信息和真实执行数据放在一起看，才能准确判断 PostgreSQL 为什么这样执行，以及下一步该改什么。