PHP小码农

Appearance

覆盖索引（INCLUDE） ​

B-Tree 索引只保存被索引列的值。当查询还需要返回没被索引的列时，数据库必须拿着索引里找到的物理地址（TID，Tuple Identifier）回到堆表（Heap Table）里取出整行——这一步俗称「回表」。一次查询命中几千行，就是几千次随机 I/O，索引带来的收益常常被回表抵消掉。

覆盖索引（Covering Index）的目标就是消灭回表：把查询需要的返回列一并塞进索引，让索引自己就能回答整个查询，这种执行方式叫仅索引扫描（Index-Only Scan）。PostgreSQL 11 起提供了 INCLUDE 子句，用「非键列（Non-Key Columns）」的形式优雅地表达覆盖列。本节讲清它背后的可见性映射（Visibility Map）机制、Heap Fetches 指标的含义、INCLUDE 与多列索引的本质区别，以及它的真实代价。

前置知识

阅读本节前，建议先了解：B-Tree 索引原理、多列索引、部分索引（Partial Index）

基础概念 ​

回表：索引无法避免的第二步 ​

假设 orders 表上有 (user_id) 索引，执行下面的查询：

sql
SELECT id, amount, created_at FROM orders WHERE user_id = 42;

索引能飞快定位到 user_id = 42 的所有条目，但索引条目里只有 user_id 加一个 TID。id、amount、created_at 都不在索引中，于是执行器只能：

text
第一步：在索引里找到 user_id = 42 的条目  →  拿到 TID 列表
第二步：按 TID 逐个访问堆表页面，取出整行 →  「回表」
第三步：从整行里筛出 id / amount / created_at

回表是随机 I/O。命中 5000 行、堆表又大于内存时，第二、三步的开销会远超第一步，优化器甚至可能因此放弃索引改用顺序扫描。

text
普通 B-Tree 索引：        [ user_id | TID ] ──回表──> 堆表整行
覆盖索引（INCLUDE）：      [ user_id | id | amount | created_at ] ──> 直接返回

Index-Only Scan 与 Visibility Map ​

「仅索引扫描」就是在索引里把查询从头到尾答完，不回表。但它有一个绕不开的麻烦：MVCC 可见性。

索引条目里没有行版本信息，优化器无法直接判断这个 TID 对应的元组对当前事务是否可见。PostgreSQL 用一个叫可见性映射（Visibility Map，VM）的旁路结构来解决：VM 为堆表的每个页面记录两个比特（bit）：

比特名称含义
bit 0all-visible（全可见）该页所有元组对所有事务都可见
bit 1all-frozen（全冻结）该页所有元组都已冻结，无需再 VACUUM

如果索引命中的 TID 落在 all-visible 的页面上，执行器就确信该元组可见，无需回表；否则必须回表确认。EXPLAIN (ANALYZE) 输出的 Heap Fetches: 行，统计的正是「被迫回表确认」的次数。

text
VM = 1 1 1 1 1 0 1 1 1 1 ...
     ↑ 这一页 all-visible → 索引命中的元组直接采信，不回表
                 ↑ 这一页脏 → 必须回表核对可见性

一句话总结：Heap Fetches: 0 才是理想的仅索引扫描；Heap Fetches 越大，你会越接近「带索引的回表扫描」。

INCLUDE：把载荷列放进索引 ​

PostgreSQL 11 引入了 INCLUDE 子句。它把索引列分成两类：

类别英文作用参与排序/搜索参与唯一性判定可被仅索引扫描返回
键列Key Columns索引的搜索维度是是是
非键列Non-Key Columns（INCLUDE 列）纯粹的载荷（payload）否否是

它解决了多列索引的一个尴尬：为了覆盖返回列而把 amount 加进键里，会让索引的排序维度、唯一性语义、体积全部变形。INCLUDE 让「载荷」和「键」彻底分开：

sql
-- 多列索引：amount 成为搜索维度的一部分，影响排序与体积
CREATE INDEX idx_orders_user_amount ON orders (user_id, amount);

-- 覆盖索引：amount 只是载荷，不参与搜索与排序
CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (amount);

INCLUDE 的索引方法支持范围

INCLUDE 最初（PostgreSQL 11）只支持 B-Tree，PostgreSQL 12 起扩展到 GiST 与 SP-GiST。Hash、GIN、BRIN 不支持 INCLUDE，这些方法上写 INCLUDE 会直接报语法/语义错误。

语法与用法 ​

text
-- 基本形式：键列在前，INCLUDE 列在后
CREATE [ UNIQUE ] INDEX 索引名 ON 表名 (键列 [, ...] ) INCLUDE (非键列 [, ...]);

-- 可以与部分索引的 WHERE 谓词共存（顺序：INCLUDE 在 WHERE 之前）
CREATE INDEX 索引名 ON 表名 (键列) INCLUDE (载荷列) WHERE 谓词;

-- 可以与表达式键列共存
CREATE INDEX 索引名 ON 表名 (lower(email)) INCLUDE (id, nickname);

-- 非键列可以有多个（受总列数上限约束）
CREATE INDEX 索引名 ON 表名 (a, b) INCLUDE (c, d, e);

-- 唯一索引 / 主键也可以用 INCLUDE（非键列不参与唯一性判定）
CREATE UNIQUE INDEX 索引名 ON 表名 (tenant_id, slug) INCLUDE (title, updated_at);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (id) INCLUDE (created_at);

-- 在线创建覆盖索引
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (user_id) INCLUDE (amount);

-- 反向：不能用 INCLUDE 做降序、排序等语义（那是键列的事）
-- 错误！INCLUDE 列不能指定排序方向或操作符类
CREATE INDEX idx ON t (a) INCLUDE (b DESC);

最小可运行示例：

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz   NOT NULL DEFAULT now()
);

-- 只把 user_id 当键，把查询常返回的列放进 INCLUDE
CREATE INDEX idx_orders_user_cover
  ON orders (user_id) INCLUDE (id, amount, created_at);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

Index-Only Scan 的两个基本前提 ​

优化器要走仅索引扫描，必须同时满足两个条件，缺一不可：

  1. 索引类型支持：索引必须能把查询引用到的列原值返回。B-Tree 总是支持（它存储完整的键值副本）；GiST、SP-GiST 只对部分操作符类支持；GIN、BRIN、Hash 从不支持——GIN 存的是元素/倒排项，BRIN 存的是块范围摘要，都拿不回原始列值。
  2. 查询只引用索引中存在的列：SELECT 列表、WHERE、ORDER BY 里出现的每一列，都必须能由索引本身提供。
sql
-- 索引：ON orders (user_id) INCLUDE (amount)
-- 可以走 Index Only Scan：三列都在索引里
SELECT user_id, amount FROM orders WHERE user_id = 42;

-- 只能走 Index Scan（回表）：created_at 不在索引里
SELECT user_id, amount, created_at FROM orders WHERE user_id = 42;

注意 WHERE 里出现但不在索引里的列，同样会破坏仅索引扫描——因为过滤需要读取该列的值。

Visibility Map 与 Heap Fetches ​

即使两个前提都满足，是否真的不回表仍取决于 VM。这是最容易误解的一点：INCLUDE 列并不会让索引「自带可见性信息」。

VM 的规则很严格：

事实说明
VM 只由 VACUUM 设置VACUUM（含 autovacuum）扫描后把干净页面标为 all-visible
任何数据修改都会清除对应页的 VM 位INSERT / UPDATE / DELETE 之后该页就不是 all-visible 了
索引本身没有 VMVM 是堆表的附属文件（_vm 后缀），索引不记录可见性
只有 VACUUM FREEZE 才设置 all-frozen常规 VACUUM 只设 all-visible
text
堆表文件：  base/16384/16400           ← 数据页
可见性映射：base/16384/16400_vm        ← 每页两个 bit（VM fork）

看一个计划输出：

text
Index Only Scan using idx_orders_user_cover on orders  (cost=0.42..8.45 rows=1 width=40) (actual time=0.021..0.024 rows=1 loops=1)
  Index Cond: (user_id = 42)
  Heap Fetches: 0
Planning Time: 0.120 ms
Execution Time: 0.048 ms

Heap Fetches: 0 表示所有命中的元组都落在 all-visible 页面上，完全没有回表。如果刚做过大批量写入，同一查询可能变成：

text
Index Only Scan using idx_orders_user_cover on orders  (cost=0.42..8.45 rows=1 width=40) (actual time=0.031..0.140 rows=1 loops=1)
  Index Cond: (user_id = 42)
  Heap Fetches: 1287

节点名依然是 Index Only Scan，但 Heap Fetches: 1287 说明它实际上回表了 1287 次。判断覆盖索引是否真的生效，看的是 Heap Fetches，不是节点名。

可以直接查询 VM 的状态：

sql
-- relallvisible：VM 中标记为 all-visible 的页数
SELECT relname, relpages, relallvisible,
       round(100.0 * relallvisible / nullif(relpages, 0), 2) AS vmap_pct
FROM pg_class
WHERE relname = 'orders';

-- 更细：用 pg_visibility 扩展统计 all-visible / all-frozen 页数
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('orders');
text
 relname | relpages | relallvisible | vmap_pct
---------+----------+---------------+----------
 orders  |    12345 |         12345 |   100.00
(1 row)

 all_visible | all_frozen
-------------+------------
       12345 |          0
(1 row)

INCLUDE 的语法与非键列语义 ​

INCLUDE 子句的位置固定：紧跟在键列列表之后、WHERE 谓词之前。

text
CREATE [ UNIQUE ] INDEX name ON table (key_1, key_2) INCLUDE (payload_1, payload_2) WHERE pred;
                                                   └── 必须在这里 ──┘

非键列的语义（官方定义）：

  • 出现在 INCLUDE 里的列在索引中物理存储，因此仅索引扫描可以返回它们；
  • 它们不能用于索引扫描的搜索条件（即不能写进 WHERE 去走索引查找）；
  • 它们不参与任何唯一性或排除约束的判定；
  • 它们不需要操作符类——因此连索引方法本身不直接支持的数据类型也能放进 INCLUDE。
sql
-- B-Tree 没有 jsonb 的操作符类，jsonb 不能当键列……
-- 错误！data type jsonb has no default operator class for access method "btree"
CREATE INDEX idx_bad ON events (payload);

-- ……但可以当 INCLUDE 列（非键列不需要操作符类）
CREATE INDEX idx_events_cover ON events (id) INCLUDE (payload);

非键列也不需要指定排序方向、NULLS FIRST/LAST 或操作符类，写了就是语法错误。

非键列的三个「不」 ​

把这三点单独拎出来，因为它们决定了什么时候不该用 INCLUDE。

不参与搜索。 下面这个查询用不到 amount 的索引加速，因为 amount 是非键列：

sql
-- 索引：ON orders (user_id) INCLUDE (amount)
-- amount 只是载荷，这个 WHERE 无法用它做索引查找
SELECT id FROM orders WHERE amount > 1000;

不参与唯一性。 唯一性只看键列：

sql
-- 唯一性由 (tenant_id, slug) 决定，title / updated_at 可以重复
CREATE UNIQUE INDEX uq_pages_slug
  ON pages (tenant_id, slug) INCLUDE (title, updated_at);

不参与排序。 ORDER BY 只能利用键列的排序结果：

sql
-- 索引：ON orders (user_id) INCLUDE (amount)
-- 可以走索引顺序：ORDER BY user_id
SELECT user_id, amount FROM orders ORDER BY user_id;

-- 不能：amount 是非键列，无法提供有序输出
SELECT user_id, amount FROM orders ORDER BY amount;

想清楚「这一列到底是被搜的，还是被返回的」

如果某列既要出现在 WHERE 的等值/范围条件里，又要出现在返回列表里，它应该是键列，而不是 INCLUDE 列。INCLUDE 只解决「返回」，不解决「查找」。

INCLUDE vs 多列索引 ​

两者都能让仅索引扫描返回更多列，但性质完全不同。考虑索引 (a) INCLUDE (b) 与 (a, b)：

维度(a) INCLUDE (b)(a, b)（多列键）
b 参与搜索否是
b 参与排序否是（可作为第二排序键）
b 参与唯一性否是
上层索引页是否存 b否（后缀截断）是
索引体积更小更大
适合b 只被返回b 被搜索或排序

后缀截断（Suffix Truncation）是关键区别：B-Tree 在构建上层（非叶子）页面时，只会保留区分度足够的最小前缀，非键列总是从上层页面中被移除。因此 INCLUDE 列不会放大索引的高层结构，索引更紧凑。

sql
-- 场景：只按 a 查找，同时要返回 b
-- 推荐：b 是纯载荷
CREATE INDEX idx_t_a_inc ON t (a) INCLUDE (b);

-- 场景：既要按 a 查找，也可能按 (a, b) 联合过滤或排序
-- 推荐：b 是搜索维度
CREATE INDEX idx_t_ab ON t (a, b);

还有一个容易忽略的点：INCLUDE 索引永远不会使用 B-Tree 去重（Deduplication）。去重是 PostgreSQL 13 引入的优化，能把大量重复的键合并存储；但只要索引带了 INCLUDE，去重就被彻底禁用——因为去重后的条目无法再承载各自不同的载荷列。

sql
-- 去重生效（无 INCLUDE，且重复键多）
CREATE INDEX idx_t_a ON t (a);

-- 错误认知！带了 INCLUDE 就不可能去重
CREATE INDEX idx_t_a_inc ON t (a) INCLUDE (b);

INCLUDE 的代价 ​

覆盖索引不是免费的。它在四个方面付出成本：

一、体积膨胀。 非键列在每一个索引条目里重复存储一次，等于把堆表的这些列复制了一份。

sql
SELECT indexrelid::regclass AS idx,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;
text
         idx          | index_size
----------------------+------------
 idx_orders_user_cover| 86 MB
 idx_orders_user      | 26 MB

二、破坏 HOT 更新。 堆元组优化（HOT，Heap-Only Tuple）要求 UPDATE 不修改任何被索引引用的列。INCLUDE 列是「被索引引用的列」，所以只要 UPDATE 改到了载荷列，就无法走 HOT，必须更新所有相关索引。

text
HOT 可用的条件：新元组落在同一页 + 被索引引用的列一个都没变
                 └─ 包括 INCLUDE 载荷列、部分索引谓词列、表达式索引依赖列

三、宽列风险。 索引元组有最大尺寸限制。B-Tree 版本 4 的上限是 2704 字节，超了会直接让插入失败：

sql
-- 错误！index row size 3120 exceeds btree version 4 maximum 2704 for index "idx_docs_cover"
-- DETAIL: Index row references tuple (0,1) in relation "docs".
-- HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
--        Consider a function index of an MD5 hash of the value, or use full text indexing.
CREATE INDEX idx_docs_cover ON docs (id) INCLUDE (content);

四、列数上限。 索引最多 32 列，这个上限包含 INCLUDE 列。键列 + 非键列的总数不能超过 32。

sql
-- 系统目录里可以看清「总列数」与「键列数」的区别
SELECT indexrelid::regclass AS idx,
       indnatts     AS total_attrs,   -- 总列数：键列 + INCLUDE 列
       indnkeyatts  AS key_attrs      -- 键列数：不含 INCLUDE 列
FROM pg_index
WHERE indrelid = 'orders'::regclass;
text
          idx          | total_attrs | key_attrs
-----------------------+-------------+-----------
 idx_orders_user_cover |           4 |         1
 idx_orders_user       |           1 |         1
(idx_orders_user_cover 的 4 = user_id + id + amount + created_at，key_attrs 只有 1)

表达式索引 + INCLUDE 的变通 ​

INCLUDE 列不能是表达式，官方明确说明：表达式既不能用于索引查找、也无法在仅索引扫描中重建，因此不支持。但这带来一个真实痛点：如果你想把 f(x) 作为键、同时又要返回 x 本身，该怎么办？

sql
-- 错误！expressions are not supported as included columns
CREATE INDEX idx_bad ON tab (lower(email)) INCLUDE (lower(email));

标准变通是把表达式依赖的原列放进 INCLUDE：

sql
-- 推荐：键是表达式，INCLUDE 放原列
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

-- 实际例子：按小写邮箱查，同时要返回原始大小写
CREATE INDEX idx_users_lower_email
  ON users (lower(email)) INCLUDE (email, id);
sql
SELECT id, email FROM users WHERE lower(email) = 'alice@example.com';
text
Index Only Scan using idx_users_lower_email on users  (cost=0.42..8.44 rows=1 width=36) (actual time=0.023..0.025 rows=1 loops=1)
  Index Cond: (lower(email) = 'alice@example.com'::text)
  Heap Fetches: 0

注意 INCLUDE (email) 里放的是原始列 email，不是表达式。这样索引条目里既有 lower(email) 的排序键，又有 email 的原值，仅索引扫描就能同时返回两者。

部分索引 + 覆盖索引 ​

INCLUDE 与部分索引的 WHERE 谓词可以叠加，这是生产环境里最常用的组合拳：用部分索引缩小索引规模，用 INCLUDE 消除回表。

sql
-- 只索引待处理订单，同时把常用返回列带上
CREATE INDEX idx_jobs_pending_cover
  ON jobs (created_at) INCLUDE (user_id, kind)
  WHERE status = 'pending';

这里有一个反直觉的点值得澄清：WHERE status = 'pending' 里的 status 并不在索引的键列或 INCLUDE 列中，但仅索引扫描依然可行。

原因是：部分索引的谓词在建索引时就已保证「索引里的每一行都满足 status = 'pending'」。执行器只要计划选择了这个部分索引，就无需对谓词部分重新做堆检查（这一点在 PostgreSQL 9.6 起被优化器正确识别）。因此 status 不需要出现在索引里。

text
部分索引 idx_jobs_pending_cover：
  条目 = [ created_at | user_id | kind ]   仅 status='pending' 的行
  谓词 status='pending' 由「索引定义」保证 → 无需回表 recheck
sql
-- 三列都能从索引直接返回；status 由谓词保证
SELECT created_at, user_id, kind FROM jobs WHERE status = 'pending' AND created_at >= now() - interval '1 day';
text
Index Only Scan using idx_jobs_pending_cover on jobs  (cost=0.29..12.60 rows=8 width=24) (actual time=0.028..0.041 rows=8 loops=1)
  Index Cond: (created_at >= (now() - '1 day'::interval))
  Heap Fetches: 0

什么时候用、什么时候别用 ​

覆盖索引的收益完全取决于「表够不够冷」和「查询形态够不够稳定」。官方文档对此有一句非常犀利的提醒：除非表的变化速度足够慢，否则往索引里加载荷列意义不大——因为页面刚被修改就丢了 VM 标记，Heap Fetches 会立刻飙升，你还是得回表。

情形是否适合 INCLUDE 覆盖理由
读多写少的字典/配置/报表表适合VM 长期保持 all-visible，Heap Fetches 稳定为 0
高频更新的事务表谨慎修改频繁清除 VM，载荷列还破坏 HOT
只需返回 1~2 个小列适合体积代价小，收益明确
需返回大文本 / 宽 JSON不建议索引急剧膨胀，可能触碰 2704 字节上限
列既要被搜索又要被返回用键列而非 INCLUDEINCLUDE 列无法参与搜索
JOIN 中需要返回连接表的少量列视情况见下

JOIN 场景要特别小心：INCLUDE 只能覆盖本索引所在表的列。如果查询 A JOIN B，即使 A 的索引覆盖了 A 的全部返回列，从 B 取列的那部分依然要访问 B。

sql
-- A 的索引能覆盖 a.*，但 b.name 仍需访问 B
SELECT a.id, a.amount, b.name
FROM orders a JOIN users b ON b.id = a.user_id
WHERE a.user_id = 42;

要让 JOIN 也走仅索引扫描，需要对 B 也建覆盖索引，且 B 的相关页面保持 all-visible。这是两件事，不要混为一谈。

实战示例 ​

下面用一张 100 万行的 orders 表，完整走一遍「发现问题 → 建覆盖索引 → 用 Heap Fetches 验证」。

第一步：建表并灌入数据。

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint        NOT NULL,
  status     text          NOT NULL DEFAULT 'pending',
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO orders (user_id, status, amount, created_at)
SELECT (random() * 50000)::bigint,
       (ARRAY['pending','paid','shipped','done'])[1 + (random() * 3)::int],
       round((random() * 2000)::numeric, 2),
       now() - (random() * 365 || ' days')::interval
FROM generate_series(1, 1000000);

ANALYZE orders;

第二步：先建一个普通单列索引，观察回表。

sql
CREATE INDEX idx_orders_user ON orders (user_id);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at FROM orders WHERE user_id = 1234;
text
Index Scan using idx_orders_user on orders  (cost=0.42..96.10 rows=20 width=40) (actual time=0.030..0.152 rows=21 loops=1)
  Index Cond: (user_id = 1234)
  Buffers: shared hit=26
Planning Time: 0.141 ms
Execution Time: 0.190 ms

节点是 Index Scan（不是 Index Only Scan），因为它必须回表取 id、amount、created_at。

第三步：建成覆盖索引，再看执行计划。

sql
CREATE INDEX CONCURRENTLY idx_orders_user_cover
  ON orders (user_id) INCLUDE (id, amount, created_at);

VACUUM (ANALYZE) orders;
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at FROM orders WHERE user_id = 1234;
text
Index Only Scan using idx_orders_user_cover on orders  (cost=0.42..4.90 rows=20 width=40) (actual time=0.029..0.041 rows=21 loops=1)
  Index Cond: (user_id = 1234)
  Heap Fetches: 0
  Buffers: shared hit=5
Planning Time: 0.135 ms
Execution Time: 0.058 ms

三个关键变化：

指标覆盖索引前覆盖索引后说明
节点类型Index ScanIndex Only Scan不再回表
共享缓冲区命中hit=26hit=5I/O 显著下降
Heap Fetches无此行0真正零回表

第四步：验证 VM 被破坏后的退化。 更新该用户的一批订单，并且改到 INCLUDE 列，然后立刻重跑同一个查询（不做 VACUUM）：

sql
UPDATE orders SET amount = amount + 1 WHERE user_id = 1234;

EXPLAIN (ANALYZE)
SELECT id, amount, created_at FROM orders WHERE user_id = 1234;
text
Index Only Scan using idx_orders_user_cover on orders  (cost=0.42..4.95 rows=20 width=40) (actual time=0.035..0.171 rows=21 loops=1)
  Index Cond: (user_id = 1234)
  Heap Fetches: 21

节点名仍是 Index Only Scan，但 Heap Fetches: 21 说明它把 21 行全部回表核对了一遍——覆盖索引的收益此刻归零。VACUUM orders 之后 VM 恢复，Heap Fetches 才会重新归零。

第五步：对比 INCLUDE 与多列索引的体积。

sql
CREATE INDEX idx_orders_user_amount ON orders (user_id, amount);

SELECT indexrelid::regclass AS idx,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders' AND indexrelid::regclass::text LIKE '%user%'
ORDER BY pg_relation_size(indexrelid) DESC;
text
         idx           |  size
-----------------------+---------
 idx_orders_user_cover | 78 MB
 idx_orders_user_amount| 54 MB
 idx_orders_user       | 26 MB

INCLUDE 版比多列版更大（多覆盖了两列），但它的键只有 user_id，不会因为 amount 的排序语义而误导优化器做一些无意义的「有序扫描」选择。

注意事项 ​

  • 别只看节点名，要看 Heap Fetches。 Index Only Scan 节点不保证真的不回表；Heap Fetches: 0 才是。

    sql
    -- 用 ANALYZE 而不是裸 EXPLAIN，才会出现 Heap Fetches 行
EXPLAIN (ANALYZE) SELECT id, amount FROM orders WHERE user_id = 42;

  • VM 只由 VACUUM 建立，任何写入都会清零对应页的标记。 刚批量写入、大批量 UPDATE 后，覆盖索引形同虚设，必须等 autovacuum 或手工 VACUUM 追上。

  • INCLUDE 列不能是表达式。 想覆盖表达式的结果，就把它的原列放进 INCLUDE。

    sql
    -- 错误！expressions are not supported as included columns
CREATE INDEX idx_bad ON tab (lower(email)) INCLUDE (lower(email));

  • INCLUDE 列不能参与搜索条件。 把要在 WHERE 里过滤的列放进 INCLUDE，是纯粹的浪费——它既不能加速过滤，还占体积。

    sql
    -- 不推荐：amount 想用来过滤，放 INCLUDE 就白建了
CREATE INDEX idx_bad ON orders (user_id) INCLUDE (amount);
SELECT * FROM orders WHERE user_id = 42 AND amount > 100;  -- amount 部分仍需回表过滤

  • INCLUDE 索引永远不会去重。 期待用去重压缩重复键的假想彻底落空；重复键多且载荷列小的时候，多列索引反而更省空间。

  • 宽列慎入 INCLUDE。 大文本、大 JSON 会让索引体积失控，还可能触发 index row size ... exceeds btree version 4 maximum 2704 导致写入失败（这是运行时报错，不是建索引时报错，往往在生产插入时才炸）。

    sql
    -- 错误！运行期插入大 content 时才会失败
CREATE INDEX idx_docs_cover ON docs (id) INCLUDE (content);

  • INCLUDE 列会破坏 HOT 更新。 UPDATE 一旦改到载荷列，就无法走 HOT，必须同步维护索引，写放大明显。

  • 注意列数上限 32（含 INCLUDE 列）。 键列 + 非键列总数超限，CREATE INDEX 直接失败。

  • Hash、GIN、BRIN 不支持 INCLUDE。 在这些方法上写 INCLUDE 会报错，覆盖这些索引类型需要另想办法（如把列并入键列，但受限）。

  • 索引只覆盖本表。 JOIN 中另一侧的表列仍需访问那张表，除非对那张表也建覆盖索引。

最佳实践 ​

  • 只把「稳定被返回、且很小」的列放进 INCLUDE。 一个 bigint 主键、一两个 numeric 或 timestamptz 通常是甜点区；大文本、宽 JSON 一律不进。

    sql
    CREATE INDEX idx_orders_user_cover
  ON orders (user_id) INCLUDE (id, amount, created_at);

  • 用 pg_index.indnkeyatts 与 indnatts 复核设计意图。 两者之差就是 INCLUDE 列数，一眼看出索引是不是被塞得太胖。

    sql
    SELECT indexrelid::regclass AS idx, indnkeyatts AS keys, indnatts AS total
FROM pg_index
WHERE indrelid = 'orders'::regclass;

  • 上线后用 Heap Fetches 验收，而不是只看节点名。 在真实负载下跑 EXPLAIN (ANALYZE, BUFFERS)，确认 Heap Fetches 接近 0。

    sql
    EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount FROM orders WHERE user_id = 42;

  • 覆盖索引 + 部分索引组合，把索引做到「又小又全」。 只索引热数据，同时带上常用的返回列。

    sql
    CREATE INDEX idx_jobs_pending_cover
  ON jobs (created_at) INCLUDE (user_id, kind) WHERE status = 'pending';

  • 别为了「顺手覆盖」而无脑加列。 每多一个 INCLUDE 列，就多一份写入放大和 HOT 破坏的风险；先用监控数据证明回表是真实瓶颈，再动手。

  • 高频写的表要给 autovacuum 更积极的策略。 VM 靠 VACUUM 恢复，覆盖索引在写密集表上要配合更频繁的 autovacuum 才有意义。

    sql
    ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_analyze_scale_factor = 0.01);

  • 优先用 INCLUDE 而非多列键来表达「纯载荷」。 这样索引的排序语义、唯一性语义保持干净，INCLUDE 列也不会污染上层的后缀截断。

  • 建覆盖索引走 CONCURRENTLY，建完必 ANALYZE。 大表上直接 CREATE INDEX 会长时间持写锁。

    sql
    CREATE INDEX CONCURRENTLY idx_orders_user_cover
  ON orders (user_id) INCLUDE (id, amount, created_at);
ANALYZE orders;

  • 定期复核覆盖索引的使用率与体积。 idx_scan = 0 或体积远大于收益的，列入清理清单。

    sql
    SELECT indexrelid::regclass AS idx,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

下一节

继续学习：降序索引

参考链接 ​