PostgreSQL 索引与 EXPLAIN 分析实战教程

适用读者：使用 PostgreSQL 的后端开发者与 DBA，希望系统掌握查询优化技能。
预计学习时长：55 分钟 · 含动手实验

学习目标

完成本教程后，你将能够：

1. 理解 PostgreSQL 主要索引类型及适用场景
2. 根据查询模式设计合理的单列、复合、部分索引
3. 使用 EXPLAIN 和 EXPLAIN ANALYZE 解读查询计划
4. 识别 Seq Scan、Index Scan、Bitmap Scan 等关键节点
5. 将慢查询从秒级优化到毫秒级

前置知识

• SQL 基础（SELECT、WHERE、JOIN、ORDER BY）
• 了解 PostgreSQL 基本安装与 psql 使用
• 理解主键、外键概念

---

第一章：准备实验环境

1.1 创建测试表

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    amount      NUMERIC(12,2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 插入 100 万条测试数据
INSERT INTO orders (user_id, status, amount, created_at)
SELECT
    (random() * 10000)::BIGINT,
    (ARRAY['pending','paid','shipped','cancelled'])[floor(random()*4+1)],
  (random() * 10000)::NUMERIC(12,2),
    now() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

ANALYZE orders;

1.2 开启计时

\timing on

---

第二章：索引类型详解

2.1 B-tree（默认）

最常用的索引类型，支持 =、<、>、BETWEEN、IN、ORDER BY。

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

2.2 复合索引

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

最左前缀原则：此索引可加速 WHERE user_id = ? 和 WHERE user_id = ? AND status = ?，但无法加速单独的 WHERE status = ?。

2.3 部分索引（Partial Index）

只为满足条件的行建索引，节省空间：

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

适合高频查询某个子集的场景（如未支付订单）。

2.4 GIN 索引

适用于数组、JSONB、全文搜索：

ALTER TABLE orders ADD COLUMN tags JSONB;
CREATE INDEX idx_orders_tags ON orders USING GIN(tags);

-- 查询
SELECT * FROM orders WHERE tags @> '["vip"]';

2.5 BRIN 索引

适合物理有序的大表（如按时间追加的日志）：

CREATE INDEX idx_orders_created_brin ON orders USING BRIN(created_at);

体积极小，适合数亿行的时间序列数据。

---

第三章：EXPLAIN 解读

3.1 基本用法

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

输出关键信息：

Seq Scan on orders  (cost=0.00..25000.00 rows=100 width=48)
  Filter: (user_id = 42)

• Seq Scan：全表扫描，100 万行时很慢
• cost：估算成本（启动成本..总成本）
• rows：估算返回行数

3.2 添加索引后

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Index Scan using idx_orders_user_id on orders
  (cost=0.42..8.44 rows=100 width=48)
  Index Cond: (user_id = 42)

Index Scan：通过索引直接定位行，cost 从 25000 降到 8。

3.3 EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Index Scan using idx_orders_user_status on orders
  (cost=0.42..50.00 rows=25 width=48)
  (actual time=0.025..0.089 rows=20 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'paid'::text))
  Buffers: shared hit=15
Planning Time: 0.15 ms
Execution Time: 0.12 ms

关注指标：

指标	含义
actual time	实际耗时（首行..末行）ms
rows	实际返回行数 vs 估算行数
Buffers shared hit	缓存命中页数
Execution Time	总执行时间

估算行数与实际行数偏差大时，需运行 ANALYZE 更新统计信息。

3.4 常见扫描类型

类型	说明	何时出现
Seq Scan	全表扫描	无合适索引或返回行数 > 表 5-10%
Index Scan	索引扫描+回表	精确查找少量行
Index Only Scan	仅索引	查询列全在索引中（覆盖索引）
Bitmap Index Scan	位图索引扫描	中等选择性
Bitmap Heap Scan	位图堆扫描	配合 Bitmap Index

---

第四章：实战优化案例

4.1 案例一：慢分页

问题查询：

SELECT * FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
OFFSET 100000 LIMIT 20;

OFFSET 越大越慢，因为数据库仍需扫描并丢弃前 100000 行。

优化方案——游标分页：

SELECT * FROM orders
WHERE status = 'paid'
  AND created_at < '2025-06-01T00:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

配合索引：

CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);

4.2 案例二：覆盖索引

-- 查询只需要 id 和 amount
SELECT id, amount FROM orders WHERE user_id = 42;

-- 覆盖索引：索引包含所有查询列，无需回表
CREATE INDEX idx_orders_user_covering
ON orders(user_id) INCLUDE (amount);

EXPLAIN 将显示 Index Only Scan。

4.3 案例三：JOIN 优化

EXPLAIN ANALYZE
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > now() - interval '7 days';

确保 JOIN 列和 WHERE 列都有索引：

CREATE INDEX idx_orders_created_at ON orders(created_at);
-- users.id 通常是主键，已有索引

---

第五章：索引设计原则

1. 为 WHERE、JOIN、ORDER BY 中的列建索引
2. 高选择性列优先（如 user_id），低选择性列（如 gender）谨慎
3. 复合索引列顺序：等值条件在前，范围条件在后
4. 避免过度索引：每个索引增加写入开销和存储空间
5. 定期维护：REINDEX、VACUUM ANALYZE
6. 使用 pg_stat_user_indexes 监控未使用索引

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

idx_scan = 0 的索引考虑删除。

---

练习 / 作业

1. 在测试表上创建 3 种不同类型索引，分别用 EXPLAIN 对比有无索引的差异。
2. 制造一个「估算行数偏差大」的场景（如数据倾斜），用 ANALYZE 修复。
3. 将 OFFSET 分页改为游标分页，记录性能提升倍数。
4. 使用 pg_stat_statements 扩展找出最慢的 5 条 SQL 并优化。
5. 进阶：为 JSONB 字段创建 GIN 索引，实现标签搜索。

---

FAQ

Q：是不是所有列都应该建索引？

A：不是。写入频繁的表、低选择性列、小表（< 1000 行）不需要索引。

Q：EXPLAIN 中 cost 的单位是什么？

A：任意成本单位，用于比较相对开销，不是毫秒。实际耗时看 EXPLAIN ANALYZE 的 actual time。

Q：主键和唯一约束会自动建索引吗？

A：是。PostgreSQL 自动为 PRIMARY KEY 和 UNIQUE 约束创建 B-tree 索引。

Q：分区表如何建索引？

A：在父表上建索引会自动传播到所有分区。也可在各分区单独建索引。

Q：如何监控慢查询？

A：设置 log_min_duration_statement = 1000（记录超过 1 秒的查询），配合 pgBadger 分析。

---

小结

PostgreSQL 查询优化的核心是：理解数据分布 → 设计合适索引 → 用 EXPLAIN ANALYZE 验证。B-tree 是通用首选，GIN 适合 JSONB/全文，BRIN 适合时序大表。避免盲目建索引，用 pg_stat_user_indexes 清理无用索引，用 pg_stat_statements 持续监控慢查询。