索引:给优化器一条更便宜的路径

3.1B-tree:默认索引,擅长什么

B-tree 是 PostgreSQL 的默认索引,服务一切"可比较"的查询:等值、范围、排序、前缀匹配。

B-tree 能服务的查询条件:

• 等值与范围:=、<、>、<=、>=、BETWEEN、IN (...)——这些都是"在有序键里定位一个点或一段区间"。
• 前缀匹配:LIKE 'abc%'(以及 ~ '^abc')能走 B-tree,因为前缀确定了叶子层里一段连续区间的起点。
• ORDER BY 免排序:查询的排序列与索引列(及方向)一致时,优化器直接读有序叶子,跳过显式 Sort 节点。

B-tree 用不上的场景:

• 后缀/中缀匹配:LIKE '%abc'、LIKE '%abc%'——通配符在前,无法确定有序键里的起点,只能逐行扫。这类查询要靠 §3.5 的 GIN(配 pg_trgm)。
• 被函数包裹的列:WHERE lower(email) = ? 对裸 email 的 B-tree 无效——索引存的是原值,不是 lower() 后的值。解法是 §3.4 的表达式索引。

一个走 B-tree 的计划,标志是 Index Cond 这一行——它表示条件被下推进了索引扫描,而不是取出行后再 Filter:

Index Scan using idx_orders_created on orders
      (cost=0.42..28.6 rows=120 width=84)
      (actual time=0.02..0.31 rows=118 loops=1)
  Index Cond: ((created_at >= '2026-05-01') AND (created_at < '2026-06-01'))

这是一个范围查询走 B-tree 的典型:Index Cond 里两个边界把扫描限定在叶子层的一段连续区间内,只读了这段区间对应的索引项,再回表取 118 行。对比第 01 章那个 Rows Removed by Filter: 999991 的顺序扫描,差别一目了然——条件进了 Index Cond,就没有海量行被扫出来再扔掉。

3.2多列索引与列序(含 skip scan 现状)

多列索引 (a, b, c) 的列序决定它能服务哪些查询;列序排错,索引就服务不了你以为它能服务的查询。

多列 B-tree 的叶子按"a 为主、b 次之、c 再次"的字典序排列。这个排序方式直接推出经典的最左前缀规则:索引只能服务"从最左列开始、连续使用"的条件前缀。

以 (a, b) 为例:

• WHERE a = ? —— 能用(用到最左列)。
• WHERE a = ? AND b = ? —— 能用(前缀完整)。
• WHERE b = ? —— 经典规则下用不上:缺了最左列 a,无法在按 a 排序的叶子里定位。

列序原则:等值列在前,范围列在后

当查询里既有等值又有范围,把等值列放在前面、范围列放在后面。原因还是叶子的字典序:等值条件把扫描收窄到"a 固定"的一段,在这一段里 b 仍是有序的,范围条件 b > ? 就能继续在这段内定位区间。反过来,(b, a) 且 b 是范围时,b > ? 跨越多个 b 值,每段里的 a 各自有序但整体无序,等值条件 a = ? 就无法靠索引收窄。

所以查询 WHERE a = ? AND b > ? 的最优索引是 (a, b),不是 (b, a)。这条原则在 §3.6 的挑战题里会再用到。

CURRENCY · PG 18 引入 B-tree skip scan(2025-09)

PostgreSQL 18(GA 于 2025-09-25)给 B-tree 加入了 skip scan。它改变了上面"缺最左列就用不上"的结论:对索引 (a, b),查询 WHERE b = ?(缺前导列 a)现在也有机会用上这个索引——优化器会遍历 a 的每一个 distinct 值,在每个值下用 b = ? 做一次子搜索,把多次子搜索拼起来。

3.3覆盖索引与 Index Only Scan

查询要的列全在索引里时,优化器可以只读索引、不回表(Index Only Scan)——前提是数据页被标记为 all-visible。

普通 Index Scan 分两步:走索引拿到行的物理位置(TID),再回表(到堆表对应页)取出整行。回表是随机读,正是第 02 章里"命中行一多就该用顺序扫描"的代价来源。如果查询需要的列恰好都在索引里,这一步回表就能省掉——这就是 Index Only Scan。

用 INCLUDE 把额外列搭进叶子

让索引覆盖更多列,有两种写法。一是直接把列加进索引键 (a, b),但这会让 b 参与排序和定位,有时并不需要。二是用 INCLUDE 子句:INCLUDE 的列只存进叶子节点(供取数、避免回表),不参与排序与查找。当某列只是要被 SELECT 出来、从不作为查询条件时,放进 INCLUDE 比放进索引键更省。

-- 查询:SELECT amount FROM orders WHERE customer_id = ?
-- customer_id 用于查找,amount 只是要取出来
CREATE INDEX idx_orders_cust_amt
  ON orders (customer_id) INCLUDE (amount);

建好后,上面那条查询就能走 Index Only Scan——customer_id 定位、amount 直接从叶子取,完全不碰堆表:

Index Only Scan using idx_orders_cust_amt on orders
      (cost=0.42..8.4 rows=9 width=8)
      (actual time=0.02..0.03 rows=9 loops=1)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

关键看 Heap Fetches: 0:0 次回表,整条查询只读了索引。这是 Index Only Scan 跑在理想状态的标志。

3.4部分索引与表达式索引

部分索引只索引"满足某条件的行",表达式索引索引"某个表达式的结果"——两者都是把索引精确对准查询。

部分索引:只索引你真正会查的行

部分索引在定义里带 WHERE,只为满足条件的行建索引项。它的好处是更小、写入更省:不满足条件的行根本不进索引,既省空间,这些行的增删改也不必维护索引项。

这正好接上第 02 章挑战题的悬念:status 有 shipped 60% / paid 25% / pending 12% / cancelled 3%,高频值走 Seq Scan、只有低频的 cancelled 适合走索引。但建一个完整的 status 单列索引,会为全部行(包括占 97% 的非 cancelled 行)建项,浪费且拖慢写入。更优解是只为 cancelled 行建部分索引:

-- 只索引低频值 cancelled 的行;索引体积≈全表的 3%
CREATE INDEX idx_orders_cancelled
  ON orders (created_at)
  WHERE status = 'cancelled';

-- 这条查询会走上面的部分索引
SELECT * FROM orders
WHERE status = 'cancelled' AND created_at >= '2026-05-01';

优化器识别出查询条件 status = 'cancelled' 与索引的 WHERE 谓词匹配,就会用这个只含 3% 行的小索引,既快又几乎不增加写入负担。这是"部分索引专门服务低频值查询"的标准用法。

表达式索引:让函数包裹的列也能走索引

§3.1 提过,WHERE lower(email) = ? 用不上 email 的普通 B-tree——索引存的是原值,不是 lower() 后的值。表达式索引(也叫函数索引)直接索引表达式的结果:

-- 索引 lower(email) 的结果,而不是 email 本身
CREATE INDEX idx_users_lower_email
  ON users (lower(email));

-- 现在这条大小写不敏感的查找能走索引
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

注意查询里的表达式必须与索引定义里的完全一致(都是 lower(email)),优化器才会匹配。表达式索引的代价:每次 INSERT/UPDATE 都要计算一次该表达式来维护索引项,所以表达式应当是确定性的、不太重的函数。

3.5索引类型全景

B-tree 不是唯一的索引类型;数据形态和查询算子不同,最合适的索引结构也不同。

前四节都在讲 B-tree,因为它覆盖了绝大多数 OLTP 查询。但 jsonb 的包含查询、全文检索、几何最近邻、超大时序表——这些场景 B-tree 要么用不上,要么远不是最优。PostgreSQL 内置五种索引类型:

几点要点:

• GIN 是"倒排":对一行里的多个元素分别建索引项(如 jsonb 的每个 key、数组的每个元素、文档的每个词)。配 pg_trgm 扩展时,GIN 还能服务 §3.1 里 B-tree 做不到的 LIKE '%abc%' 中缀匹配。
• BRIN 不存每一行,只存"每一段连续块的值范围摘要"。所以它极小(一张几百 GB 的表,BRIN 往往只有几 MB)。代价是它只在"物理顺序与列值强相关"时有效——按时间追加写入的时序表是最佳场景,行的物理位置天然按 created_at 递增。
• Hash 在 PG 10 起已经写 WAL、崩溃安全,不再是过去那个"不推荐"的状态。但它只支持等值、不支持范围,而 B-tree 同样能做等值,所以实践中仍少用。

3.6索引为什么不被用 + 索引的代价

建了索引不等于会被用;而且每个索引都对写入收税——索引是"读变快 vs 写变慢/占空间"的权衡。

索引为什么不被用

"明明建了索引,优化器却没走"是最常见的困惑。逐条对应根因:

• 统计信息过期,估错了选择率:优化器以为命中很多行,觉得索引不划算。根因和修法见第 02 章 §2.2(ANALYZE / 提高统计精度)。这是头号原因。
• 列被函数包裹:WHERE lower(email) = ? 用不上 email 的普通索引。修法:建 §3.4 的表达式索引。
• 隐式类型不匹配:列是 bigint,查询写 WHERE id = '42'(字符串),或列是 text 却用数字比较——隐式转换会让索引失效。修法:让参数类型与列类型一致。
• 命中行太多,Seq 更便宜:这不是 bug,是正确的代价权衡。命中全表很大比例时,顺序扫描比海量随机回表更快。机制见第 02 章 §2.3。
• 表太小:只有几百行的表,整页顺序读一遍比走索引还快,优化器直接选 Seq Scan。
• LIKE '%x' 前导通配:通配符在前,B-tree 无法定位起点(§3.1)。修法:GIN + pg_trgm。

索引的代价:不是越多越好

索引让读变快,但不是免费的。三笔代价:

• 拖慢写入:每个索引都要在 INSERT/UPDATE/DELETE 时同步维护索引项。一张表上有 8 个索引,一次 INSERT 就要写 1 个堆元组 + 8 个索引项。索引越多,写入越慢。
• 破坏 HOT 优化:PostgreSQL 的 HOT(Heap-Only Tuple)优化能让"不改任何被索引列"的 UPDATE 不去更新索引,代价很低。但只要改了被某个索引覆盖的列,这次 UPDATE 就无法走 HOT,必须更新所有相关索引项。给一个频繁更新的列建索引,等于让每次更新都变贵。HOT 的机制见第 04 章 §4.4。
• 索引自身会膨胀:和表一样,索引在大量更新/删除后也会积累死项、变得臃肿,需要 REINDEX 重建才能恢复紧凑。膨胀与重建见第 04 章。