PostgreSQL INCLUDE 覆盖索引：用 Index Only Scan 消除回表

问题场景

有一张 users 表，业务最常用的查询是按 email 定位用户并返回 id 与 name：

SELECT id, name FROM users WHERE email = 'user500@example.com';

若只在 email 上建普通 B-tree 索引，索引项只包含 email 和指向堆表的 TID。执行器通过索引找到 email 匹配的 TID 后，还必须访问堆表取出 id 与 name。即使只返回两列，也产生随机堆访问；返回列越多，这种“回表”成本越明显。

覆盖索引的思路是：让索引本身包含查询需要的所有列，使执行器无需访问堆表即可完成查询。PostgreSQL 11 引入的 INCLUDE 子句，允许在索引中声明“非键列”，专门用于这种投影覆盖。

INCLUDE 与键列的区别

CREATE INDEX ON users (email) INCLUDE (id, name) 中：

• email 是键列，参与 B-tree 排序、等值/范围条件匹配。
• id、name 是包含列，只存放在索引叶子元组中，不参与排序、不参与键条件匹配，也不参与唯一性判断。

这与 CREATE INDEX ON users (email, id, name) 不同。后者会把 id、name 也变成键列，改变索引可支持的排序和前缀匹配方式，索引结构也不同。若查询只需要 WHERE email = ... 并返回 id、name，用 INCLUDE 更贴近意图。

最小可运行示例

以下 SQL 在 PostgreSQL 11 及以上版本可执行。占位值请按需调整。

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id         bigserial PRIMARY KEY,
    email      text NOT NULL,
    name       text,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (email, name)
SELECT
    'user' || g || '@example.com',
    'user-' || g
FROM generate_series(1, 10000) AS g;

CREATE INDEX idx_users_email_include
    ON users (email) INCLUDE (id, name);

VACUUM (ANALYZE) users;

运行方法：用 psql 连接目标数据库后，将上述内容保存为 setup.sql，执行：

psql -h <host> -U <user> -d <dbname> -f setup.sql

然后查看执行计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM users
WHERE email = 'user500@example.com';

若使用覆盖索引且可见性映射允许，计划中会出现 Index Only Scan using idx_users_email_include，并显示 Heap Fetches。示意如下（实际数值随版本、数据分布和缓存状态变化）：

Index Only Scan using idx_users_email_include on users
  Index Cond: (email = 'user500@example.com'::text)
  Heap Fetches: 0

Heap Fetches: 0 表示本次执行没有访问堆表，索引已经覆盖了查询所需列。若该值大于 0，说明执行器仍需回堆检查行可见性；执行 VACUUM (ANALYZE) users; 后再看，通常可以减少或归零，但这取决于表上后续是否有未清理的变更。

作为对照，可以删除覆盖索引并建立普通索引：

DROP INDEX idx_users_email_include;
CREATE INDEX idx_users_email ON users (email);
VACUUM (ANALYZE) users;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM users
WHERE email = 'user500@example.com';

此时通常看到 Index Scan using idx_users_email，并伴随堆表访问。若表很小，规划器也可能直接选择 Seq Scan，这属于正常代价选择；可以用 SET enable_seqscan = off; 在会话内临时观察索引路径，验证完记得 RESET enable_seqscan;。

边界与限制

1. 版本要求：INCLUDE 子句从 PostgreSQL 11 开始支持；更早版本只能用多列索引模拟覆盖。
2. Index Only Scan 不等于绝对不回表：PostgreSQL 需要借助 visibility map 判断索引元组对应堆行是否可见。若页面不够“全可见”，仍会产生 Heap Fetches。VACUUM 与 autovacuum 的状态会影响这一点。
3. 包含列不参与条件与排序：不能指望 INCLUDE 列用于 WHERE 过滤或 ORDER BY；这些能力属于键列。
4. 唯一性只看键列：CREATE UNIQUE INDEX ... (email) INCLUDE (name) 的唯一性仍由 email 决定，name 不参与去重判断。
5. 写放大与索引膨胀：包含列会存入索引叶子，增加索引体积；更新包含列也必须维护索引，可能影响写入吞吐。不要把长文本、大 JSON 等宽列随意放进 INCLUDE。
6. 索引元组大小限制：单个索引元组不能超过页面约三分之一，包含过大的值可能导致创建失败或插入失败。覆盖索引适合短小、稳定的投影列。
7. 不是所有查询都适合覆盖索引：若查询返回大量列、访问大范围数据，顺序扫描可能更合适；覆盖索引更适合高频、点查、返回列少且固定的查询。

小结

INCLUDE 让 PostgreSQL 可以用更明确的语法声明“只用于覆盖、不作为键”的列。对 WHERE email = ... 返回 id, name 这类查询，CREATE INDEX ON users (email) INCLUDE (id, name) 配合 EXPLAIN (ANALYZE, BUFFERS) 观察 Index Only Scan 与 Heap Fetches，是较低成本的优化手段。使用前应结合查询模式、列宽、写入频率和可见性维护策略评估，不要为了覆盖而盲目加宽索引。