EXPLAIN ANALYZE 输出解读

EXPLAIN ANALYZE 是 PostgreSQL 查询优化的核心工具。它显示查询执行计划（计划器的决定）和实际执行统计（真实的数据）。掌握它，你就能从"感觉应该加索引"升级到"有数据支撑的优化决策"。

EXPLAIN 和 EXPLAIN ANALYZE 的区别

-- EXPLAIN：只显示执行计划（不实际执行查询）
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- EXPLAIN ANALYZE：执行查询并显示实际统计（会真正运行！）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- 完整版（推荐）：显示缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123;
-- 输出 JSON 格式（可程序化分析）
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE user_id = 123;

注意：EXPLAIN ANALYZE 会真正执行查询。对于 UPDATE/DELETE/INSERT，用 ROLLBACK 包裹： sql BEGIN; EXPLAIN ANALYZE UPDATE orders SET status = 'paid' WHERE id = 1; ROLLBACK;

输出结构解读

Seq Scan on orders  (cost=0.00..4821.00 rows=50000 width=128) (actual time=0.017..45.234 rows=50000 loops=1)
│                    │     │      │      │           │         │     │          │           │         │
│                    └─────┴──────┘      │           │         └─────┴──────────┘           │         │
│                    启动成本..总成本     │           │         实际启动时间..结束时间       │         │
│                                        └───────────┘                                      └─────────┘
│                                        估计行数 x 每行宽度（字节）                        实际行数  循环次数
└── 节点类型

关键字段含义：

扫描节点类型

Seq Scan         → 全表顺序扫描（没有索引，或索引不值得用）
Index Scan       → 索引扫描（用索引找到行位置，再访问堆表）
Index Only Scan  → 纯索引扫描（覆盖索引，不访问堆表，最快）
Bitmap Index Scan → 位图索引扫描（多索引组合，OR 条件）

实例对比：

-- 全表扫描（无索引）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- Seq Scan on orders  (cost=0.00..4821.00 rows=1 width=128)
--                     (actual time=0.017..45.234 rows=47 loops=1)
-- 创建索引后
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.43..187.21 rows=47 width=128)
--   (actual time=0.087..0.312 rows=47 loops=1)
--   Index Cond: (user_id = 123)
-- 耗时从 45ms 降到 0.3ms

JOIN 节点类型

Nested Loop   → 嵌套循环（小表驱动大表，内表有索引时效果好）
Hash Join     → 哈希连接（大表之间，一个表建哈希表，另一个扫描）
Merge Join    → 归并连接（两个表都已排序时使用）

EXPLAIN ANALYZE
SELECT o.id, u.email, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
AND o.created_at > NOW() - INTERVAL '30 days';
-- 可能输出：
-- Hash Join  (cost=...)  (actual time=... rows=... loops=1)
--   Hash Cond: (o.user_id = u.id)
--   -> Index Scan on orders  (cost=...)
--        Filter: (status = 'paid' AND created_at > ...)
--   -> Hash  (cost=...)
--        -> Seq Scan on users  (cost=...)

识别性能问题的关键信号

信号 1：估计行数 vs 实际行数差距过大

rows=1   actual rows=50000  ← 估计严重低估

原因：统计信息过时。解决：ANALYZE orders; 或等待 autovacuum 运行。

信号 2：Seq Scan on 大表

Seq Scan on orders  (actual time=0.017..4512.345 rows=100000000 ...)

原因：没有合适的索引，或者条件不够选择性。解决：添加索引，或检查 WHERE 条件是否用了函数。

信号 3：索引扫描变成全表扫描

-- ❌ 索引失效：WHERE 子句中对索引列使用了函数
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
-- → Seq Scan（created_at 上的索引无法用于 DATE() 函数）
-- ✅ 修复：用范围条件代替函数
SELECT * FROM orders
WHERE created_at >= '2024-01-15 00:00:00'
AND created_at < '2024-01-16 00:00:00';
-- → Index Scan

信号 4：loops 很大

Nested Loop  (actual time=0.1..5234.1 rows=100000 loops=100000)

含义：内表被循环了 10 万次，N+1 查询的特征。解决：改为 JOIN 或批量查询。

常用优化手法速查

-- 强制刷新统计信息（解决 rows 估计不准）
ANALYZE users;
ANALYZE orders;
-- 或全库
ANALYZE;
-- 查看表的统计信息
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders';
-- 对 HIGH correlation 的列（有序），BRIN 可能比 B-tree 更好
-- correlation 接近 1 或 -1 表示物理存储有序
-- 检查缓存命中率（低于 95% 可能需要增加 shared_buffers）
SELECT
heap_blks_read,
heap_blks_hit,
round(heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read), 2) AS hit_ratio
FROM pg_statio_user_tables
WHERE relname = 'orders';

用 explain.depesz.com 可视化分析

对于复杂的 EXPLAIN 输出，可以粘贴到 explain.depesz.com 获取可视化的分析——它会自动高亮最慢的节点，比人工分析文本快得多。

本节记录清单

• [ ] 对你最慢的查询运行 EXPLAIN (ANALYZE, BUFFERS)
• [ ] 检查是否有 rows 估计与实际严重偏差的节点
• [ ] 确认 WHERE 条件没有在索引列上使用函数（导致索引失效）
• [ ] 检查是否有 Seq Scan on 大表（超过 100 万行）

下一节：慢查询识别与优化实战——知道了 EXPLAIN 怎么读，如何系统地找到和修复生产环境的慢查询？