PostgreSQL EXPLAIN ANALYZE 读法 + 找慢查询的根因

应用慢 90% 是 DB 查询慢。能读 EXPLAIN ANALYZE 输出是 SQL 调优的前置技能。

1. 一个最简单的例子

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

输出：

Index Scan using users_email_idx on users  (cost=0.43..8.45 rows=1 width=84)
                                            (actual time=0.025..0.026 rows=1 loops=1)
  Index Cond: ((email)::text = '[email protected]'::text)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

读懂这几个字段：

Index Scan using users_email_idx：用了哪个索引（好）
cost=0.43..8.45：估算的相对开销（启动..总）
rows=1：估算返回行数
actual time=0.025..0.026：实际启动时间..完成时间（毫秒）
rows=1 loops=1：实际行数 + 循环次数

Execution Time 是最终关注点。

2. 看到 Seq Scan 警觉

Seq Scan on orders  (cost=0.00..18334.00 rows=987 width=58)
                    (actual time=0.123..123.456 rows=982 loops=1)
  Filter: (status = 'pending')
  Rows Removed by Filter: 999018

Seq Scan = 全表扫描。1M 行表里筛 1k 行，扫了 1M 行。
解决：给 status 建索引。

CREATE INDEX idx_orders_status ON orders (status);

但 PG 优化器有时仍选 Seq Scan（如果 status='pending' 占行数比例 > 5-10%
PG 会觉得全扫更快——是对的，索引扫 + 回表的成本可能更高）。

3. estimated vs actual 行数差异

Bitmap Heap Scan on events  (cost=... rows=1)
                            (actual time=... rows=12345 loops=1)

rows=1 估算 vs rows=12345 实际，差 4 个数量级。
说明统计信息陈旧或不准。

ANALYZE events;
-- 更激进
ALTER TABLE events ALTER COLUMN type SET STATISTICS 1000;
ANALYZE events;

STATISTICS 默认 100，加大让统计直方图更细。

PG 自动 ANALYZE 大量更新后触发，但 batch ETL 后建议显式 ANALYZE。

4. Nested Loop / Hash Join / Merge Join

Hash Join  (cost=... )
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (...)
  ->  Hash  (...)
        ->  Seq Scan on users u  (...)

三种 JOIN 策略：

Nested Loop：外层循环 + 内层索引查找。适合小集合 + 内表有索引
Hash Join：内表建 hash 表，外表 lookup。适合两边都大的等值连接
Merge Join：两边排序后归并。适合已排序数据

PG 自动选；策略错（如 nested loop 跑 1M × 1M）会爆。

慢的话试着调 work_mem（影响 Hash 是否能放内存）：

SET work_mem = '256MB';

5. Bitmap Index Scan

Bitmap Heap Scan on logs  (cost=... rows=10000)
  ->  Bitmap Index Scan on logs_date_idx  (cost=...)
        Index Cond: (date >= '2024-01-01' AND date < '2024-02-01')

Bitmap scan 是"读索引拿到所有匹配的 row position 后批量去表里取"。
适合中等选择度（1-30% 行匹配）。Index Scan 适合更小（< 1%），
Seq Scan 适合大量（> 30%）。

6. EXPLAIN ANALYZE BUFFERS

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

输出加上：

Buffers: shared hit=128 read=4520 written=12

hit：命中 shared buffer（内存里的 PG cache）
read：从磁盘读
dirtied / written：脏页 / 写盘

read 数字大 = 慢查询是 I/O 受限的。如果同一查询第二次跑 read 变 hit，
就是缓存效应，单次测试不准。

-- 重置缓存 + 多次跑取平均
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- 跑 3-5 次

7. EXPLAIN (ANALYZE, VERBOSE, FORMAT JSON)

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

JSON 格式适合丢进可视化工具，如：

把 EXPLAIN 输出粘进去，图形化展示树 + 高亮慢节点。强烈推荐。

8. 慢查询日志

postgresql.conf：

log_min_duration_statement = 100   # ms，超过就记录
log_duration = on
log_statement_stats = off

sudo systemctl reload postgresql
sudo tail -f /var/log/postgresql/postgresql-*.log

或用 pg_stat_statements 扩展看 Top N 慢查询：

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements 是生产 PG 的必备扩展之一。

9. 几个常见慢查询根因

A. 缺索引

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Seq Scan + Rows Removed by Filter: 多
-- 解：CREATE INDEX idx_orders_user_id ON orders (user_id);

B. 索引顺序错了

-- 索引 (a, b, c)
SELECT ... WHERE b = ?   -- ❌ 跳过 a，索引用不上
SELECT ... WHERE a = ? AND b = ?   -- ✅ 用上索引前缀

C. 不能用 SARGable 表达式

-- ❌ 函数应用在列上，索引无效
SELECT ... WHERE lower(email) = '[email protected]'

-- ✅ 用函数索引
CREATE INDEX idx_users_email_lower ON users (lower(email));

D. JOIN 时 row estimate 严重偏差

→ ANALYZE 更新统计；或者改写 query 让 PG 估算更准。

E. 大表 DELETE/UPDATE 没回收空间

VACUUM ANALYZE big_table;
VACUUM FULL big_table;   -- 会锁表，谨慎

10. 索引大概念

索引类型	适合
btree	默认；等值、范围、ORDER BY
hash	仅等值（PG 10+ 才 WAL safe）
gin	数组、jsonb、全文搜索
gist	几何、范围类型
brin	大表 + 数据有物理顺序（如 append-only 日志）

99% 用 btree。

11. 部分索引（partial index）

-- 只索引 active 的订单
CREATE INDEX idx_orders_pending_user
ON orders (user_id) WHERE status = 'pending';

索引体积小、维护便宜，但只在查询条件包含 status='pending' 时生效。

12. 表达式索引

CREATE INDEX idx_users_created_month
ON users ((date_trunc('month', created_at)));

-- 查询用同样表达式才命中
SELECT count(*) FROM users
WHERE date_trunc('month', created_at) = '2024-01-01';

踩过的坑

用 EXPLAIN（不带 ANALYZE）只看估算，不真跑 → 估算和实际差很多时
误导。诊断必带 ANALYZE。
EXPLAIN ANALYZE 在 INSERT/UPDATE/DELETE 真的执行！要 dry-run 包
事务：BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
"我加了索引为啥还 Seq Scan"：PG 觉得 Seq Scan 更快（数据量 / 选择度
问题）；ANALYZE 表 + 看 actual stats。
VACUUM FULL 锁表，生产慎用。日常依赖 autovacuum；不够用 pg_repack
在线 repack。