PostgreSQL 的 enable_indexonlyscan：为什么“只扫索引”仍然会碰堆表

2026-06-30 22 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：7 分钟

在 PostgreSQL 里，索引不只有一种用法。普通 Index Scan 会沿着索引找到行，再回表读取堆表；Bitmap Scan 会先收集一批 TID，再批量访问堆表；Index Only Scan 看起来最诱人：如果查询需要的列都在索引里，理论上就不用碰堆表。

但 enable_indexonlyscan 最容易被误解的地方也在这里：“物理上可以使用 Index Only Scan”不等于“执行时真的只读索引”。很多查询计划显示了 Index Only Scan，实际却因为可见性检查而几乎每行都要访问 heap。

Index Only Scan 到底省掉了什么

PostgreSQL 的表数据存放在 heap 里，索引项通常保存键值和指向 heap tuple 的位置。普通索引扫描大致是：

  1. 从索引定位候选行；
  2. 根据 TID 去 heap 读取完整 tuple；
  3. 检查 MVCC 可见性；
  4. 返回需要的列。

Index Only Scan 想跳过第 2 步：如果查询列全部能从索引获得，就直接从索引返回结果。

例如这个查询：

SELECT email
FROM users
WHERE account_id = 42;

如果有如下索引：

CREATE INDEX users_account_id_email_idx
ON users (account_id, email);

从列覆盖角度看，account_id 用于过滤，email 用于返回，索引已经够了。规划器就有机会选择 Index Only Scan。

真正的问题是：PostgreSQL 还必须确认这行对当前事务是否可见。这个信息主要在 heap tuple 上，而不在普通索引项里。

最容易踩坑的条件：Visibility Map

PostgreSQL 用 visibility map 解决这个矛盾。它为 heap page 记录一个很关键的状态：这个 page 上的 tuple 是否都对所有事务可见，也就是 all-visible。

当某个 heap page 被标记为 all-visible 时，Index Only Scan 读取到指向这个 page 的索引项后，就可以相信该 page 上的 tuple 对当前查询可见，不必再访问 heap。

如果 page 没有 all-visible 标记，哪怕查询列都在索引里，执行器仍然要去 heap 检查可见性。

这就是那个看起来反直觉的现象：

Index Only Scan using users_account_id_email_idx on users
  Heap Fetches: 98765

计划节点叫 Index Only Scan，但 Heap Fetches 很高，说明它并没有真正避开 heap。它只是“有资格尝试只扫索引”，但可见性信息不允许它完全这么做。

可以这样实践：用 EXPLAIN 看清 Heap Fetches

下面是一组可以在测试库里改造运行的 SQL。表名和数据量可以按你的环境调整。

DROP TABLE IF EXISTS ios_demo;

CREATE TABLE ios_demo (
  id bigserial PRIMARY KEY,
  account_id integer NOT NULL,
  email text NOT NULL,
  payload text NOT NULL
);

INSERT INTO ios_demo (account_id, email, payload)
SELECT
  (random() * 1000)::integer,
  'user-' || gs || '@example.com',
  repeat('x', 200)
FROM generate_series(1, 200000) AS gs;

CREATE INDEX ios_demo_account_email_idx
ON ios_demo (account_id, email);

ANALYZE ios_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT email
FROM ios_demo
WHERE account_id = 42;

你需要关注输出里的两处：

Index Only Scan using ios_demo_account_email_idx ...
Heap Fetches: ...

接着执行 vacuum，再观察变化：

VACUUM (ANALYZE) ios_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT email
FROM ios_demo
WHERE account_id = 42;

如果表页能被标记为 all-visible，第二次执行通常会看到 Heap Fetches 明显下降，甚至接近 0。这个实验能直接说明：Index Only Scan 的收益不只取决于索引是否覆盖列，还取决于表页的可见性状态。

如果你想强制观察不同扫描方式，也可以临时调整 GUC：

SET enable_indexonlyscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT email
FROM ios_demo
WHERE account_id = 42;

RESET enable_indexonlyscan;

注意，这类 enable_* GUC 更适合诊断和实验，不适合当成生产环境的长期优化开关。真正应该优化的是索引设计、查询形态、vacuum 状态和表的更新模式。

为什么频繁更新的表更难受益

Index Only Scan 对“读多写少、页面稳定”的表更友好。原因很直接：

  • 新插入或刚更新过的 tuple 需要经过 vacuum 等过程，相关 page 才可能重新获得 all-visible 状态；
  • 频繁 UPDATE 会产生新版本 tuple，也会让 visibility map 的 all-visible 信息失效；
  • 长事务会拖住可见性判断，让旧版本 tuple 无法及时清理；
  • 表上即使有覆盖索引，也可能因为 heap page 状态不理想而出现大量 heap fetch。

因此，看到 Index Only Scan 不要急着庆祝。真正要看的是 EXPLAIN (ANALYZE, BUFFERS) 中的实际执行信息。

采用建议：别只看计划节点名称

使用 enable_indexonlyscan 或分析 Index Only Scan 时，可以按这个清单排查：

  • 查询需要的列是否都在索引中，必要时考虑 INCLUDE 列；
  • EXPLAIN (ANALYZE, BUFFERS) 里的 Heap Fetches 是否足够低；
  • autovacuum 是否及时，表是否长期缺少 vacuum；
  • 表是否频繁更新，是否存在拖很久的事务；
  • 这个索引是否为了少数查询过度膨胀，增加了写入成本。

Index Only Scan 是 PostgreSQL 很实用的能力，但它不是“覆盖索引等于不回表”的简单故事。enable_indexonlyscan 控制的是规划器是否考虑这种路径，而真正决定它能省多少 I/O 的，是索引覆盖、visibility map、vacuum 和工作负载共同作用的结果。

# AI # PostgreSQL # 数据库

相关推荐