PostgreSQL 查询执行计划深度解析 —— Seq Scan vs Index Scan vs Bitmap Scan 详解

✅ PostgreSQL 查询执行计划深度解析 —— Seq Scan vs Index Scan vs Bitmap Scan 详解

理解执行计划是性能优化的核心能力。本篇将带你深入 PostgreSQL 查询规划器（Planner）内部，详解三大核心扫描方式的原理、适用场景、成本计算及优化策略。

---

🧭 一、执行计划基础

✅ 什么是执行计划（Execution Plan）？

执行计划是 PostgreSQL 查询规划器（Planner） 为 SQL 语句生成的“操作路线图”，描述如何最高效地获取数据。

✅ 如何查看执行计划？

-- 显示计划（不执行）
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 显示计划 + 实际执行统计
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 1;

关键参数：

• ANALYZE：实际执行并显示真实耗时
• BUFFERS：显示 I/O 统计（shared hit/read）
• VERBOSE：显示详细信息
• COSTS：显示成本估算（默认开启）

---

🔍 二、三大核心扫描方式详解

---

✅ 1. 顺序扫描（Seq Scan）

📌 原理：

• 从表的第一行开始，逐行扫描直到最后一行
• 无索引参与，暴力遍历

📊 执行计划示例：

EXPLAIN SELECT * FROM users WHERE age > 25;

-- 输出：
Seq Scan on users  (cost=0.00..25.88 rows=667 width=123)
  Filter: (age > 25)

✅ 适用场景：

• 表很小（< 1000 行）
• 查询条件选择性差（返回 > 10% 数据）
• 无合适索引
• 需要返回大部分列（避免回表开销）

⚠️ 性能问题：

• 大表上极慢（O(n) 复杂度）
• 高 I/O 开销

💡 优化建议：

-- 为条件列创建索引
CREATE INDEX idx_users_age ON users (age);

---

✅ 2. 索引扫描（Index Scan）

📌 原理：

• 利用 B-Tree 索引快速定位行指针（TID）
• 通过 TID 回表（Heap Fetch）获取完整行数据

📊 执行计划示例：

EXPLAIN SELECT * FROM users WHERE id = 100;

-- 输出：
Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=123)
  Index Cond: (id = 100)

✅ 适用场景：

• 等值查询（=）
• 范围查询（<, >, BETWEEN）且结果集小
• 需要排序（ORDER BY 索引列）

⚠️ 性能瓶颈：

• 回表开销：每行都要从堆表读取数据
• 大结果集时比 Seq Scan 慢（随机 I/O vs 顺序 I/O）

💡 优化建议：

-- 使用覆盖索引（PostgreSQL 11+）
CREATE INDEX idx_users_covering ON users (id) INCLUDE (name, email);

-- 或创建包含所有查询列的复合索引
CREATE INDEX idx_users_full ON users (id, name, email);
-- → 可能触发 Index Only Scan（无需回表）

---

✅ 3. 位图扫描（Bitmap Scan）

📌 原理：

1. 先扫描索引，收集所有匹配行的 TID 到**位图（Bitmap）**中
2. 对位图按堆表物理顺序排序
3. 按排序后的 TID 顺序回表（减少随机 I/O）

📊 执行计划示例：

EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- 输出：
Bitmap Heap Scan on users  (cost=4.44..19.96 rows=333 width=123)
  Recheck Cond: ((age >= 25) AND (age <= 35))
  ->  Bitmap Index Scan on idx_users_age  (cost=0.00..4.36 rows=333 width=0)
        Index Cond: ((age >= 25) AND (age <= 35))

✅ 适用场景：

• 中等结果集（几百~几千行）
• 多条件 OR 查询
• 多索引 BitmapAnd/BitmapOr

🌟 核心优势：

• 将随机 I/O 转化为顺序 I/O → 大幅提升磁盘性能
• 支持多索引合并

💡 多条件示例：

EXPLAIN SELECT * FROM users 
WHERE age BETWEEN 25 AND 35 
   OR city = '北京';

-- 输出：
Bitmap Heap Scan on users
  ->  BitmapOr
        ->  Bitmap Index Scan on idx_users_age
        ->  Bitmap Index Scan on idx_users_city

---

🆚 三、三大扫描方式对比表

特性	Seq Scan	Index Scan	Bitmap Scan
原理	全表顺序扫描	索引定位 + 回表	索引定位 → 位图 → 排序回表
I/O 模式	顺序 I/O	随机 I/O	顺序 I/O（优化后）
适用结果集大小	大（> 10%）	小（< 1%）	中（1%~10%）
是否回表	否（直接读数据）	是	是
多条件支持	支持	差（通常单条件）	优秀（BitmapAnd/Or）
排序能力	需额外 Sort 节点	可利用索引有序	需额外 Sort 节点
成本估算关键	表大小	索引选择性	结果集大小 + I/O 优化

💡 规划器决策依据：成本估算（Cost） = CPU 成本 + I/O 成本

---

🧮 四、成本计算与规划器决策

PostgreSQL 通过以下参数估算成本：

SHOW seq_page_cost;   -- 1.0 (默认)
SHOW random_page_cost;-- 4.0 (默认，SSD 建议设为 1.1)
SHOW cpu_tuple_cost;  -- 0.01
SHOW cpu_index_tuple_cost; -- 0.005
SHOW cpu_operator_cost;    -- 0.0025

📈 成本计算示例：

-- 假设表有 10000 行，100 页
-- 查询返回 100 行（1%）

-- Seq Scan 成本 ≈ 100 * seq_page_cost + 10000 * cpu_tuple_cost
--               = 100*1.0 + 10000*0.01 = 100 + 100 = 200

-- Index Scan 成本 ≈ 3 (索引页) * random_page_cost + 100 * random_page_cost + 100 * cpu_index_tuple_cost
--                 = 3*4.0 + 100*4.0 + 100*0.005 = 12 + 400 + 0.5 = 412.5

-- → 规划器选择 Seq Scan！

⚙️ 优化参数（SSD 环境）：

-- 降低随机读成本（SSD 随机读不慢）
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '8GB'; -- 告诉规划器系统有多少缓存

-- 重载配置
SELECT pg_reload_conf();

---

🔬 五、高级扫描类型

---

✅ 4. 仅索引扫描（Index Only Scan）

📌 原理：

• 索引包含查询所需所有列 → 无需回表
• 依赖 Visibility Map 判断数据可见性

📊 执行计划：

EXPLAIN SELECT id, name FROM users WHERE id < 100;

-- 输出：
Index Only Scan using idx_users_covering on users  (cost=0.29..8.31 rows=99 width=36)
  Index Cond: (id < 100)
  Heap Fetches: 0  -- 关键！0 表示无需回表

✅ 触发条件：

• 查询列都在索引中
• 数据页在 Visibility Map 中标记为全部可见
• VACUUM 后效果最佳

💡 创建覆盖索引：

-- PostgreSQL 11+
CREATE INDEX idx_users_covering ON users (id) INCLUDE (name, email);

-- 旧版本
CREATE INDEX idx_users_covering ON users (id, name, email);

---

✅ 5. 位图索引扫描（Bitmap Index Scan）

这是 Bitmap Scan 的第一阶段：

EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- 输出：
Bitmap Heap Scan on users
  ->  Bitmap Index Scan on idx_users_age  -- ← 这里！
        Index Cond: ((age >= 25) AND (age <= 35))

---

🎯 六、性能优化实战案例

---

📊 案例1：选择性差的查询

-- 表：orders (1,000,000 行)
-- 查询：SELECT * FROM orders WHERE status = 'completed'; -- 返回 80% 数据

EXPLAIN ANALYZE:
Seq Scan on orders  (cost=0.00..25880.00 rows=800000 width=123)
  Filter: (status = 'completed'::text)
Execution Time: 450 ms

-- ❌ 建索引反而更慢！
CREATE INDEX idx_orders_status ON orders (status);
-- Index Scan 成本更高（随机 I/O）

✅ 结论：高选择性查询才建索引！

---

📊 案例2：复合索引优化

-- 查询：SELECT * FROM orders WHERE user_id = 123 AND created_at > '2025-01-01'

-- ❌ 单列索引效果差
CREATE INDEX idx_orders_user ON orders (user_id); -- 只能过滤 user_id
CREATE INDEX idx_orders_date ON orders (created_at); -- 只能过滤日期

-- ✅ 复合索引完美匹配
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

EXPLAIN:
Index Scan using idx_orders_user_date on orders
  Index Cond: (user_id = 123 AND created_at > '2025-01-01')
Execution Time: 0.5 ms

---

📊 案例3：Bitmap Scan 优势

-- 查询：SELECT * FROM users WHERE age BETWEEN 25 AND 35; -- 返回 5000 行

-- Index Scan (随机 I/O):
Execution Time: 120 ms

-- Bitmap Scan (顺序 I/O):
Execution Time: 35 ms  -- 快 3.4 倍！

-- 规划器自动选择 Bitmap Scan 当结果集较大时

---

🛠 七、执行计划调优工具

---

✅ 1. EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = 'alice@example.com';

关注：

• actual time：真实耗时
• rows：预估 vs 实际行数（偏差大需 ANALYZE）
• Buffers: shared hit/read：缓存命中率
• I/O Timings：磁盘 I/O 耗时

---

✅ 2. pg_stat_statements（需启用）

-- 查看最慢查询
SELECT query, total_exec_time, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

---

✅ 3. auto_explain（记录慢查询计划）

-- postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true

---

📝 八、优化 Checklist

1. 检查执行计划：是否使用了预期索引？
2. 分析行数偏差：EXPLAIN 预估行数 vs ANALYZE 实际行数
3. 更新统计信息：ANALYZE table_name;
4. 检查缓存命中：Buffers: shared hit vs read
5. 调整成本参数：SSD 环境降低 random_page_cost
6. 考虑覆盖索引：避免回表
7. 大结果集用 Bitmap Scan：规划器通常自动选择
8. 避免过度索引：写多读少的表

---

🎯 实践任务

请完成以下操作：

1. 创建 users 表（100 万行测试数据）
2. 执行 EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30; → 观察 Seq Scan
3. 为 age 创建索引，再次执行 → 观察 Index Scan 或 Bitmap Scan
4. 执行 EXPLAIN ANALYZE SELECT id FROM users WHERE age > 30; → 尝试触发 Index Only Scan
5. 调整 random_page_cost = 1.1，观察执行计划变化
6. 使用 pg_stat_user_indexes 监控索引使用情况