BodySense 数据库第 7 课：PostgreSQL Index——B-Tree、联合、Partial、Covering、GIN 与 BRIN

系统学习 PostgreSQL 索引设计，从 B-Tree 和联合索引到 partial、covering、expression、GIN/BRIN，并用 BodySense 查询模式验证收益和写放大成本。

#course #growing #backend #database #postgresql #bodysense

[!info] related notes

课程 MOC：BodySense 数据库工程深化课程 MOC

前置：第 6 课

知识：B+Tree 数据库索引、联合索引与覆盖索引

BodySense 数据库第 7 课：PostgreSQL Index——B-Tree、联合、Partial、Covering、GIN 与 BRIN

[!abstract] 本课目标把“给常查字段加索引”升级成：根据 query predicate、排序、选择性、写入成本和数据分布设计访问路径。

从零建立概念：Index 本质上是额外维护的一条访问路径

没有 Index 时，最通用的方法是：

从 Table 的 Heap pages
一页一页检查
直到找出满足 predicate 的 rows

Index 的核心思想是：

额外保存一份更适合查找的数据结构，让数据库不必每次扫描全部 Heap。

但注意关键词：额外保存。

每个 Index 都带来：

extra disk space
INSERT maintenance
UPDATE maintenance
DELETE cleanup
WAL
VACUUM work
cache footprint

所以索引设计是读写 trade-off，不是“越多越好”。

B-Tree 从零理解

PostgreSQL 默认最常见的 Index 是 B-Tree。

可以先把它想成一棵高度较低、每个节点能放很多 key 的平衡搜索树：

           [root]
          /      \
     [internal] [internal]
       / ...       \ ...
   [leaf pages containing ordered keys + row references]

为什么数据库常用 B-Tree/B+Tree 家族，而不是普通二叉树？

因为数据库访问成本高度受 page I/O 影响。

一个 tree node 可以和一个 page 高度匹配，单个节点包含很多 key：

branching factor 很大
→ tree height 很小
→ 少量 page traversal 就到 leaf

B-Tree 擅长什么

由于 key 有顺序：

=
< > <= >=
BETWEEN
prefix/range
ORDER BY
MIN/MAX 某些场景

都很自然。

Index Entry 与 Heap Row 的关系

普通 PostgreSQL B-Tree Index 不是“把整行搬进去”。

核心 key entry 会定位 heap tuple。

于是普通 Index Scan 大致：

search index pages
→ get tuple locations
→ visit heap pages
→ check visibility / fetch columns

这一步“回 Heap”为什么重要？

因为如果结果非常多且分散：

大量 index traversal
+ 大量 heap page random fetch

可能比直接 Seq Scan 更贵。

Selectivity：Index 有没有机会真正缩小数据范围

假设 100 万行：

WHERE id = unique UUID
→ 预计 1 row

选择性非常高，Index 很有价值。

另一个：

WHERE status = 'active'
→ 90 万 rows

普通 status index 可能没有太大价值，因为最终仍要读表的大部分数据。

Cardinality / distinct values 和 data distribution 会影响 selectivity。

所以：

“WHERE 里出现的字段就加 Index”是错误口诀。

Composite / Multicolumn Index 为什么有顺序

假设：

WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20

候选：

CREATE INDEX ... ON conversations(user_id, created_at DESC);

B-Tree 逻辑顺序近似：

先按 user_id 分组排序
在同 user_id 内按 created_at 排序

于是可以快速定位：

user_id = u1 的范围
→ 直接按时间顺序取前 20

PostgreSQL 18 还增强了 multicolumn B-Tree 的 skip scan 能力，使某些缺少 leading-column equality 的查询也可能利用后续列；但这不是取消列顺序的重要性。是否使用仍由 Planner 根据 distinct values 和 cost 决定。

Index 与 ORDER BY

B-Tree 可以 forward/backward scan。

单列：

index x ASC
→ 可支持 x ASC
→ backward scan 可支持 x DESC

多列混合顺序时：

(x ASC, y DESC)

可能专门服务某种常用 order。

是否值得维护要看查询频率，不应为了每一种排序都建 Index。

Partial Index：只给真正需要的子集建索引

如果表里：

95% jobs = completed
5% jobs  = pending/running

Worker 主要查 pending：

CREATE INDEX ...
ON jobs(created_at)
WHERE status = 'pending';

Index 只维护少量热点 rows。

好处：

smaller index
less write maintenance
higher useful selectivity

关键限制：

Query predicate 必须让 Planner 能证明它满足这个 Partial Index 的 predicate。

Covering / INCLUDE 与 Index-Only Scan

如果 query 只需要：

index key
+
几个额外返回列

可以用 INCLUDE 把非搜索 key 带进 Index。

例如：

CREATE INDEX ...
ON conversations(user_id, created_at DESC)
INCLUDE (title);

这样某些条件下可以 Index-Only Scan。

但名字容易让人误解：

有 INCLUDE 不保证永远不访问 Heap。

PostgreSQL 还要依据 visibility map 判断相关 heap page 是否 all-visible；否则仍需 heap fetch 检查 MVCC visibility。

Expression Index

如果 query 经常：

WHERE lower(email) = lower($1)

普通 email Index 不一定直接服务函数结果。

可以建立：

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

这叫 Expression Index。

但如果业务真正要求 email case-insensitive unique，还要进一步考虑 collation、citext、表达式 unique index 等完整语义。

GIN：反向索引式的多值查询

GIN 常见于：

JSONB
arrays
full-text
多值 membership

可以形成：

term / contained value
→ which rows contain it

它不是 B-Tree 的“升级版”，而是针对不同 operator/query model 的 Access Method。

BRIN：超大、物理顺序高度相关数据的轻量摘要

BRIN 不为每条 row 建精细索引 entry。

它按 page range 保存摘要：

page range 0..127
→ min timestamp / max timestamp

对：

append-only
时间随物理位置递增
超大历史表

可能非常小而有效。

但数据分布随机时，range 摘要过滤能力就弱。

Bitmap Scan：多个 Index 也可以组合

PostgreSQL 可以：

Index A → bitmap
Index B → bitmap
AND / OR
→ visit heap pages

这解释为什么：

“有两个单列索引”有时能服务两个条件，但也不等于总是优于一个合适的联合索引。

Planner 会比较成本。

1. B-Tree 是默认主力

适合：

=
< > BETWEEN
ORDER BY
prefix/range scan

索引减少的是需要检查的 tuple/page 范围，但每个索引都会增加 INSERT/UPDATE/DELETE 的维护成本和磁盘空间。

2. Multicolumn Index

例如常见查询：

WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20

候选索引通常比两个独立索引更贴合访问模式：

(user_id, created_at DESC)

列顺序取决于真实 predicate / ordering，而不是固定口诀。

3. Partial Index

如果多数历史行不再被主路径读取：

CREATE INDEX ... WHERE lifecycle_state = 'active';

可以只索引热点子集，但查询 predicate 必须能匹配该条件。

4. Covering / INCLUDE

INCLUDE 可以让索引携带非 key 列，从而在 visibility 条件满足时支持 index-only scan。它不是免费午餐：索引更大、写成本更高。

5. 其他 PostgreSQL 索引

GIN  → JSONB、array、全文等 inverted index 场景
GiST → 多种可扩展搜索结构
BRIN → 超大且物理值相关性强的数据，如时间递增日志
Hash → equality 特定场景

BodySense 的 pgvector 又有自己的 ANN 索引语义，应与普通 B-Tree 分开理解。

Prediction

[!question]

给每一列都建索引为什么通常是坏主意？

查询 WHERE user_id=? ORDER BY created_at DESC LIMIT 20，单列 user_id 索引一定足够吗？

Partial Index 最大的优势和最大限制分别是什么？

有了 covering index 是否保证 index-only scan？

JSONB 字段为什么不应该默认用 B-Tree 解决所有内部 key 查询？

[!success]- Answer / 解析 1. 索引占空间并让每次写入额外维护，多索引还会增加 planner 选择和 vacuum 负担；应由查询模式驱动。

2. 不一定。 单列索引可缩小 user 范围，但仍可能额外 sort；联合 (user_id, created_at DESC) 更可能同时服务过滤与顺序。

3. 优势是索引更小、针对热点子集；限制是查询条件必须能被 planner 证明满足 partial predicate。

4. 不保证。 PostgreSQL 还需要 visibility map 等条件判断 heap tuple 是否对所有事务可见。

5. JSONB 内部查询常需要 GIN/operator class 等特定访问结构；B-Tree 更适合标量排序/equality/range。

Lab

为 BodySense 两个真实查询设计 3 组候选索引：单列、联合、partial。对每组记录 query plan、buffers、index size，并保留一个“看似合理但 planner 不用”的反例。

L4 Gate

能解释一个索引为什么对某条 SQL 有效，同时指出它对写入、磁盘、VACUUM 和其他查询的代价。