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}

Index Only Scan 的覆盖条件与可见性检查

用 INCLUDE 建立一个可验证的覆盖索引

下面创建一张订单表,并让 customer_id 负责搜索、status 作为不参与排序和搜索的 payload 列。随后执行 VACUUM,让满足条件的堆页有机会被标记为 all-visible,再观察执行计划。

sql
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;

预期结果:

text
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 检查对应版本是否可见。

判断是否真正获得收益时,优先观察三个信号:

  1. 计划节点是否为 Index Only Scan,而不是普通 Index Scan。
  2. Heap Fetches 是否很低。它明显偏高时,说明查询仍在频繁访问堆。
  3. 表是否长期稳定。更新越频繁,堆页的 all-visible 状态越难长期保持,覆盖索引的理论优势就越可能被实际堆访问抵消。

用执行计划验证是否值得保留

最终的验证标准不是“索引包含了查询列”,而是“索引覆盖 + 可见性条件”共同成立,并且实际执行成本因此下降。对一个线上索引候选,可以分别记录普通索引、覆盖索引和执行后的 Heap Fetches,再结合 BUFFERS 判断减少的堆访问是否值得承担更大的索引存储与写入维护成本。

因此,设计覆盖索引时应把 INCLUDE 看成一种可能实现 Index Only Scan 的结构设计,而不是性能保证。只有执行计划和运行数据都证明堆访问被显著减少,这个索引才真正达成了优化目标。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

验证索引仅扫描

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通