PHP小码农

Appearance

EXPLAIN 输出解读（成本 / 行数 / 时间） ​

拿到一份执行计划，如何逐行读懂它？本节把 EXPLAIN 文本输出的每一个组成部分拆开：缩进与 -> 的结构含义、cost=0.00..35.50 的代价格式、rows=1000 width=36 的行数与行宽、Filter 与 Index Cond 等条件字段的差别、Sort Method 与 Batches 的性能信号，最后用一个完整的真实计划做逐行中文注解。

本节是前两节的「字典」，也是后续「扫描类型」与「连接算法」两节的基础。

前置知识

阅读本节前，建议先了解：EXPLAIN ANALYZE 实际执行

基础概念 ​

中文英文说明
节点类型Node Type该节点的算子名，如 Seq Scan
启动代价startup cost返回第一行之前的代价
总代价total cost返回全部行的代价
估算行数Plan Rows节点假设跑完时输出的行数
行宽Plan Width每行输出的平均字节数（估算）
输出列表Output节点输出的列（VERBOSE 才显示）
过滤条件Filter在节点内逐行判断的条件
索引条件Index Cond用于定位索引扫描范围的条件
重检条件Recheck Cond位图扫描回表后重新校验的条件
哈希条件Hash Cond哈希连接使用的等值条件
连接过滤Join Filter连接完成后额外判断的条件
归并条件Merge Cond归并连接的等值条件
排序键Sort KeySort 节点的排序表达式
分组键Group Key聚合节点的分组表达式
排序方法Sort Methodquicksort / top-N heapsort / external merge
批次数Batches哈希表分几批处理，1 表示不落盘

计划树的空间结构（缩进与箭头）：

text
 Hash Join  (cost=..., rows=...)                      ← 缩进 0，根节点
   Hash Cond: (o.customer_id = c.id)                  ← 节点属性行（无 ->）
   ->  Seq Scan on orders o  (cost=..., rows=...)     ← 缩进 2，子节点（带 ->）
         Filter: (status = 'paid'::text)              ← 该子节点的属性行
   ->  Hash  (cost=..., rows=...)                     ← 缩进 2，另一个子节点
         ->  Seq Scan on customers c  (cost=...)      ← 缩进 6，孙节点

规则：

  • 带 -> 的行是节点首行（summary line），描述节点类型 + 估算/实际数据；
  • 不带 -> 的缩进行是属性行（条件、排序键、统计等），归属其上方的节点；
  • 缩进深度 = 树中层级；相同缩进的多个 -> 是兄弟节点。

语法与用法 ​

决定输出内容多少的选项：

sql
-- 最简：只有节点名与估算
EXPLAIN SELECT * FROM orders;

-- 加实际数据
EXPLAIN ANALYZE SELECT * FROM orders;

-- 加输出列清单、schema 限定名、触发器名、Query Identifier
EXPLAIN (VERBOSE) SELECT * FROM orders;

-- 加缓冲区与 WAL
EXPLAIN (ANALYZE, BUFFERS, WAL) SELECT * FROM orders;

-- 机器可读
EXPLAIN (FORMAT JSON) SELECT * FROM orders;

VERBOSE 带来的输出差异：

sql
EXPLAIN (VERBOSE, COSTS OFF)
SELECT o.order_no, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Beijing';
text
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join
   Output: o.order_no, c.name
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on public.orders o
         Output: o.id, o.customer_id, o.order_no, o.amount, o.status, o.created_at
   ->  Hash
         Output: c.name, c.id
         ->  Seq Scan on public.customers c
               Output: c.id, c.name, c.city, c.created_at
               Filter: (c.city = 'Beijing'::text)

可以看到 public. schema 前缀被加上，每个节点都输出了 Output:。

详细说明 ​

节点类型全景 ​

PostgreSQL 的节点类型可分为四大类：扫描节点、连接节点、处理节点、汇总/分发节点。

扫描节点（scan nodes）

节点含义
Seq Scan顺序扫描整表
Index Scan走索引取 TID，再回表逐行取数据
Index Only Scan所需列全部在索引中，无需回表
Bitmap Index Scan只走索引，构建 TID 位图
Bitmap Heap Scan按位图指出的物理顺序批量回表
Tid Scan按物理行号（ctid）直接访问
Tid Range Scan按 ctid 范围扫描（PostgreSQL 14+）
CTE Scan扫描一个已物化的 CTE 结果
WorkTable Scan扫描递归 CTE 的工作表
Function Scan扫描 FROM 中的集合返回函数
Values Scan扫描 VALUES 列表
Subquery Scan扫描一个子查询结果

连接节点（join nodes）

节点含义
Nested Loop嵌套循环连接
Hash Join哈希连接
Merge Join归并连接

处理节点（processing nodes）

节点含义
Sort显式排序
Incremental Sort增量排序（PostgreSQL 13+）
HashAggregate哈希聚合（GROUP BY 走哈希表）
GroupAggregate排序后分组聚合
Group仅做分组（无聚合函数）
Unique去重（常用于 DISTINCT）
WindowAgg窗口函数计算
Limit限制行数（LIMIT / OFFSET）
Materialize物化子节点结果，供多次读取
Memoize缓存内层扫描结果（PostgreSQL 14+）
Result常量表达式求值 / One-Time Filter 场景
SetOpINTERSECT / EXCEPT 集合运算
LockRows行锁（FOR UPDATE / FOR SHARE）
ModifyTable写操作的统一入口（INSERT/UPDATE/DELETE/MERGE）

汇总与分发节点（gather / append nodes）

节点含义
Gather汇总并行 worker 的结果
Gather Merge汇总并保持有序
Append依次追加多个子计划结果
MergeAppend有序追加（要求子计划有序）
Parallel Append并行追加（PostgreSQL 10+）
Recursive Union递归 CTE 的迭代合并

cost=0.00..35.50 的格式 ​

代价部分格式固定为：

text
cost=启动代价..总代价
text
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..312.30 rows=98 width=44)
                                                     └───┬───┘ └───┬───┘ └───┬──┘ └─┬──┘
                                                    启动     总代价   估算行数  行宽
  • 启动代价（startup cost）：返回第一行之前必须付出的代价。Sort 节点的启动代价很高，因为它必须读完所有输入才能输出第一行；而 Seq Scan 的启动代价几乎是 0。
  • 总代价（total cost）：假设该节点跑到完成、返回所有行的代价。
  • 规划器的选择目标：绝大多数情况下最小化总代价。但在 EXISTS 子查询、LIMIT 等场景下，会倾向于最小化启动代价，因为执行器会提前停止。

代价单位是任意（arbitrary）的抽象刻度，默认以「顺序读一个磁盘页」为 1.0：

ini
seq_page_cost     = 1.0    # 基准
random_page_cost  = 4.0    # 随机读一页 = 4 倍顺序读
cpu_tuple_cost    = 0.01   # 处理一行
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025 # 每个算子/函数调用

举例，tenk1 有 345 个页、10000 行、无 WHERE，那么 Seq Scan 的代价为：

text
345 × seq_page_cost(1.0) + 10000 × cpu_tuple_cost(0.01) = 345 + 100 = 445

这与官方文档示例中的 cost=0.00..445.00 完全对应。

关键提醒：代价不是毫秒，不同机器、不同版本的代价可比性有限。它的唯一用途是在同一个实例内比较不同方案的优劣。

rows=N width=W 的含义 ​

text
 rows=1000 width=36
  • rows=1000：规划器估计该节点输出 1000 行。注意是输出而非扫描。一个 Seq Scan 可能扫描 10000 行，但通过 Filter 后只输出 1000 行，则 rows=1000。
  • width=36：估计每行输出的平均字节数。它影响内存分配估算（排序是否落盘、哈希表是否分批），也是判断「是否传输了过多列」的依据。

width 的一个重要用途是识别 SELECT * 的代价：

sql
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = 888;
text
                    QUERY PLAN
--------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   Index Cond: (customer_id = 888)

对比 VERBOSE 下的 width：

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 888;
EXPLAIN SELECT id FROM orders WHERE customer_id = 888;
text
-- SELECT * 的 width 更大
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..312.30 rows=98 width=44)

-- 只查 id，width 更小，还可能走 Index Only Scan
 Index Only Scan using idx_orders_customer_id on orders  (cost=0.42..12.30 rows=98 width=8)

条件字段：Filter / Index Cond / Recheck Cond / Hash Cond / Join Filter / Merge Cond ​

这六个字段经常被混淆，它们的执行位置和性能含义完全不同。

字段出现位置含义性能影响
Index CondIndex Scan / Bitmap Index Scan下推到索引层的条件，直接缩小索引范围最好，参与索引定位
Recheck CondBitmap Heap Scan位图可能含「假阳性」行，回表后重新校验位图扫描的必要成本
Filter各类扫描节点取出行后逐行判断，丢弃不合格行越少越好，多说明条件无法下推
Hash CondHash Join哈希连接的等值条件由哈希表结构完成，效率高
Merge CondMerge Join归并连接的等值条件由归并过程完成
Join FilterNested Loop / Merge Join连接完成后再判断的条件越少越好，多说明连接后还要大量丢弃

来看一组对比，同一个查询在不同筛选强度下的条件归属：

sql
-- 情况 1：条件可以走索引 → Index Cond
EXPLAIN SELECT * FROM orders WHERE customer_id = 888;
text
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..312.30 rows=98 width=44)
   Index Cond: (customer_id = 888)
sql
-- 情况 2：索引只覆盖部分条件，另一部分只能做 Filter
EXPLAIN SELECT * FROM orders WHERE customer_id = 888 AND status = 'paid';
text
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..312.55 rows=25 width=44)
   Index Cond: (customer_id = 888)
   Filter: (status = 'paid'::text)
sql
-- 情况 3：走位图扫描，出现 Recheck Cond
EXPLAIN SELECT * FROM orders WHERE customer_id < 100;
text
 Bitmap Heap Scan on orders  (cost=25.07..612.40 rows=2900 width=44)
   Recheck Cond: (customer_id < 100)
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..24.35 rows=2900 width=0)
         Index Cond: (customer_id < 100)

Filter 与 Index Cond 的关键区别：Index Cond 在索引层生效，扫描的行数就已经被限制；Filter 在取出行之后生效，被丢弃的行已经付出了 I/O 代价。所以把条件变成 Index Cond 永远是优化方向。

Bitmap Heap Scan 为什么要 Recheck Cond？因为位图是有损（lossy）的：当索引条目太多，无法为每个 TID 都保留在精确位图中时，位图会退化成「按数据页标记」，导致同一页上不符合条件的行也被一并取出，所以必须回表后再校验一次。

Sort Key / Group Key / Output ​

text
 Sort  (cost=1834.72..1835.22 rows=100 width=44)
   Sort Key: created_at DESC

Sort Key 是排序表达式，可以有多个键，逗号分隔：

text
 Sort Key: o.amount DESC, o.created_at ASC NULLS LAST
text
 HashAggregate  (cost=1850.32..1852.11 rows=5 width=36)
   Group Key: c.city

Group Key 是分组表达式。如果 GROUP BY 有多个列，会全部列出。

Output 只在 VERBOSE 下显示，列出节点输出的列：

text
 Result
   Output: (count(*)), (sum(o.amount))

Sort Method：内存还是磁盘 ​

Sort 节点的关键属性行：

text
 Sort Method: quicksort  Memory: 2512kB
Sort Method含义性能
quicksort全部数据在内存中快速排序好
top-N heapsort配合 LIMIT，只维护最大的 N 个好，内存占用小
external merge内存不足，分成多段落盘后归并差，出现 Disk: N kB

external merge 的完整输出形式：

text
 Sort Method: external merge  Disk: 12576kB

有时会带附加信息：

text
 Sort Method: external sort  Disk: 8192kB

Incremental Sort（PostgreSQL 13+）会额外显示已排序的前缀键：

text
 Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
   Sort Key: hundred, ten
   Presorted Key: hundred
   Full-sort Groups: 100  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB

Presorted Key: hundred 说明输入已经按 hundred 有序，只需在每个 hundred 分组内按 ten 排序，代价远低于全量排序。

Hash 属性：Batches / Buckets / Memory Usage ​

哈希相关节点（Hash、HashAggregate、Hash Join 的构建侧）会输出版本信息：

text
 Hash  (cost=518.75..518.75 rows=100 width=32) (actual time=24.310..24.310 rows=2000 loops=1)
   Buckets: 2048  Batches: 1  Memory Usage: 4097kB
字段含义判读
Buckets哈希桶数量与行数匹配即可，一般不必干预
Batches分批数= 1 表示全部在内存；> 1 表示分批落盘，性能显著变差
Memory Usage哈希表占用的内存与 work_mem × hash_mem_multiplier 对照

Batches: 8 表示哈希表被分成了 8 批，写出 7 批到临时文件，然后逐批探测。这是 Hash Join 最典型的性能退化信号，修复方式是提高 work_mem。

HashAggregate 同理：

text
 HashAggregate  (cost=1832.00..1852.00 rows=200 width=36) (actual time=1850.32..1852.11 rows=5 loops=1)
   Group Key: c.city
   Batches: 1  Memory Usage: 105kB
   Planned Partitions: 4

Planned Partitions: 4 出现在估算分组数很多的情况下，表示规划器预先计划了分区聚合。

Buckets / Rows Removed by Filter / Heap Fetches ​

Rows Removed by Filter 出现在有 Filter 的扫描节点：

text
 Seq Scan on orders  (actual time=0.018..92.140 rows=203 loops=1)
   Filter: (order_no ~~ '%8888'::text)
   Rows Removed by Filter: 199797

Rows Removed by Join Filter 出现在连接节点：

text
 Nested Loop  (actual time=0.062..1520.400 rows=12 loops=1)
   Join Filter: ((o.amount)::numeric > ((c.id * 100))::numeric)
   Rows Removed by Join Filter: 399988

Heap Fetches 出现在 Index Only Scan：

text
 Index Only Scan using idx_orders_customer_created on orders  (actual time=0.031..12.480 rows=10012 loops=1)
   Index Cond: ((customer_id >= 100) AND (customer_id <= 200))
   Heap Fetches: 10012

还有一个不常见但有用的字段 Rows Removed by Index Recheck，出现在位图扫描因有损位图而重新校验时：

text
 Bitmap Heap Scan on orders  (actual time=0.180..42.400 rows=2900 loops=1)
   Recheck Cond: (customer_id < 100)
   Rows Removed by Index Recheck: 812
   Heap Blocks: exact=300 lossy=120

lossy=120 表示有 120 个数据页的位图是「有损」的（只记录了页号，没记录具体行），因此需要重新校验，并产生了 812 行的额外丢弃。

估算偏差诊断清单 ​

把上面这些字段组合起来，就形成了一套可执行的诊断流程：

现象可能原因处理方式
rows < actual rows 10 倍以上统计信息不足 / 列相关性ANALYZE、提高 SET STATISTICS、建扩展统计
rows > actual rows 10 倍以上统计信息过时 / 选择率估算过乐观ANALYZE，检查是否有稀疏值
Rows Removed by Filter 远大于 actual rows条件无法走索引建索引、改写查询使条件下推
Rows Removed by Join Filter 很大连接条件太弱重构查询、增加连接条件的选择性
Sort Method: external mergework_mem 不足提高 work_mem 或减少排序数据量
Batches > 1哈希表超出内存提高 work_mem / hash_mem_multiplier
Heap Fetches ≈ actual rows可见性映射未更新VACUUM 该表
loops 很大且单次耗时不可忽略嵌套循环内层昂贵换索引、改连接方式
shared read 很大缓存未命中增 shared_buffers、优化索引
Planning Time > Execution Time规划复杂度过高简化查询、调 join_collapse_limit

实战示例 ​

场景一：完整的真实计划逐行注解 ​

下面是一个电商后台「按城市统计已支付订单金额」的查询，我们对其计划逐行注解。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city, count(*) AS order_cnt, sum(o.amount) AS total_amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
GROUP BY c.city
ORDER BY total_amount DESC
LIMIT 10;
text
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2456.20..2456.22 rows=10 width=44) (actual time=612.480..612.485 rows=5 loops=1)
   Buffers: shared hit=1268 read=12
   ->  Sort  (cost=2456.20..2456.32 rows=50 width=44) (actual time=612.480..612.483 rows=5 loops=1)
         Sort Key: (sum(o.amount)) DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1268 read=12
         ->  HashAggregate  (cost=2454.20..2455.20 rows=50 width=44) (actual time=612.400..612.460 rows=5 loops=1)
               Group Key: c.city
               Batches: 1  Memory Usage: 24kB
               Buffers: shared hit=1268 read=12
               ->  Hash Join  (cost=546.00..2354.20 rows=20000 width=40) (actual time=32.150..548.300 rows=24810 loops=1)
                     Hash Cond: (o.customer_id = c.id)
                     Buffers: shared hit=1268 read=12
                     ->  Bitmap Heap Scan on orders o  (cost=22.40..1420.00 rows=20000 width=28) (actual time=14.200..342.800 rows=24810 loops=1)
                           Recheck Cond: (created_at >= (now() - '30 days'::interval))
                           Filter: (status = 'paid'::text)
                           Rows Removed by Filter: 12450
                           Heap Blocks: exact=1150
                           Buffers: shared hit=1240 read=12
                           ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..17.40 rows=37215 width=0) (actual time=12.400..12.401 rows=37260 loops=1)
                                 Index Cond: (created_at >= (now() - '30 days'::interval))
                                 Buffers: shared hit=28
                     ->  Hash  (cost=518.75..518.75 rows=2000 width=36) (actual time=17.900..17.901 rows=2000 loops=1)
                           Buckets: 2048  Batches: 1  Memory Usage: 145kB
                           Buffers: shared hit=16
                           ->  Seq Scan on customers c  (cost=0.00..518.75 rows=2000 width=36) (actual time=0.012..9.400 rows=2000 loops=1)
                                 Buffers: shared hit=16
 Planning Time: 0.395 ms
 Execution Time: 613.020 ms
(28 rows)

逐行注解：

text
 Limit  (cost=2456.20..2456.22 rows=10 width=44) (actual time=612.480..612.485 rows=5 loops=1)
 ↑        ↑        ↑         ↑         ↑        ↑        ↑         ↑        ↑           ↑
 节点名   启动     总代价    估算     行宽     实际启动  实际总  每循环    循环        真实输出
         代价               行数              耗时      耗时    行数      次数        5 行（只有 5 个城市）
  • cost 的启动与总代价几乎相等（2456.20 vs 2456.22），这是 Limit 的典型特征：它只需从子节点取前 10 行就可以停。
  • rows=10 是 LIMIT 10 的要求；actual rows=5 说明实际只有 5 个城市，比预期少。
  • actual time 的启动值与总值接近，因为只取了很少的行。
text
   ->  Sort  (cost=2456.20..2456.32 rows=50 width=44) (actual time=612.480..612.483 rows=5 loops=1)
         Sort Key: (sum(o.amount)) DESC
         Sort Method: quicksort  Memory: 25kB
  • Sort Key: (sum(o.amount)) DESC 排序的是聚合后的结果，不是原始行，所以只有 5 行，Memory: 25kB 很小。
  • Sort Method: quicksort 说明没落盘。
  • rows=50 估算有 50 个城市分组，实际 5 个——估算偏高 10 倍，但因为数据量小，无实际影响。
text
         ->  HashAggregate  (cost=2454.20..2455.20 rows=50 width=44) (actual time=612.400..612.460 rows=5 loops=1)
               Group Key: c.city
               Batches: 1  Memory Usage: 24kB
  • Group Key: c.city：按城市分组。
  • Batches: 1：分组数少，完全在内存中完成，未落盘。
  • Memory Usage: 24kB 与 work_mem（默认 4MB）相比极小，无需调整。
text
               ->  Hash Join  (cost=546.00..2354.20 rows=20000 width=40) (actual time=32.150..548.300 rows=24810 loops=1)
                     Hash Cond: (o.customer_id = c.id)
  • Hash Cond 是连接条件，用的是等值连接，所以能走哈希。
  • 估算 rows=20000，实际 rows=24810，偏差仅 1.24 倍，估算准确。
  • actual time 的启动 32.15ms 包含构建哈希表的时间（Hash 节点耗时 17.9ms）。
text
                     ->  Bitmap Heap Scan on orders o  (cost=22.40..1420.00 rows=20000 width=28) (actual time=14.200..342.800 rows=24810 loops=1)
                           Recheck Cond: (created_at >= (now() - '30 days'::interval))
                           Filter: (status = 'paid'::text)
                           Rows Removed by Filter: 12450
                           Heap Blocks: exact=1150
  • Recheck Cond 说明这是位图扫描，回表后需要重新校验时间条件。
  • Filter: (status = 'paid') 说明 status 条件无法走索引（索引建在 created_at 上），只能在取出行之后过滤。
  • Rows Removed by Filter: 12450 表示 37260 行中有 12450 行因 status != 'paid' 被丢弃，过滤效率约 66%，还算可接受。
  • Heap Blocks: exact=1150 表示访问了 1150 个数据页，且位图是精确的（非 lossy）。
text
                           ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..17.40 rows=37215 width=0) (actual time=12.400..12.401 rows=37260 loops=1)
                                 Index Cond: (created_at >= (now() - '30 days'::interval))
  • 只走索引构建 TID 位图，width=0 是这一节点的固定特征（不输出实际列）。
  • 估算 rows=37215，实际 rows=37260，估算极准。
text
                     ->  Hash  (cost=518.75..518.75 rows=2000 width=36) (actual time=17.900..17.901 rows=2000 loops=1)
                           Buckets: 2048  Batches: 1  Memory Usage: 145kB
                           ->  Seq Scan on customers c  (cost=0.00..518.75 rows=2000 width=36) (actual time=0.012..9.400 rows=2000 loops=1)
  • Hash 的启动代价 = 总代价（518.75..518.75），因为它不做过滤、只把输入全部读进哈希表。
  • Batches: 1 表示 2000 行完全装进内存，Memory Usage: 145kB 很小。
  • 小表 customers 只有 2000 行，走 Seq Scan 合理。
text
 Planning Time: 0.395 ms
 Execution Time: 613.020 ms
  • 规划几乎不耗时，执行 613ms 是本次查询的主要成本。

结论：这个查询的执行时间主要花在 Bitmap Heap Scan（342.8ms 中去除子节点约 330ms）。优化方向是建一个覆盖 (created_at, status) 或 (status, created_at) 的复合索引，把 status 条件也变成 Index Cond，消除 Rows Removed by Filter: 12450。

sql
-- 优化：让时间和状态都能走索引
CREATE INDEX CONCURRENTLY idx_orders_status_created
    ON orders (status, created_at);
ANALYZE orders;

场景二：识别估算偏差并修正 ​

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE order_no = 'ORD-000123456';
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on orders  (actual time=0.018..88.240 rows=1 loops=1)
   Filter: (order_no = 'ORD-000123456'::text)
   Rows Removed by Filter: 199999
   Buffers: shared hit=8 read=1216
 Planning Time: 0.150 ms
 Execution Time: 88.420 ms

计划里 看不到 rows= 估算值（因为 COSTS OFF 关掉了）。要看估算偏差，必须保留 COSTS：

sql
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE order_no = 'ORD-000123456';
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2032.00 rows=1 width=44) (actual time=0.018..88.240 rows=1 loops=1)
   Filter: (order_no = 'ORD-000123456'::text)
   Rows Removed by Filter: 199999
 Planning Time: 0.150 ms
 Execution Time: 88.420 ms

估算 rows=1，实际 rows=1，估算非常准。那为什么还是 88ms？因为 没有 order_no 索引，规划器只能用 Seq Scan，扫了 20 万行。

这个案例说明：估算准 ≠ 计划好。没有索引时，规划器再怎么正确地估算也无计可施。修复方式是：

sql
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_order_no ON orders (order_no);
ANALYZE orders;

重建后：

text
 Index Scan using idx_orders_order_no on orders  (cost=0.42..8.44 rows=1 width=44) (actual time=0.031..0.032 rows=1 loops=1)
   Index Cond: (order_no = 'ORD-000123456'::text)
 Planning Time: 0.180 ms
 Execution Time: 0.055 ms

耗时从 88ms 降到 0.055ms，且 Filter 变成了 Index Cond。

注意事项 ​

  • 不要只看节点名，要读属性行。 两个都是 Seq Scan 的计划，一个带 Filter 一个不带，性能含义完全不同。

    sql
    -- 前者输出 20 万行（无 Filter），后者输出 3 行（带 Filter，丢 199997 行）
-- 只看节点名会误判为「一样」

  • Bitmap Index Scan 的 width=0 是正常的，不是错误。 它只输出 TID，不输出业务列。

    text
    Bitmap Index Scan on idx_orders_created_at  (cost=0.00..17.40 rows=37215 width=0)

  • Recheck Cond 不是多余的重复工作，而是位图扫描的正确性要求。 有损位图必须重检。

  • Batches > 1 一定要当回事。 它意味着磁盘临时文件，性能可能下降数倍。

    text
    Buckets: 2048  Batches: 8  Memory Usage: 4096kB   -- 不推荐，应提高 work_mem

  • external merge 是最差的排序方式。 宁可加大 work_mem，也不要让排序落盘。

    text
    Sort Method: external merge  Disk: 12576kB   -- 不推荐

  • 不要忽略 Heap Fetches。 它大于 0 且接近 actual rows 时，Index Only Scan 名不副实。

    text
    Index Only Scan ... Heap Fetches: 10012   -- 需要 VACUUM

  • Filter 与 Index Cond 的差别是优化的核心杠杆。 看到 Filter 就要思考「能否变成 Index Cond」。

    sql
    -- 改前：status 只能做 Filter
-- WHERE created_at >= X AND status = 'paid'
-- 改后：建 (status, created_at) 复合索引，两个条件都进 Index Cond

  • Sort Key 显示的是表达式，注意括号。 聚合结果排序会显示为 (sum(o.amount)) DESC，不要误读为列名。

  • width 变大意味着数据传输与内存开销上升。 避免 SELECT *。

    text
    -- 不推荐：width=244
Seq Scan on orders  (cost=0.00..445.00 rows=10000 width=244)

  • Gather / Gather Merge 下面的子树才是真正的并行部分。 它们之上的节点在 leader 进程中串行执行。

    text
    Finalize Aggregate            ← leader 进程
  ->  Gather                   ← 并行边界
        ->  Partial Aggregate  ← worker 进程

最佳实践 ​

  • 建立「先看结构、再看条件、再看数值」的三步阅读法。 结构决定执行顺序，条件决定过滤效率，数值决定瓶颈位置。

    text
    第 1 步：看缩进与 -> 理清节点树
第 2 步：看 Filter / Index Cond / Join Filter 判断条件是否下推
第 3 步：看 rows vs actual rows、actual time、Buffers 定位瓶颈

  • 遇到 Seq Scan 不要条件反射地认为要加索引。 大范围查询、小表、宽表返回大量列时，Seq Scan 反而是最优选择。

    sql
    -- 合理：取回全表 40% 的行，Seq Scan 比索引回表更快
EXPLAIN SELECT * FROM orders WHERE status <> 'void';

  • 把 Filter 改造成 Index Cond 作为首要优化目标。 建复合索引让多个条件一起走索引。

    sql
    CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders (status, created_at);

  • Sort Method 与 Batches 出现磁盘信号时，先评估是否真的需要全量排序或大哈希表。 加 LIMIT、缩小列宽、提前过滤都能减少内存需求。

    sql
    -- 先过滤再排序，减少排序输入量
SELECT id, order_no FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

  • Heap Fetches 高时先 VACUUM 再下结论。 可见性映射的更新是 VACUUM 的职责，不能靠改索引解决。

    sql
    VACUUM (ANALYZE, VERBOSE) orders;

  • 用 EXPLAIN (VERBOSE, COSTS OFF) 查看 Output 列表，检查是否有多余列。 这能直观看出 SELECT * 的成本。

    sql
    EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders WHERE customer_id = 1;

  • 把 Format JSON 的输出保存为基线，用于计划回归测试。 发版后对比 Plan Rows 与 Total Cost 变化。

    sql
    EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) SELECT ...;

  • 读懂 cost 的相对大小而非绝对大小。 同一个查询的两个候选计划的 cost 才具备可比性。

    text
    计划 A 的 total cost=1200，计划 B 的 total cost=800 → 选 B（同一实例内比较）

  • Limit 节点的 cost 有两个数值且很接近时，代表它提前停止。 不要据此认为子节点的总代价就是查询代价。

    text
    Limit  (cost=0.29..14.28 rows=2 width=244)

  • Bitmap Heap Scan 的 Heap Blocks: exact=N lossy=M 中，lossy > 0 需要关注。 有损位图意味着额外的重检成本，可通过提高 work_mem 缓解。

    text
    Heap Blocks: exact=300 lossy=120   -- 存在有损页，重检成本上升

  • Subquery Scan 通常是可消除的中间层。 若它只是简单转发，考虑把子查询展开（PG 通常会自动展开，未展开说明有 LIMIT、OFFSET 或其他阻断因素）。

    sql
    -- 若出现 Subquery Scan，检查子查询是否含 LIMIT / OFFSET / volatile 函数

  • 节点属性行的顺序是固定的，可以把习惯固化下来。 一般顺序为：Output → 条件（Index Cond / Filter 等）→ Rows Removed by ... → 算法属性（Sort Method / Batches）→ Buffers。

下一节

继续学习：Seq Scan vs Index Scan

参考链接 ​