PostgreSQL / 系统课时
验证索引仅扫描
验证索引仅扫描
本课解决“已经建立了 INCLUDE 覆盖索引,为什么执行计划仍然访问堆表”的问题。核心是区分两个条件:查询所需数据是否全部在索引中,以及对应堆页是否通过可见性映射可以免去实际堆访问。
学习目标
- 判断一个查询是否具备使用 Index Only Scan 的数据覆盖条件
- 理解 Visibility Map 为什么决定是否真的能跳过堆访问
- 用
EXPLAIN (ANALYZE, BUFFERS)和Heap Fetches验证覆盖索引是否真正产生收益
Index Only Scan 需要两个条件
第一,查询需要的列必须能够直接从索引获得,包括过滤条件、返回列以及执行该计划所需的索引数据。INCLUDE 可以把额外的 payload 列存进 B-tree 索引,使“只需要这些列”的查询具备 Index Only Scan 的数据基础。
第二,PostgreSQL 仍然需要确认行对当前 MVCC 快照可见。索引项本身不保存完整的可见性信息,因此执行器会检查表对应堆页的 Visibility Map;只有页面被标记为 all-visible 时,才可以不访问堆即可确认可见性。:contentReference[oaicite:2]{index=2}

用 INCLUDE 建立一个可验证的覆盖索引
下面创建一张订单表,并让 customer_id 负责搜索、status 作为不参与排序和搜索的 payload 列。随后执行 VACUUM,让满足条件的堆页有机会被标记为 all-visible,再观察执行计划。
CREATE TABLE customer_orders (
id bigint PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL
);
INSERT INTO customer_orders (id, customer_id, status)
SELECT g,
1 + (g % 1000),
CASE WHEN g % 10 = 0 THEN 'paid' ELSE 'pending' END
FROM generate_series(1, 100000) AS g;
CREATE INDEX customer_orders_customer_idx
ON customer_orders (customer_id) INCLUDE (status);
VACUUM (ANALYZE) customer_orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, status
FROM customer_orders
WHERE customer_id = 42;预期结果:
Index Only Scan using customer_orders_customer_idx on customer_orders
Index Cond: (customer_id = 42)
Heap Fetches: 0具体的 actual time、rows 和 Buffers 数字会因环境不同而变化。这里最关键的证据是计划节点为 Index Only Scan,并且 Heap Fetches 很低或为 0。PostgreSQL 官方文档也强调,Index Only Scan “可用”并不代表一定更快;只有足够多的堆页处于 all-visible 状态时,跳过堆访问才真正有价值。:contentReference[oaicite:3]{index=3}
为什么 INCLUDE 不等于零次堆访问
INCLUDE 解决的是“需要返回的列不在普通索引键中”这一问题。例如 ON customer_orders (customer_id) INCLUDE (status) 可以让 status 直接从索引获得,但它不会把 MVCC 可见性信息复制到每个索引项里。因此表频繁更新时,即使索引覆盖完整,执行器仍可能需要回到 Heap 检查对应版本是否可见。
判断是否真正获得收益时,优先观察三个信号:
- 计划节点是否为
Index Only Scan,而不是普通Index Scan。 Heap Fetches是否很低。它明显偏高时,说明查询仍在频繁访问堆。- 表是否长期稳定。更新越频繁,堆页的 all-visible 状态越难长期保持,覆盖索引的理论优势就越可能被实际堆访问抵消。
用执行计划验证是否值得保留
最终的验证标准不是“索引包含了查询列”,而是“索引覆盖 + 可见性条件”共同成立,并且实际执行成本因此下降。对一个线上索引候选,可以分别记录普通索引、覆盖索引和执行后的 Heap Fetches,再结合 BUFFERS 判断减少的堆访问是否值得承担更大的索引存储与写入维护成本。
因此,设计覆盖索引时应把 INCLUDE 看成一种可能实现 Index Only Scan 的结构设计,而不是性能保证。只有执行计划和运行数据都证明堆访问被显著减少,这个索引才真正达成了优化目标。