PHP小码农

Appearance

EXPLAIN ANALYZE 实际执行 ​

EXPLAIN 只告诉你「规划器打算怎么跑」，EXPLAIN ANALYZE 则告诉你「实际跑得怎么样」。它真正执行语句，把每个节点的真实耗时、真实输出行数、真实循环次数附加到计划树上。更重要的是，它暴露了估算与实际的差距——这正是绝大多数性能问题的根源。

本节聚焦 EXPLAIN ANALYZE 输出的每一个关键字段，尤其是被误读最多的 actual time 与 loops 的乘除关系。

前置知识

阅读本节前，建议先了解：EXPLAIN 基础用法

基础概念 ​

中文英文说明
实际执行ANALYZE真正运行语句并收集运行时统计
实际启动耗时actual startup time返回第一行所需毫秒数（每循环平均值）
实际总耗时actual total time返回全部行所需毫秒数（每循环平均值）
实际行数actual rows每循环平均输出的行数
循环次数loops该节点被执行的次数
规划耗时Planning Time生成执行计划花费的毫秒数
执行耗时Execution Time执行阶段总毫秒数（不含规划）
缓冲区Buffersshared/local/temp 块的命中与读写统计
写前日志统计WAL记录数、全页镜像数、字节数、缓冲区满次数
内存使用Memory节点运行时占用的内存（部分节点）+ 规划器内存
过滤移除行数Rows Removed by Filter被 Filter 条件丢弃的行数
连接过滤移除行数Rows Removed by Join Filter被 Join Filter 丢弃的行数
堆页抓取Heap FetchesIndex Only Scan 因可见性映射未命中而回表次数
计时开关TIMING是否统计每节点耗时，默认 on

EXPLAIN ANALYZE 的输出结构与 EXPLAIN 完全一致，只是在每个节点的括号里追加了一组实际数据：

text
                    ┌────────── 估算部分 ──────────┐ ┌────────── 实际部分 ──────────┐
 Index Scan using idx_orders_user_id on orders  (cost=0.29..24.83 rows=12 width=28) (actual time=0.031..0.052 rows=12 loops=1)
                                                  └ 启动代价..总代价 ┘ └ 估算行数 ┘ └ 行宽 ┘         └ 启动..总耗时 ┘ └ 行数 ┘ └ 循环 ┘

两个 () 内的对应关系是：cost ↔ actual time、rows ↔ actual rows、width 无对应实际值（行宽无法实测）。

语法与用法 ​

EXPLAIN ANALYZE 与 EXPLAIN 共用同一套选项语法，ANALYZE 只是其中一个选项：

sql
EXPLAIN ANALYZE statement;

-- 等价于
EXPLAIN (ANALYZE) statement;

-- 完整写法
EXPLAIN (ANALYZE, BUFFERS, TIMING, SUMMARY, VERBOSE, COSTS, WAL, MEMORY, FORMAT TEXT)
statement;

在 PostgreSQL 18 中，ANALYZE 会自动包含 BUFFERS 与 SUMMARY（输出 Planning Time 与 Execution Time）：

sql
-- 18 中这两条输出基本等价（数据略有差异）
EXPLAIN ANALYZE SELECT count(*) FROM orders;
EXPLAIN (ANALYZE, BUFFERS, SUMMARY) SELECT count(*) FROM orders;

只关心行数、不关心耗时的时候，关闭 TIMING 可以显著降低插桩开销：

sql
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

WAL 统计只在 ANALYZE 下可用：

sql
EXPLAIN (ANALYZE, WAL) UPDATE orders SET status = 'closed' WHERE id = 1;

MEMORY 选项（PostgreSQL 17 起）输出规划器内存消耗：

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

详细说明 ​

actual time 与 loops：最常见的误读点 ​

这是 EXPLAIN ANALYZE 中必须掌握的一点，弄错会导致优化方向完全错误。

规则：actual time 和 actual rows 都是「每次循环的平均值」，不是总计。

  • 节点单次执行的真实总耗时 = actual total time × loops
  • 节点输出的真实总行数 = actual rows × loops

看一个典型的嵌套循环计划：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.name, o.order_no
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Hangzhou';
text
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Nested Loop  (actual time=0.055..18.420 rows=9700 loops=1)
   Buffers: shared hit=2154
   ->  Seq Scan on customers c  (actual time=0.018..0.410 rows=400 loops=1)
         Filter: (city = 'Hangzhou'::text)
         Rows Removed by Filter: 1600
         Buffers: shared hit=13
   ->  Index Scan using idx_orders_customer_id on orders o  (actual time=0.021..0.042 rows=24 loops=400)
         Index Cond: (customer_id = c.id)
         Buffers: shared hit=2141
 Planning Time: 0.312 ms
 Execution Time: 18.665 ms
(11 rows)

关键解读：

观察数值结论
Nested Loop 的 rows=9700 loops=1真实输出 9700 行400 个客户 × 平均 24 单 ≈ 9700，吻合
Index Scan 的 loops=400执行了 400 次外层 Seq Scan 每返回一行，内层就完整跑一次
Index Scan 的 actual time=0.021..0.042这是单次查找的耗时真实总耗时 ≈ 0.042 × 400 ≈ 16.8 ms
Index Scan 的 rows=24这是单次查到的行数真实总行数 = 24 × 400 = 9600

错误读法：「内层索引扫描只花了 0.042ms，很快」——实际它贡献了 16.8ms，占了总耗时的绝大部分。

Planning Time 与 Execution Time ​

Summary 信息在 ANALYZE 下默认输出（PostgreSQL 18）：

text
 Planning Time: 0.312 ms
 Execution Time: 18.665 ms
  • Planning Time：从收到查询到生成执行计划的时间，包括解析、重写、规划。对复杂查询（多表连接、大量子查询）可能很大。
  • Execution Time：执行器运行计划的总时间，不包含规划时间，也不包含把结果序列化输出给客户端的时间。

如果发现 Planning Time 远大于 Execution Time，说明问题在「规划太慢」而不是「执行太慢」，通常需要简化查询或调整 join_collapse_limit、from_collapse_limit。

配合 SERIALIZE 选项可以测出序列化输出的开销：

sql
EXPLAIN (ANALYZE, SERIALIZE TEXT, COSTS OFF)
SELECT * FROM orders;
text
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders  (actual time=0.012..42.310 rows=200000 loops=1)
 Planning Time: 0.145 ms
 Serialization: 18.420 ms
 Execution Time: 42.560 ms

这里序列化花了 18ms，占执行时间的 40%，说明瓶颈可能在「传数据」而不是「查数据」。

Buffers：shared hit/read/dirtied/written 与 temp ​

BUFFERS 输出的是块（block，通常 8kB）的计数：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders;
text
                          QUERY PLAN
---------------------------------------------------------------
 Aggregate  (actual time=38.215..38.216 rows=1 loops=1)
   Buffers: shared hit=1180 read=64
   ->  Seq Scan on orders  (actual time=0.014..29.140 rows=200000 loops=1)
         Buffers: shared hit=1180 read=64
 Planning Time: 0.128 ms
 Execution Time: 38.410 ms
指标含义关注点
shared hit命中共享缓冲区的块数高 hit 说明数据在缓存，快
shared read从磁盘读入的块数高 read 说明需要 I/O，慢
shared dirtied本次被首次修改的块数写操作专有
shared written本次被驱逐出缓存并写盘的块数写操作专有，受检查点影响
local hit/read/dirtied/written临时表的块统计临时表查询中才有
temp read/written临时文件读写块数排序/哈希/物化溢出到磁盘的信号

temp read/written 是最需要注意的指标之一：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders ORDER BY amount, created_at;
text
                             QUERY PLAN
-----------------------------------------------------------------------
 Sort  (actual time=245.180..318.420 rows=200000 loops=1)
   Sort Key: amount, created_at
   Sort Method: external merge  Disk: 12576kB
   Buffers: shared hit=1180, temp read=1572 written=1572
   ->  Seq Scan on orders  (actual time=0.013..38.510 rows=200000 loops=1)
         Buffers: shared hit=1180
 Planning Time: 0.180 ms
 Execution Time: 319.020 ms

看到 external merge 和 temp written=1572 就说明排序无法在 work_mem 内完成，落到了磁盘，慢是必然的。修复方向是提高 work_mem。

在文本格式中，只有非零值会被打印。所以 Buffers: shared hit=1180 意味着 read=0，全部命中缓存。

WAL：写操作的代价 ​

WAL 选项（配合 ANALYZE）显示写语句产生的 WAL 量：

sql
BEGIN;
EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
INSERT INTO orders (customer_id, order_no, amount)
SELECT 1 + (random() * 1999)::int, 'BULK-' || g, 99.99
FROM generate_series(1, 10000) AS g;
ROLLBACK;
text
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Insert on orders  (actual rows=10000 loops=1)
   WAL: records=10002 fpi=6 bytes=1843200 buffers full=0
   ->  Function Scan on generate_series g  (actual rows=10000 loops=1)
 Planning Time: 0.260 ms
 Execution Time: 48.120 ms
字段含义
records产生的 WAL 记录条数
fpi全页镜像（full page image）数量，检查点后首次修改页面会写
bytes生成的 WAL 总字节数
buffers fullWAL 缓冲区满的次数（说明 WAL 写入压力大）

bytes 可以用来评估复制延迟与写入放大：写入 1MB 数据却产生 10MB WAL，说明存在大量索引更新或全页写入。

Memory：规划与运行的内存开销 ​

MEMORY 选项显示规划阶段的内存消耗：

sql
EXPLAIN (MEMORY, COSTS OFF)
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Beijing' AND o.amount > 100;
text
                          QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o
   ->  Hash
         ->  Seq Scan on customers c
 Planning:
   Memory: used=129kB  allocated=256kB
(9 rows)

used 是规划器内部结构实际使用的内存，allocated 是包含分配开销的总量。复杂查询（数十个连接的报表 SQL）规划内存可能达到数十 MB，这本身也是需要关注的成本。

部分执行节点也会输出自己的内存使用，例如：

text
 Sort Method: quicksort  Memory: 2512kB
 HashAggregate  ...
   Batches: 1  Memory Usage: 8193kB

用 actual rows 与估算 rows 判断偏差 ​

这是 EXPLAIN ANALYZE 最核心的价值。经验判断标准：

实际/估算比值严重程度常见原因
0.5 ~ 2 倍正常统计抽样误差，无需处理
2 ~ 10 倍需要留意统计信息略陈旧，或数据分布不均
> 10 倍必须处理统计信息缺失/过时，或列间相关性
> 100 倍严重规划器选错算法，性能可能差一个数量级
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND amount > 5000;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..1832.00 rows=2 width=44) (actual time=0.025..58.240 rows=1254 loops=1)
   Filter: ((status = 'pending'::text) AND (amount > 5000::numeric))
   Rows Removed by Filter: 198746
   Buffers: shared hit=8 read=1216
 Planning Time: 0.210 ms
 Execution Time: 58.480 ms

估算 rows=2，实际 rows=1254，偏差 627 倍。规划器以为只返回 2 行，于是选择了零成本的 Seq Scan；如果它知道实际有 1254 行，可能会考虑走索引。

修复方法：

sql
-- 方法一：收集统计信息
ANALYZE orders;

-- 方法二：提高统计目标（默认 100，可提高到 500~1000）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- 方法三：如果两列相关（status=pending 时 amount 通常较小），建扩展统计信息
CREATE STATISTICS orders_status_amount (dependencies, ndistinct)
    ON status, amount FROM orders;
ANALYZE orders;

Rows Removed by Filter：过滤效率的晴雨表 ​

Rows Removed by Filter 出现在扫描节点上，表示被 Filter 条件丢弃的行数。它和 actual rows 的比值反映了过滤效率：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE order_no LIKE '%8888';
text
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders  (actual time=0.018..92.140 rows=203 loops=1)
   Filter: (order_no ~~ '%8888'::text)
   Rows Removed by Filter: 199797
 Planning Time: 0.190 ms
 Execution Time: 92.350 ms

读了 20 万行，只留下 203 行，Rows Removed by Filter: 199797。过滤效率 = 203 / 200000 ≈ 0.1%。这说明扫描做了大量无用功。

对比一下高效过滤：

text
 Index Scan using idx_orders_order_no on orders  (actual time=0.030..0.480 rows=203 loops=1)
   Index Cond: ((order_no >= '8888'::text) AND (order_no < '8889'::text))

同样的结果，耗时从 92ms 降到 0.5ms。

Rows Removed by Join Filter ​

Nested Loop 节点上可能出现这个字段，表示连接后又被连接条件之外的条件丢弃的行：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.amount > c.id * 100;
text
                                QUERY PLAN
--------------------------------------------------------------------------
 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
   ->  Seq Scan on customers c  (actual time=0.012..0.460 rows=2000 loops=1)
   ->  Seq Scan on orders o  (actual time=0.009..0.320 rows=200 loops=1)
 Planning Time: 0.240 ms
 Execution Time: 1520.820 ms

注意这里内层 Seq Scan on orders 的 rows=200 是每循环输出，loops 没有显示（为简洁省略，实际应看到 loops=2000），总的处理量是 200 × 2000 = 40 万行，其中 399988 行被 Join Filter 丢弃。

Rows Removed by Join Filter 很大时，通常意味着：

  • 连接条件太弱，产生大量笛卡尔积候选；
  • 存在无法下推到扫描层的连接后条件，需要重新设计查询（例如把条件提前到子查询里）。

Heap Fetches：Index Only Scan 的隐形成本 ​

Index Only Scan 理论上不回表，但它依赖可见性映射（visibility map）确认「这个索引项指向的行对所有事务都可见」。如果对应数据页在可见性映射里没有被标记为「全部可见」，就必须回表确认：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id FROM orders WHERE customer_id BETWEEN 100 AND 200;
text
                             QUERY PLAN
-----------------------------------------------------------------------
 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
   Buffers: shared hit=1240 read=96
 Planning Time: 0.300 ms
 Execution Time: 12.760 ms

Heap Fetches: 10012 意味着 10012 行全部回表了，Index Only Scan 退化成了普通 Index Scan。原因是表刚被大量更新，可见性映射还没被 VACUUM 更新：

sql
VACUUM orders;
-- 再跑一次，Heap Fetches 应降到 0 或很低

这是「索引理论上该很快却没那么快」的经典原因。

「估算准但实际慢」的排查 ​

有时候 rows 与 actual rows 高度吻合，但查询依然慢。这时要看 BUFFERS 与其他因素：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 888;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..312.30 rows=98 width=44)
       (actual time=0.048..128.400 rows=98 loops=1)
   Index Cond: (customer_id = 888)
   Buffers: shared hit=2 read=1043
 Planning Time: 0.290 ms
 Execution Time: 128.620 ms

估算 98 行，实际 98 行，估算完全准确。但耗时 128ms，且 read=1043 表示读了 1043 个数据页。原因是这些行散落在 1043 个不同的数据页上（数据写入时顺序随机），随机 I/O 代价极高。

修复方向不是改统计信息，而是：

  • 用聚簇（CLUSTER）或 pg_repack 物理重排数据，让同一 customer_id 的行聚集；
  • 或者接受现状，加涂层缓存（effective_cache_size 调高影响估算但不改物理布局）。

其他「计划里看不到但影响实际耗时」的因素：

因素说明排查方式
锁等待等待其他事务释放行锁/表锁pg_locks、pg_stat_activity.wait_event
触发器开销AFTER 触发器的 SQL 不计入计划检查是否有触发器
序列化开销结果转文本发给客户端的时间EXPLAIN (ANALYZE, SERIALIZE TEXT)
函数内部查询PL/pgSQL 内部的 SQL 是独立计划auto_explain.log_nested_statements
并行 worker 启动开销每个 gather 都有进程 fork 成本小查询上避免并行
JIT 编译高 cost 查询触发 JIT，可能有编译开销jit_above_cost

在只读副本上做 EXPLAIN ANALYZE ​

把 EXPLAIN ANALYZE 放到只读副本（replica）上跑，是生产环境的最佳实践：

  • 不干扰主库负载；
  • 副本上通常是相同的数据与统计信息；
  • 只读副本上执行写操作的 EXPLAIN ANALYZE 会被拒绝，这反而是种保护。

不过要注意两点差异：

  1. 副本上不能做写操作，所以只适合 SELECT 类排查；
  2. 副本的 hot_standby_feedback 与 vacuum 状态不同，可见性映射可能比主库旧，Index Only Scan 的 Heap Fetches 可能更多。

TIMING OFF 的适用场景 ​

TIMING 需要在每个节点进出时调用系统时钟。当节点执行次数极多（loops 上万）而单次耗时极短时，计时开销会显著拉长总时间，甚至把 1ms 的查询拉到 100ms。

sql
-- 对比：默认 TIMING ON
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 只关心行数与 I/O 时关闭 TIMING
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT ...;

判断准则：

  • 排查行数偏差 → TIMING OFF，减少干扰；
  • 排查耗时分布 → TIMING ON（默认）；
  • 判断是否读磁盘 → 与 BUFFERS 搭配，TIMING 无关。

即使 TIMING OFF，Execution Time 仍然会被测量，因为整个语句的执行时间是必要的汇总信息。

实战示例 ​

场景一：定位嵌套循环被放大成大查询 ​

业务反馈「查询某个城市的订单明细要 30 秒」。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.order_no, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Chengdu'
ORDER BY o.created_at DESC
LIMIT 100;
text
                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=128.40..3421.20 rows=100 width=52) (actual time=1520.400..29845.120 rows=100 loops=1)
   Buffers: shared hit=98421 read=12
   ->  Nested Loop  (cost=128.40..131840.00 rows=4000 width=52) (actual time=1520.380..29844.900 rows=100 loops=1)
         ->  Sort  (actual time=1520.320..1520.330 rows=400 loops=1)
               Sort Key: o.created_at DESC
               ->  Seq Scan on customers c  (actual time=0.018..0.620 rows=400 loops=1)
                     Filter: (city = 'Chengdu'::text)
                     Rows Removed by Filter: 1600
         ->  Index Scan using idx_orders_customer_id on orders o  (actual time=0.021..70.480 rows=40 loops=400)
               Index Cond: (customer_id = c.id)
               Buffers: shared hit=98421

诊断：

  • 内层 Index Scan 单次耗时 70ms（这是关键！），loops=400，总耗时 ≈ 70 × 400 = 28000ms；
  • 单次慢的原因：Buffers: shared hit=98421 分摊到 400 次循环，每次约 246 次缓存命中——说明每个客户的订单行散落在很多数据页上，大量随机访问；
  • 估算 rows=40，实际 rows=40，估算很准，但单次 I/O 成本高。

优化方案：

sql
-- 方案一：让复合索引同时覆盖过滤与排序，让外层直接走索引
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);
ANALYZE orders;

-- 优化后计划会变成从 orders 出发、按排序顺扫，避免 400 次随机回表

场景二：确认哈希连接溢出磁盘 ​

报表查询耗时不稳，偶尔从 2 秒飙到 30 秒。

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.city, count(*), sum(o.amount)
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city;
text
                                  QUERY PLAN
--------------------------------------------------------------------------------
 HashAggregate  (actual time=1850.320..1852.110 rows=5 loops=1)
   Group Key: c.city
   Batches: 1  Memory Usage: 105kB
   Buffers: shared hit=1196, temp read=2048 written=2048
   ->  Hash Join  (actual time=120.400..1420.560 rows=200000 loops=1)
         Hash Cond: (o.customer_id = c.id)
         Buffers: shared hit=1196, temp read=2048 written=2048
         ->  Seq Scan on orders o  (actual time=0.015..380.220 rows=200000 loops=1)
               Buffers: shared hit=1180
         ->  Hash  (actual time=0.480..24.310 rows=2000 loops=1)
               Buckets: 2048  Batches: 8  Memory Usage: 4096kB
               Buffers: shared hit=16, temp written=2048
               ->  Seq Scan on customers c  (actual time=0.014..0.310 rows=2000 loops=1)
 Planning Time: 0.420 ms
 Execution Time: 1862.300 ms

诊断：

  • Hash 节点 Batches: 8——哈希表被分成 8 批，意味着构建时超出内存、写了临时文件（temp written=2048）；
  • Hash Join 与 HashAggregate 也带上了 temp read=2048，说明探测/聚合阶段反复读临时文件；
  • 耗时抖动来自磁盘临时文件与操作系统缓存状态。

修复：

sql
-- 提高 work_mem（注意：哈希表上限 = work_mem × hash_mem_multiplier）
SET work_mem = '64MB';
SET hash_mem_multiplier = 2.0;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.city, count(*), sum(o.amount)
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city;

优化后 Batches: 1，temp read/written 归零，耗时降到 400ms 以内。

注意事项 ​

  • EXPLAIN ANALYZE 会真正执行语句，写操作必须包事务回滚。

    sql
    -- 错误！会真的把数据改掉
EXPLAIN ANALYZE UPDATE orders SET status = 'void' WHERE id < 100;

-- 正确
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'void' WHERE id < 100;
ROLLBACK;

  • 不要把 actual time 当成节点总耗时。 它乘以 loops 才是总耗时。

    text
    -- 错误理解：内层只花 0.042ms，可以忽略
->  Index Scan using idx_orders_customer_id on orders  (actual time=0.021..0.042 rows=24 loops=400)
-- 正确理解：内层真实总耗时 ≈ 0.042 × 400 = 16.8ms，是主要开销

  • 不要把 actual rows 当成节点总行数。 同样要乘以 loops。

    sql
    -- 计划显示 rows=1 但 loops=2000，说明实际处理了 2000 行
-- 估算 rows=1 时若规划器据此选择了 Nested Loop，会比实际便宜很多

  • 不要忽略 Rows Removed by Filter。 它揭示了「扫了很多、留了很少」的低效扫描。

    sql
    -- 不推荐的信号：过滤效率极低
-- Rows Removed by Filter: 199797  /  actual rows=203

  • 看到 external merge 或 Batches > 1 就等于看到磁盘 I/O。 这是 work_mem 不足的直接证据。

    text
    Sort Method: external merge  Disk: 12576kB
Buckets: 2048  Batches: 8  Memory Usage: 4096kB

  • Index Only Scan 有 Heap Fetches 就不是真「只索引」。 Heap Fetches 接近 actual rows 时说明它退化成了普通索引扫描，需要 VACUUM。

    sql
    VACUUM orders;   -- 更新可见性映射，降低 Heap Fetches

  • EXPLAIN ANALYZE 本身有插桩开销。 复杂查询可能比正常执行慢数倍，不要把它测出的绝对时间当作线上真实耗时。

    sql
    -- 只关心行数时关闭 TIMING，减少计时开销
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT ...;

  • Execution Time 不含规划时间、不含网络传输时间。

    sql
    -- 想看序列化开销
EXPLAIN (ANALYZE, SERIALIZE TEXT, COSTS OFF) SELECT ...;

  • 锁等待、触发器、函数内部 SQL 都不在计划树里。 计划显示 1ms 但实际卡 10 秒，要往这些方向查。

    sql
    -- 查看当前会话在等什么
SELECT pid, wait_event_type, wait_event, state
FROM pg_stat_activity WHERE state <> 'idle';

  • 副本上跑 EXPLAIN ANALYZE 时，可见性映射状态可能比主库旧，Heap Fetches 会偏大，不要据此判断主库性能。

最佳实践 ​

  • 生产排查慢查询，优先在只读副本上跑 EXPLAIN (ANALYZE, BUFFERS)。 零风险且不干扰主库。

    sql
    -- 在 replica 上执行
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

  • 养成「先比 rows 与 actual rows」的条件反射。 偏差超过 10 倍就是必须处理的信号。

    text
    rows=2 ... actual rows=1254   -- 偏差 627 倍，先修统计信息

  • loops 明显大于 1 时，第一时间把 actual time 乘回去。 判断节点是否是真正的瓶颈。

    sql
    -- 心算：单次 0.042ms × 400 次 = 16.8ms

  • 把 Rows Removed by Filter 与 actual rows 一起看，算过滤效率。 效率低于 10% 通常意味着缺索引或条件无法下推。

    sql
    -- 过滤效率 = 203 / (203 + 199797) ≈ 0.1%

  • 排查「估算准但慢」时，直接看 Buffers 的 read。 高 read + 准估算 = 物理 I/O 问题，优化方向是索引覆盖、聚簇或缓存。

    text
    Buffers: shared hit=2 read=1043   -- 大部分读磁盘

  • 遇到临时文件就调 work_mem，但要按并发数估算总量。

    sql
    -- 每个排序/哈希操作都可能用到 work_mem，复杂查询可能同时有多个
-- 总内存 ≈ max_connections × work_mem × 并发操作数
SET work_mem = '32MB';

  • 用 EXPLAIN (ANALYZE, VERBOSE) 观察并行 worker 的负载是否均衡。 在并行计划中它会输出每个 worker 的独立统计。

    sql
    EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(*) FROM orders;

  • 需要多次对比时才用 SET 改参数，避免污染全局配置。

    sql
    BEGIN;
SET LOCAL work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
ROLLBACK;

  • 用 TIMING OFF 做行数与 I/O 的对比实验，减少测量噪声。 需要精确到节点的耗时分布时才打开。

    sql
    EXPLAIN (ANALYZE, TIMING OFF, BUFFERS, COSTS OFF) SELECT ...;

  • 把关键查询的执行计划纳入版本管理。 用 JSON 格式保存基线，发版后对比计划是否退化。

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

  • 发现 Heap Fetches 高时，先 VACUUM 再看，而不是急着改索引。

    sql
    VACUUM (ANALYZE) orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

  • 内存不足导致落盘时，优先考虑是否真的需要那么大的哈希表或排序，而非盲目加大 work_mem。 有时导出到应用层聚合更划算。

下一节

继续学习：EXPLAIN 输出解读

参考链接 ​