索引

把索引想成一本字典的目录：按某个键排好序，告诉你「这个词在第几页」。这个画面对 PG 大体成立，但缺了三处关键。其一，目录指向的「页码」是堆里的物理坐标 ctid，拿到坐标还得翻回正文（回表）；其二，不同形态的数据需要不同形态的目录——有序键用 B-tree，多值文档用倒排，大表的物理顺序用块摘要；其三，目录本身不记录「这一版对谁可见」，可见性永远要回堆确认，除非可见性图替它担保。这一章把这三处补齐，并把索引接回前三章铺好的物理地基。

§1B-tree：默认索引，管有序与等值范围

叶子节点按键有序、双向链相连；索引项是 (key, ctid)，命中后拿 ctid 回堆取整行。

B-tree 是 CREATE INDEX 不指定类型时的默认访问方法。它的形状是经典的：一个 root 页，若干层 internal（中间）页负责导航，最底层 leaf（叶子）页存实际的索引项。每个 leaf 页通过 special space（第 1 章 §2 提过的页尾保留区）里的左右兄弟指针，与相邻叶子双向相连——这条叶子链是范围扫描和 ORDER BY 不必排序就能顺序输出的物理基础。

底层机制（比文档深一层）。 关键在索引项的内容：一个 leaf 项 = (键值, ctid)，ctid 正是第 1 章 隐藏系统列里那个物理坐标 (block, offset)。索引本身不存整行，只存「键 + 这行在堆里的哪个槽」。于是一次索引查找分两步：先在 B-tree 里按键定位到 leaf 项拿到 ctid，再拿 ctid 回堆把整行读出来——这一步在执行计划里就是 Index Scan 节点伴随的堆访问。回表是索引的常态成本；唯一免掉它的路径是 §6 的 index-only scan。理解了「索引项指向 ctid、命中要回堆」，就理解了 PG 索引的全部基本盘：它是一层「键 → 物理位置」的映射，而非数据本身的另一份拷贝。

另一个值得记住的细节：B-tree 项里既然带 ctid，那么同一行的不同版本（第 1 章那些并存的 tuple）在索引里是不同的项——每个版本各有自己的 ctid。这正是「在频繁更新的列上多建索引会加剧膨胀」的根源，留到 §8 算这笔账。

§2访问方法概览：按数据形态选索引

B-tree 之外，PG 还内置 Hash、GIN、GiST、SP-GiST、BRIN——选哪个取决于数据的形态，不取决于表多大。

PG 内置的索引访问方法及其主战场：

下面三节按这棵决策树，逐一拆开 GIN、GiST、BRIN 的机制——B-tree 已在 §1 讲透，Hash 与 SP-GiST 留作扩展（前者多数场景被 B-tree 取代，后者是 GiST 的非平衡变体，适合四叉树 / trie 类的分区数据）。

§3GIN：把一行的多个值倒排出去

倒排索引——把一行里的每个键（jsonb 键值、数组元素、全文 lexeme）映射到包含它的所有行。

GIN 的结构是倒排表：把每一行拆成它包含的多个键（element），为每个键维护一个「含有该键的行的 ctid 列表」（posting list / posting tree）。查询 tags @> ARRAY['a','b'] 时，GIN 分别取 'a' 和 'b' 的 ctid 列表，求交集，得到同时含两者的行。它的主战场：

• jsonb 包含：data @> '{"status":"active"}'——jsonb 默认的 GIN opclass 把每个键和值都拆成可检索的 element。
• 数组包含：tags @> ARRAY['x']、tags && ARRAY['x','y']（重叠）。
• 全文检索：to_tsvector(body) @@ to_tsquery('postgres & index')——每个 lexeme 是一个 element。

底层机制（比文档深一层）。 GIN 的代价集中在写入维护。一次 INSERT 一行含 30 个键的 jsonb，意味着要往 30 个不同 element 的 posting list 里各插一个 ctid——若每次都即时改这 30 处，写入会非常昂贵。GIN 用 fastupdate 机制缓冲：新增项先追加进一个临时的pending list（顺序写、便宜），积累到阈值或 VACUUM/autovacuum 触发时，再批量合并进正式的倒排结构。代价是查询时若 pending list 非空，得额外扫一遍它来补齐结果。所以 GIN 的性格是「读极强、写要摊销」：适合读多写少、或能容忍批量合并延迟的多值检索；对写入极频繁的多值列，要权衡 fastupdate 与合并开销。

§4GiST：几何、范围与最近邻的通用框架

一棵可定制的平衡树框架——内部节点存子树的「包围摘要」，支持几何相交、范围重叠、按距离取最近邻。

GiST 不是某一种具体索引，而是一个框架：实现一组接口（如何求两个摘要的「联合」、如何判断查询与摘要是否「一致」、如何度量「距离」），就能把它特化成针对某种数据类型的索引。内置和扩展提供了几类常用特化：

• 几何与空间：point、box、polygon 的相交 / 包含查询；PostGIS 的空间索引底层正是 GiST。
• 范围类型：tstzrange、int4range 的重叠 &&、包含 @>，常配排他约束（EXCLUDE）防止时间段重叠。
• 最近邻（KNN）：ORDER BY 列 <-> 目标点 LIMIT k——GiST 能按距离有序地吐出最近的若干行，避免「先全表算距离再排序」。

底层机制（比文档深一层）。 KNN 是 GiST 区别于其它索引最独特的能力。它把「距离」做成索引可理解的度量函数，于是查询执行时维护一个按「节点到目标的距离下界」排序的优先队列，每次取出队首子树展开——保证先返回的就是最近的。这让 ORDER BY 距离 LIMIT k 能在拿到 k 个结果后立即停止，而非扫全表。SP-GiST 是 GiST 的近亲，把「平衡树」换成「空间分区」（非平衡，按四叉树 / k-d 树 / trie 切分空间），适合点数据、IP 前缀、文本前缀这类天然分区的形态——一句话带过即可，机制与 GiST 同源。

§5BRIN：为物理有序的大表存块摘要

只为每段连续的 block range 存一个 min/max 摘要，索引体积极小——前提是物理顺序与列值高度相关。

BRIN 把表的 block 切成固定大小的 block range（默认每段 128 个 page，由 pages_per_range 控制），每段只存该段内目标列的摘要——对默认的 minmax opclass，就是 min 和 max 两个值。查询 WHERE created_at BETWEEN ... AND ... 时，BRIN 扫一遍这些极小的摘要：某段的 [min, max] 与查询区间无交集，整段 block 直接跳过；有交集，则该段所有 block 进入候选。

底层机制（比文档深一层）。 BRIN 与 B-tree 有一个本质区别：它不定位具体行，只排除整段 block range。摘要 [min, max] 说「这段与查询范围有交集」，但段内具体哪些行命中、哪些是噪声，BRIN 一无所知——它是 lossy（有损）的。因此 BRIN 扫描的产物不是行，而是一张「候选 block 的位图」，必然接一个 bitmap heap scan：按位图把候选 block 整段读进来，再对每一行重新核对谓词，剔除不满足的。这条「BRIN → bitmap heap scan 二次校验」的链路是 BRIN 计划的固定形态，下一章 §bitmap 会从执行器视角再讲一遍。

由此也推出 BRIN 的适用红线：物理顺序与列值相关性越高，每段的 [min, max] 区间越窄、越不重叠，排除越干净。若相关性差（比如在一个随机乱序的列上建 BRIN），几乎每段的区间都横跨整个值域、彼此重叠，结果是「哪段都排除不掉」，BRIN 退化成几乎全表扫描。所以 BRIN 不是「大表的省空间索引」，而是「物理有序的大表的省空间索引」——这正是图 4.2 决策树把它放在「超大表 + 物理有序」分支的原因。

§6部分索引、表达式索引、覆盖索引

三种正交的裁剪手段——只索引一部分行、索引一个计算结果、把额外列塞进索引叶子。

部分索引（partial index）

在 CREATE INDEX 上加 WHERE，只为满足条件的行建索引项：

CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

若业务里 99% 的查询只关心 status='active' 的订单，而历史订单大多是 'archived'，这个索引就只为那 1% 的活跃行建项——索引体积、维护成本随之骤降，且只在查询条件能蕴含该 WHERE 时才被规划器选用。典型用法还包括「只索引 NOT NULL 的行」「只索引未删除的行」。

表达式索引（expression index）

索引一个计算结果而非原始列。最常见的是大小写无关查找：

CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 这样下面这条才能走索引（谓词左侧必须与索引表达式完全一致）：
SELECT * FROM users WHERE lower(email) = 'a@b.com';

普通的 email 索引帮不了 lower(email) = ...，因为索引里存的是原值、查询要的是函数值，两者对不上。表达式索引直接把 lower(email) 的结果建进 B-tree，谓词左侧与索引表达式逐字一致时即可命中。代价是写入时要为每行计算一次该表达式。

覆盖索引（covering index / INCLUDE）

用 INCLUDE 把查询要返回、但不参与过滤排序的额外列塞进索引叶子：

CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (status, total);

INCLUDE 里的列只存在 leaf 项里，不参与 B-tree 的排序与导航（不进 internal 页）。它的唯一目的，是让「按 customer_id 查、只取 status 和 total」这类查询所需的列全在索引里，从而满足下一节 index-only scan 的前提——把本该回堆取的那几列，预先放进索引叶子。这是把 §7 的优化「喂」给规划器的主要手段。

§7Index-only scan：靠可见性图省掉回表

查询所需列都在索引里、且目标页在可见性图标为 all-visible 时，不回堆——这一步省略由 VACUUM 维护的可见性图担保。

index-only scan 的成立需要两个条件同时满足：

• 列覆盖：查询引用的所有列，都能从索引项里取到（包括 INCLUDE 进去的列）。少一列要回堆，就退化成普通 Index Scan。
• 页可见：索引项指向的堆页，在可见性图里被标为 all-visible（整页所有 tuple 对所有事务都可见）。

底层机制（比文档深一层）。 核心矛盾在于：索引项不携带可见性信息。一个 leaf 项只有 (key, ctid)，没有 xmin/xmax——它无从判断这一版是不是死元组、是否对当前快照可见。所以正常的 Index Scan 必须回堆，读出 tuple 头部的 xmin/xmax 才能定可见性。第 3 章的可见性图给了一条捷径：当某个堆页被标记为 all-visible，意味着「这一整页的所有 tuple 都已对所有事务可见、无任何待清理的死元组」——此时索引项指向的那行必然可见，可见性确认这一步可以跳过，于是不必回堆。

这条因果链直接推出 index-only scan 的脆弱性：可见性图由 VACUUM 维护（见第 3 章），而任何对页的写入（INSERT/UPDATE/DELETE）都会清掉该页的 all-visible 标记。因此一张刚刚大量写入、还没来得及 VACUUM 的表，可见性图里几乎没有 all-visible 页，index-only scan 会大面积退化成普通 Index Scan（每行都回堆确认）。这解释了一个常见现象：同一条查询，在数据稳定（已 vacuum）的表上走 Index Only Scan 飞快，刚灌完数据时却变成 Index Scan 慢一截。结论：index-only scan 不是「建了覆盖索引就永久生效」的静态优化，它依赖 VACUUM 把页维护成 all-visible——这是第 3 章与本章在主线上的直接咬合点。

§8索引与 HOT 的权衡：索引不是越多越好

每加一个索引，被它覆盖的列上的更新就无法走 HOT——写放大加索引膨胀，这笔账要算。

回顾第 2 章的 HOT（Heap-Only Tuple）：当一次 UPDATE 满足「新版本能落在同一个 Page」且「没有改动任何被索引的列」两个条件时，PG 走 HOT 更新——新版本通过页内 line pointer 的重定向链挂在旧槽后面，所有索引都无需更新，因为索引项指向的旧 line pointer 槽顺着重定向就能找到新版本。HOT 是 PG 缓解「更新即新增版本」开销的核心手段。

底层机制（比文档深一层）。 HOT 的触发条件里，「没改任何被索引的列」是与索引直接挂钩的那一项。这意味着：一个列一旦被某个索引覆盖，对它的 UPDATE 就再也不能走 HOT——因为索引项是 (key, ctid)，键变了或新版本要新的 ctid，索引必须同步插入新项、标记旧项失效。后果是双重的：

• 写放大：本可一次 HOT 解决的更新，现在要额外维护每一个覆盖该列的索引——每个索引各写一个新 leaf 项。索引越多、被更新的列被越多索引覆盖，单次 UPDATE 的写入越重。
• 索引膨胀：旧版本的索引项不会立即消失，要等 VACUUM 回收（与第 3 章死元组回收同源）。频繁更新被索引的列，索引里堆积大量指向死元组的旧项，索引随之膨胀、查询要扫的页变多。

结论：索引不是越多越好。在一张频繁更新的表上，每加一个索引都要问两件事——它覆盖的列会被更新吗？如果会，这次更新就从「一次 HOT」变成「一次堆写 + N 个索引维护 + N 份待 VACUUM 的索引垃圾」。理想的布局是：把高频更新的列排除在索引之外，让这些更新尽量走 HOT；只在真正承担过滤 / 排序的列上建索引。这是「读收益」与「写代价 + HOT 机会成本」之间的工程权衡，没有放之四海的数字，但算账的框架就是这条。

§9动手观察：从 Index Scan 到 Index Only Scan 到 GIN

把 §1、§6、§7、§3 拧成一条可观察的实验线：建表灌数据 → 建 B-tree 看 Index Scan → 加 INCLUDE 看 Index Only Scan → 在 jsonb 上建 GIN 看 @> 走 Bitmap Index Scan。下面的 EXPLAIN 输出未在本机执行（具体行数、代价因实例与数据分布而异），用于演示该看计划里的哪个节点、它对应本章哪个概念。

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id int,
  status      text,
  total       numeric,
  attrs       jsonb
);
-- 灌入一批数据后（此处省略），并 VACUUM 使可见性图就绪：
VACUUM ANALYZE orders;

-- (1) 普通 B-tree：命中后回堆取整行 → Index Scan
CREATE INDEX idx_cust ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
--  Index Scan using idx_cust on orders            <- 未在本机执行
--    Index Cond: (customer_id = 42)

-- (2) 覆盖索引：所需列全在索引里 + 页 all-visible → Index Only Scan
CREATE INDEX idx_cust_cover ON orders (customer_id) INCLUDE (status, total);
EXPLAIN SELECT customer_id, status, total FROM orders WHERE customer_id = 42;
--  Index Only Scan using idx_cust_cover on orders <- 未在本机执行
--    Index Cond: (customer_id = 42)
--    Heap Fetches: 0

-- (3) GIN on jsonb：@> 包含查询 → Bitmap Index Scan on GIN
CREATE INDEX idx_attrs ON orders USING gin (attrs);
EXPLAIN SELECT * FROM orders WHERE attrs @> '{"vip": true}';
--  Bitmap Heap Scan on orders                     <- 未在本机执行
--    Recheck Cond: (attrs @> '{"vip": true}')
--    ->  Bitmap Index Scan on idx_attrs
--          Index Cond: (attrs @> '{"vip": true}')

逐行解读，把每个计划节点映射回本章概念：

• (1) Index Scan对应 §1：B-tree 按 customer_id 定位到 leaf 项拿 ctid，因为 SELECT * 要全部列，必须回堆取整行。计划名 Index Scan 即「索引定位 + 回表」。
• (2) Index Only Scan对应 §7 + §6 覆盖索引：查询只要 customer_id/status/total，全被索引（键 + INCLUDE）覆盖；又因 VACUUM ANALYZE 已把页标为 all-visible，Heap Fetches: 0 证明一次堆都没回。这正是可见性图担保下的免回表。
• (3) Bitmap Index Scan on GIN对应 §3：GIN 对 @> 不产出有序的单行定位，而是吐出一张「命中行的位图」，故上层是 Bitmap Heap Scan、下层是 Bitmap Index Scan on idx_attrs。Recheck Cond 表示按位图取回堆页后还要逐行复核 @>（位图按页粒度有损）——bitmap 扫描的机制留到 第 5 章 §bitmap。