优化器为什么不用你的索引

准备中…

「我建了索引，为什么还是走全表扫描？」——这个问题的答案几乎从来不是「优化器有 bug」。这一节我们用四组对照实验把它拆开。

起点：没有索引

SELECT * FROM orders WHERE user_id = 42

Seq Scan，读了 74 个缓冲块——那就是整张 orders 表。10000 行里只有 10 行符合条件，却翻遍了全表。

建个索引：

CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id);
ANALYZE orders;

⌘/Ctrl + Enter

SELECT * FROM orders WHERE user_id = 42

块数从 74 掉到 12 左右。注意节点类型是 Bitmap Heap Scan 套 Bitmap Index Scan，而不是直接的 Index Scan——当预计命中多行时，Postgres 会先用索引攒一张「哪些页面需要读」的位图，再按物理顺序一次性读这些页面，把随机 I/O 转成顺序 I/O。

实验一：选择率决定一切

同样有没有索引不重要，重要的是这个条件能过滤掉多少行：

CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
ANALYZE orders;

⌘/Ctrl + Enter

SELECT * FROM orders WHERE status = 'paid'

索引就在那里，优化器依然选了 Seq Scan。而且这是对的：status 只有四个取值，'paid' 命中约 2500 行，占全表 25%。走索引意味着要回表读 2500 次——比顺序读完 74 个块贵得多。

实验二：复合索引的前缀是硬约束

CREATE INDEX IF NOT EXISTS idx_orders_user_created ON orders(user_id, created_at);
ANALYZE orders;

⌘/Ctrl + Enter

索引 (user_id, created_at) 对带 user_id 条件的查询有效：

SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at

但对只查第二列的查询无效：

SELECT * FROM orders WHERE created_at > '2025-01-01'

又退回 Seq Scan 了。原因在 B-tree 的物理结构：索引项是按 (user_id, created_at) 依次排序的，就像电话簿按「姓，名」排。给定姓可以快速定位，只给定名则毫无帮助——同名的人分散在整本书里。

所以复合索引的列顺序不是风格问题，它决定了这个索引能服务哪些查询。

实验三：Index-Only Scan 与阶段三的伏笔

如果查询要的列全部在索引里，理论上根本不必回表。试试：

SELECT user_id, created_at FROM orders WHERE user_id = 42

user_id 和 created_at 都在索引中，却仍然是 Bitmap Heap Scan——它还是回表了。

因为索引里没有可见性信息。索引项不记录 xmin/xmax，Postgres 无法仅凭索引判断某一行对当前事务是否可见，只能回表去看。

除非——那一页已经被标记为「对所有人可见」。还记得阶段三的可见性映射吗：

VACUUM orders;

⌘/Ctrl + Enter

SELECT user_id, created_at FROM orders WHERE user_id = 42

节点类型变成了 Index Only Scan，读取的块数再降一个台阶。

VACUUM 除了回收死元组，还把「这一页全部可见」写进了可见性映射；有了它，索引扫描才敢跳过回表。Index-Only Scan 不是一个可以单独打开的开关，它是 VACUUM 维护状态良好的副产品。

本节验收

先自己回答，再点开对照。

什么情况下优化器会正确地放弃索引？给出一个大致的选择率阈值。

当命中行占全表的比例超过 5%~10% 时，索引扫描开始不划算，选 Seq Scan 是对的。

实验二里的 status = 'paid' 就是这种情况：status 只有四个取值，'paid' 命中约 2500 行，占全表 25%。走索引意味着要回表读 2500 次，而顺序读完整张表只需要 74 个块。索引就在那里，优化器依然选了 Seq Scan——这不是它没看见索引，是它算过账。

常见错误一：以为「至少能少读 75% 的数据，怎么算都比全表扫便宜」。这笔账错在回表的粒度是页不是行。2500 行分散在全表 74 个页里，几乎每个页都会被读到——等于全表扫了一遍，还额外付了一次索引扫描的钱。真正省下来的从来不是「行数比例」，而是「不用碰的页数」。

常见错误二：把选择率当成命中的绝对行数。它是比例：一张 100 行的表命中 5 行是 5%，一张一亿行的表命中 100 万行绝对值吓人，却只有 1%——后者反而是索引最该发挥作用的场景。

（详见统计信息：优化器凭什么做决定）

Bitmap Index Scan 相比 Index Scan 解决了什么问题？

解决命中多行时回表的随机 I/O。

Index Scan 是拿到一个索引项就回表读一次，命中行多时就是一长串随机跳跃，而且同一个页可能被反复访问。Bitmap Index Scan 先把索引扫完，攒出一张「哪些页面需要读」的位图，交给上层的 Bitmap Heap Scan 按物理顺序一次性读这些页——随机 I/O 变成顺序 I/O，每个页只读一次。实测 WHERE user_id = 42 从 Seq Scan 的 74 个块降到 12 个块左右。

常见错误：把 Bitmap 当成 Index Scan 的「加强版」，看到它就放心。它出现恰恰说明优化器预计命中行数已经不小了——它是 Index Scan 和 Seq Scan 之间的中间态。它还有一个容易忽略的副作用：既然是按物理顺序读页，输出就不再保持索引顺序。一条指望靠索引省掉排序的 ORDER BY 查询，一旦走成 Bitmap，计划里就会重新冒出 Sort 节点。

索引 (a, b) 为什么加速不了 WHERE b = ?

因为索引项是按 (a, b) 依次排序的，就像电话簿按「姓，名」排。给定姓可以快速定位；只给定名毫无帮助——同名的人分散在整本书里。

实验二里 (user_id, created_at) 索引对 WHERE user_id = 42 ORDER BY created_at 有效，对 WHERE created_at > '2025-01-01' 就退回 Seq Scan。复合索引的列顺序不是风格问题，它决定了这个索引能服务哪些查询。

常见错误：「反正 b 的值也在索引里，把整个索引扫一遍总比扫全表便宜吧？」——这个推理看起来很硬，但优化器的账不是这么算的。全索引扫描要读完所有索引项，命中的那些还要各自回表做一次随机 I/O；而顺序扫全表是纯顺序读。除非表很宽而索引很窄，否则总代价通常更高，所以优化器直接选了 Seq Scan。索引「能用」和「值得用」是两件事——这一节从头到尾讲的都是后者。

（从物理结构看这个约束）

为什么 Index-Only Scan 依赖 VACUUM？把因果链完整说一遍。

因为索引项里不存可见性信息，而唯一能替它做可见性判断的可见性映射是 VACUUM 维护的。

完整的链条：

索引项不记录 xmin / xmax → 无法判断某一行对当前事务是否可见 → 只能回表看元组头；
除非那一页在可见性映射里被标记 all_visible（这一页上所有元组对所有事务都可见）→ 不用看也知道答案 → 可以跳过回表；
而这个标记只有 VACUUM 会写 → 长事务阻止 VACUUM 清理 → 可见性映射失效 → Index Only Scan 退化成 Bitmap Heap Scan → 查询突然变慢。

实验三就是这条链的正向演示：user_id 和 created_at 都在索引里，查询却仍然是 Bitmap Heap Scan；跑完 VACUUM orders 之后才变成 Index Only Scan，块数再降一个台阶。

常见错误：把 VACUUM 理解成「清理死元组、回收空间」的纯维护动作，于是为了减少 I/O 把 autovacuum 调得很保守，或者在业务高峰期干脆推迟它。推迟 VACUUM 的代价不只是表膨胀——它会直接让已经上线的 Index Only Scan 退化。生产环境里「同一条查询昨天快今天慢，SQL 和索引一个字都没改」，相当一部分就是这条链断在了 VACUUM 那一环。

（详见表膨胀与 VACUUM）