PostgreSQL 索引性能优化原理

很多人把"索引加速"理解成一句话：O(log n)。这太浅了。PostgreSQL 里索引优化性能，实际上有 5 层原理。

第 1 层：减少必须访问的 heap 页面数

没有索引时，往往只能 seq scan，把表页一页页读过去。有索引后，先在索引里缩小候选集合，只回表取少量行。核心收益不是"比较次数少"，而是 磁盘/缓存页访问少很多。

在数据库里，I/O 和 cache miss 通常比 CPU 比较次数贵得多。

第 2 层：把"无序大海捞针"变成"有序或定向访问"

B-tree 不只是找得到值，还能：

• 范围查找
• 按顺序扫描
• 支持 ORDER BY
• 支持某些 LIMIT 快速停止

因为它天然有序。于是你不仅省掉全表扫描，还可能省掉排序。

这就是为什么 WHERE ts BETWEEN ... ORDER BY ts LIMIT 100 往往非常适合 B-tree。

第 3 层：用统计信息判断"值不值得用索引"

PostgreSQL 不会"有索引就用"。它会根据 pg_statistic 里的统计估算 selectivity：谓词能筛掉多少行。如果估计匹配比例太高，走索引会变成"先扫索引，再大量随机回表"，可能比顺序扫更慢。

所以索引优化的本质不是"存在索引"，而是：

索引过滤后的候选集足够小，且访问路径总成本低于顺序扫。

这也解释了为什么：

• 高频值上索引可能没用
• 统计信息过旧会选错计划
• ANALYZE 很重要

第 4 层：Bitmap Scan：在"候选较多但还没多到全表扫"的中间地带获益

当单个索引匹配很多 TID，直接逐条回表会很随机。PostgreSQL 可以先做 Bitmap Index Scan，把候选 TID 记成位图，再按 heap block 顺序批量访问，从而减少随机 I/O。多个索引还可以做 bitmap AND / OR 组合。

这就是为什么 PostgreSQL 不需要像一些数据库那样强依赖"完美复合索引"才能组合多个条件。它可以折中地走多个单列索引再合并。

第 5 层：Index Only Scan：把"回表"也省掉

普通索引扫描最大的问题是：哪怕索引找到了 TID，仍要去 heap 检查可见性。而 PostgreSQL 的 index-only scan 要想成立，需要两个条件：

1. 查询所需列都在索引里（或 INCLUDE 里）
2. heap 页在 visibility map 中是 all-visible，说明该页所有 tuple 对所有事务都可见，无需再回表核验。

所以覆盖索引的真正收益不是"少读一个字段"，而是可能把一次查询从：

索引页 + 大量 heap 页随机访问

变成：

只读索引页

这往往是数量级差别。

为什么有时建了索引，性能反而变差

写入成本上升

每次 INSERT/UPDATE/DELETE，除了改 heap，还要维护相关索引。索引越多，写放大越明显。官方文档也明确提醒：索引会给整个系统增加开销，不应滥建。

非 HOT 更新会放大索引维护

表达式索引、覆盖索引、过多普通索引，都会增加"更新某行时哪些索引必须改"的概率。表达式索引尤其要注意，它会在插入和 non-HOT update 时重新计算表达式。

页分裂、膨胀、版本垃圾

B-tree 在写入/更新多时会 page split，产生碎片；MVCC 旧版本索引项又会堆积。虽然现代 PostgreSQL 用 bottom-up deletion 和 dedup 缓解，但不是免费。fillfactor、VACUUM、REINDEX、长事务都会影响最终表现。

选择性太差时，索引天然不划算

如果某条件能匹配表中很大比例的行，用索引意味着大量随机回表，往往不如顺序扫。部分索引的一个重要用途，就是不要给公共值建索引。

怎么理解几类"高级索引定义"的原理

多列索引（multicolumn）

B-tree 多列索引遵循"左前缀"思想更强，适合联合过滤和排序；但 PostgreSQL 18 文档也强调 skip scan 可以让某些只按后列过滤的查询也获得一定收益，尤其当前导列 distinct 值不太多时。

GIN/BRIN 的多列与 B-tree 不同，它们对"查询用了哪一列"的敏感度更低。

部分索引（partial index）

部分索引只给满足谓词的行建索引。它的价值不是语法花样，而是：

• 缩小索引体积
• 降低维护成本
• 把"真正会查的少数行"建得更精确

例如只给 status = 'active' 建索引，通常比给全表建 (status, ...) 更聪明。

表达式索引（expression index）

把 lower(name)、date_trunc(...)、拼接表达式结果直接存进索引。这样查询时不必对每行重新计算。代价是写入和 non-HOT update 维护更贵。

覆盖索引 / INCLUDE

INCLUDE 列是非 key 列，不能参与搜索条件，但可让 index-only scan 直接返回这些列。要注意它会让索引变大，而且 B-tree dedup 对 INCLUDE 索引永远不可用。

HOT、VACUUM、Visibility Map 为什么是理解索引性能的关键

HOT

HOT（Heap-Only Tuples）是 PostgreSQL 降低更新成本的关键优化。若更新未修改任何被索引引用的列，且原 heap page 有空间，新版本可只在 heap 页内串接，不必为每个索引新增条目。官方文档特别指出：核心里唯一"总结型索引"是 BRIN，它不阻止 HOT。

Visibility Map

visibility map 给每个 heap page 两个 bit，其中 all-visible bit 能让 index-only scan 安全跳过 heap 可见性检查。VACUUM 会更新这个 map，而 DML 会清掉相关位。

VACUUM

VACUUM 不只是"回收空间"，还会：

• 清理旧版本
• 维护统计信息
• 更新 visibility map
• 帮助 index-only scan 生效

所以很多"索引怎么不快"的根因，不在索引定义本身，而在 autovacuum 跟不上、长事务阻塞回收、统计过旧。

2026 年实践里，怎么选索引

最常见选择

• B-tree：默认首选；等值、范围、排序、唯一约束，80% 以上场景先想它。
• GIN：数组、jsonb、全文搜索。
• GiST / SP-GiST：几何、范围、相似性、最近邻、前缀/空间划分型数据。
• BRIN：超大表、追加写、物理顺序与值相关。
• Hash：很少数只做单列等值、且值很大、B-tree 不理想时再考虑。

设计原则

1. 先看查询模式，不要先看字段名。
2. 先少量、精确建索引，再看 EXPLAIN (ANALYZE, BUFFERS)。
3. 把"能否减少回表"作为优化重点。
4. 对高更新表，优先考虑 HOT 友好设计，少建无效索引。
5. 超大时序表优先考虑 BRIN，而不是盲目 B-tree。
6. 对公共值用 partial index，比全量索引更聪明。

下一步

理解了性能优化原理后，可以继续阅读 PostgreSQL 索引体系总览 了解整体框架，或深入阅读 GIN 索引详解 了解特定索引类型的实现。