PostgreSQL 如何选择索引

明明建了索引，数据库为什么不用？

这是数据库问题里最常见、也最容易误判的一类。表上有索引，查询条件里也写了索引列，EXPLAIN 却给出一个 Seq Scan。把 enable_seqscan 关掉，索引突然又能用了。于是很自然地会得出结论：规划器选错了，或者它的成本估算与实际情况不符。

但「能用某个索引」和「使用它成本最低」是两回事。索引先找到的是行的位置，不一定是查询最终需要的行；找到位置之后，还可能要读取表页、检查 MVCC 可见性、过滤误命中、排序、回表取列。若条件会返回全表三分之一的数据，沿索引执行几万次离散读取，可能确实不如顺着表读一遍。索引不是查询的必经入口，只是规划器可以选择的一条访问路径。

这也解释了为什么只背「最左前缀」「选择率要高」「小表不走索引」总觉得差一点。它们是若干常见结果，不是选择机制。真正的因果链是：SQL 条件能否被某个索引访问方法表达，统计信息估计会命中多少行，命中的行在磁盘上有多分散，这条路径还能否提供顺序、覆盖查询，最后整棵执行计划的总代价是多少。

我们先建立最小概念框架，再从磁盘页逐步分析到规划器源码。

一、索引加速的究竟是哪一步

先准备一张订单表：

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  total       numeric(12, 2) NOT NULL,
  note        text
);

CREATE INDEX orders_tenant_idx ON orders (tenant_id);
CREATE INDEX orders_status_idx ON orders (status);
CREATE INDEX orders_created_at_idx ON orders (created_at);

查询一条订单时，没有索引的朴素办法是读取每个表页，对其中当前快照可见的元组逐一判断：

SELECT * FROM orders WHERE id = 9527;

B-tree 索引把有序键值映射到 TID。规划器若选择主键索引，执行器先在索引树中找到 9527 对应的 TID，例如 (1842, 7)，再读取 heap 的第 1842 个块和其中第 7 个槽位。这里至少有两段工作：

1. 访问索引页，获得候选 TID。
2. 访问 heap 页，取得行版本并判断它对当前事务是否可见。

命中一行时，先查索引再访问 heap 的额外成本通常很低。命中十万行时，索引树本身也许仍然查得很快，但十万个 TID 可能分散在大量 heap 页上。若按索引顺序逐个回表，同一张表页可能反复进出缓存，随机读取成本很快超过顺序扫描。

所以选择索引的核心问题从来不是「树高是不是 O(log n)」。复杂度隐藏了缓存、页布局、返回行数和回表成本。数据库实际估算的工作更接近「访问多少页、处理多少索引项和元组、做多少次比较、能否提前停止」。

1.1 三种常见的索引执行形态

PostgreSQL 并不是只有「走索引」和「不走索引」两个开关。

Index Scan 边扫描索引边按 TID 访问 heap。它启动快，可以保留 B-tree 顺序，也适合 LIMIT 提前停止。代价是命中很多且物理位置离散时，heap 访问不友好。

Index Only Scan 尝试直接从索引返回查询所需列。但 PostgreSQL 的 MVCC 可见性信息通常在 heap 元组中，不在普通索引条目里。执行器必须查看表的 Visibility Map（可见性映射）；只有目标 heap 页被标记为 all-visible，才能省掉该页的 heap fetch。名字叫「Only」，并不保证一次表页都不读。

Bitmap Index Scan 先收集候选 TID，按 heap 块组织成位图，再由 Bitmap Heap Scan 依物理块顺序访问表。它牺牲索引提供的输出顺序和一部分启动速度，换来批量、去重和合并多个索引结果的能力，适合返回行数介于「极少」与「大半张表」之间的区域。

Index Scan
索引顺序：TID(900,2) → TID(12,7) → TID(701,1) → 回表顺序可能跳跃

Bitmap Heap Scan
收集 TID → 按块整理：12 → 701 → 900 → 每个 heap 页尽量只访问一次

顺序扫描则完全跳过索引，逐页读取 heap。它没有树查找和回表跳转，预读更自然，并行化也直接。返回比例较高时，顺序扫描可能就是成本最低的访问路径。

二、PostgreSQL 表与索引的物理存储

理解选择机制之前，需要先明确一个与 MySQL 不同的存储前提：PostgreSQL 默认 heap 表并不位于主键 B-tree 的叶子节点里。

按照 PostgreSQL 18 的数据库文件布局文档，每张普通表和每个索引都是独立 relation，通常各自对应以 filenode 编号命名的文件；文件超过 1 GB 时会被切成多个 segment。表和索引都由固定大小的块组成，默认 BLCKSZ 为 8 KB。它们共享底层的页框架，但页内具体内容由 heap、B-tree、GIN 等访问方法解释。

orders heap relation                orders_tenant_idx relation

主分支 main fork                    主分支 main fork
├─ block 0: heap page               ├─ block 0: B-tree metapage
├─ block 1: heap page               ├─ block 1: internal/leaf page
├─ block 2: heap page               ├─ block 2: internal/leaf page
└─ ...                              └─ ...

辅助分支                            辅助分支
├─ _fsm：空闲空间映射               └─ _fsm：空闲空间映射
└─ _vm：可见性/冻结映射

表的 Visibility Map 对索引只读扫描很重要，但它属于表，不是每个索引各维护一份。索引 relation 通常也有 Free Space Map（空闲空间映射，FSM），用于寻找有空间的页；heap 还拥有 Visibility Map（VM）。这些 fork 与主数据文件分别落盘，却都通过 PostgreSQL 的 relation/block 接口访问。

可以用系统函数观察它们：

SELECT pg_relation_filepath('orders') AS heap_file,
       pg_relation_filepath('orders_tenant_idx') AS index_file,
       pg_relation_size('orders', 'main') AS heap_bytes,
       pg_relation_size('orders_tenant_idx', 'main') AS index_bytes;

2.1 通用页布局

PostgreSQL 的数据库页布局可以简化成这样：

低地址
┌──────────────────────────────────────────┐
│ PageHeaderData：LSN、校验、上下界等       │
├──────────────────────────────────────────┤
│ ItemId 1 │ ItemId 2 │ ItemId 3 │ ...     │  行指针数组向下增长
├──────────────────────────────────────────┤
│              空闲空间                    │
├──────────────────────────────────────────┤
│ ... │ tuple 3 │ tuple 2 │ tuple 1        │  元组数据向上增长
├──────────────────────────────────────────┤
│ special space（部分索引方法使用）         │
└──────────────────────────────────────────┘
高地址

ItemId 是页内稳定槽位。元组在页内为了压缩空间可以移动，只要槽号不变，指向它的 TID 就不必修改。TID 中块号决定哪一页，槽号决定查页内哪一个 ItemId。这层间接寻址很小，却是 HOT 更新、页内整理以及索引指向 heap 的基础。

PostgreSQL 源码在 src/include/storage/bufpage.h 中定义通用页面头和 pd_lower、pd_upper、pd_special 等边界；heap 与各种内置索引在此之上放自己的 tuple。上图不表示所有索引页都有完全相同的业务字段：相同的是页框架，不同的是页内记录语义。

2.2 B-tree 的磁盘结构

B-tree 是默认索引，也是理解规划器最重要的一种。PostgreSQL 实际实现属于高并发友好的 Lehman-Yao 风格 B-link tree。索引的第 0 号块是 metapage，记录 root、fast root、层级等元数据；其他页承担内部节点或叶子节点，并通过左右 sibling 链接相连。

metapage
   │ root block
   ▼
┌───────────────────┐
│ internal page     │  separator key + child downlink
└─────┬───────┬─────┘
      │       │
      ▼       ▼
┌──────────┐ ⇄ ┌──────────┐ ⇄ ┌──────────┐
│ leaf     │   │ leaf     │   │ leaf     │
│ key,TID  │   │ key,TID  │   │ key,TID  │
└──────────┘   └──────────┘   └──────────┘

叶子项通常保存索引键和 heap TID；内部项保存分隔键与指向下一层页面的 downlink。非最右页面还有 high key，用来表示本页键空间的上界。搜索途中即使并发事务刚完成页分裂，扫描者也可以依据 high key 和右链继续向右，避免把整棵树锁死。实现的设计说明集中在 PostgreSQL 源码的 src/backend/access/nbtree/README。

重复值很多时，B-tree 还可能把多个 TID 合并为 posting list，这叫 deduplication。它减少叶子页膨胀和分裂，但并不改变「键最终映射到 heap 行版本」这件事。INCLUDE 的非键列不会参与搜索比较，而且会影响去重适用性；在 covering index 中加入过多大字段，通常会扩大索引并增加写放大。

2.3 索引页如何进入内存

有人会问「索引是不是全放内存」。通常不是。索引和表的权威副本都在持久化文件里，需要访问的页进入 PostgreSQL 的 shared_buffers，其文件内容还可能被操作系统页缓存。缓存的是页，不是启动时把整棵树反序列化成一份常驻对象。

一次 B-tree 查询可能命中根页和内部页缓存，只为叶子页或 heap 页发生真实 I/O。根和上层节点访问频繁，往往自然成为热页；巨大索引的所有叶子页则未必放得下。规划器通过 effective_cache_size 表达可利用缓存规模的估计，但这个参数不申请内存，也不保证某页命中。

因此「索引有 20 GB，服务器只有 16 GB，所以索引不能用」也是误解。工作集若集中在少量分支和叶子页，索引仍可能有效；反过来，一个只有 2 GB 的索引若访问模式随机、并发工作集很大，也可能不断淘汰有用页面。

三、不同索引类型解决不同检索问题

PostgreSQL 的索引访问方法是可扩展接口。规划器不是看到列上「有索引」就完成匹配，而是看查询操作符能否被该索引的 operator class（操作符类）支持。B-tree 擅长全序关系，GIN 擅长把复合值拆成键，GiST 提供可编程的搜索树框架，BRIN 保存块范围摘要。它们解决的问题不同，成本函数也可以不同。

PostgreSQL 18 索引类型文档列出 B-tree、Hash、GiST、SP-GiST、GIN 与 BRIN，contrib 还提供 Bloom。下面先概括适用场景，再分别说明存储与读写代价。

3.1 B-tree：默认不是因为总是最快，而是因为语义最常用

B-tree 支持 =、<、<=、>=、>，以及可以转化为范围的 BETWEEN、IN；也能提供有序输出，支持 ORDER BY ... LIMIT 尽早结束。对 text_pattern_ops 或合适 collation/operator class，还可以服务特定的前缀模式匹配。

多列 B-tree 的关键不是死背「最左前缀」，而是明白树按列顺序做词典序排列：(tenant_id, created_at) 先按租户分组，同租户内再按时间排列。

CREATE INDEX orders_tenant_created_idx
ON orders (tenant_id, created_at DESC);

SELECT *
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 20;

这条索引同时缩小键范围、提供结果顺序，还能遇到 LIMIT 20 后停止。两个独立索引做 BitmapAnd 虽然也可能找出交集，却会丢失原始索引顺序，通常还要额外排序。

PostgreSQL 18 的多列索引文档还描述了 B-tree skip scan：在缺少前导列等值条件的特定场景，规划器可以对前导列的不同值反复定位后续范围。不过它能否降低成本，高度依赖前导列基数和选择率。它拓宽了可用边界，没有改变合理列序的重要性。

3.2 Hash：只回答等值

Hash 索引保存由键计算出的 32 位 hash code，只支持 =。由于碰撞存在，找到相同 hash 后仍要检查原值。它不能提供范围和排序，也不能作为唯一索引。对于普通标量等值，缓存良好的 B-tree 已经很强，还额外支持更多操作，所以 Hash 不是见到等值查询就必选。

它的价值在于设计空间明确：非常长的键映射成固定大小 hash code，在特定工作负载上可能降低比较和索引体积。但要通过实际尺寸、读写和缓存命中验证，不能只根据 O(1) 与 O(log n) 的复杂度作出选择。

3.3 GiST：索引框架，而非一种固定树

GiST（Generalized Search Tree，通用搜索树）让操作符类提供 consistent、union、penalty、picksplit 等行为，数据库负责并发、WAL、页面和树框架。范围重叠、二维几何、PostGIS 空间查询、pg_trgm 相似度都可建立在它上面。

GiST 的内部键常是子树内容的近似边界。搜索时，边界说「这棵子树可能有答案」，执行器便继续向下；近似表示可能产生 false positive，因此计划里会出现 Recheck Cond。它还能支持 KNN（k-nearest-neighbor，最近邻）顺序：

SELECT id, location
FROM places
ORDER BY location <-> point '(121.47,31.23)'
LIMIT 10;

同为 GiST，不同 operator class 的剪枝质量、是否 lossy、能否 index-only scan 都可能不同。评价「GiST 快不快」而不说数据类型、操作符和分布，和评价「树快不快」一样缺少对象。

3.4 SP-GiST：当搜索空间可以不断分区

SP-GiST（Space-Partitioned GiST，空间分区 GiST）支持 radix tree、quad tree、k-d tree 等不必平衡的结构。它适合数据空间存在明确分割规则的问题，例如网络地址前缀、文本前缀和点。

GiST 更像用边界包住一组对象，边界可能重叠；SP-GiST 更强调把空间递归切成分区。两者都只是框架，实际能力由 operator class 决定。选择它不是为了显得比 B-tree 高级，而是查询谓词真的符合这种分区方式。

3.5 GIN：先把一个值拆成许多可检索键

GIN（Generalized Inverted Index，通用倒排索引）适合一行里包含多个元素的值。对数组，它可以把数组元素变成索引键；对 tsvector，把 lexeme 变成键；对 JSONB，operator class 决定抽取哪些键和值。每个键再指向出现它的 TID 集合。

CREATE INDEX documents_tokens_idx
ON documents USING gin (tokens);

SELECT *
FROM documents
WHERE tokens @> ARRAY['postgresql', 'index'];

「包含 PostgreSQL 且包含 index」可以先取得两个 posting list，再求交集。这种结构适合复合值内部的多键组合，但会增加写入工作。高频键的 TID 列表可能发展为 posting tree；启用 fastupdate 时，新条目可以先进入 pending list，之后批量合并到主结构。它降低前台插入的即时成本，却可能增加后续查询或清理的合并工作。gin_pending_list_limit、autovacuum 和写入突发共同影响延迟，不能只看索引刚创建后的查询测试结果。

全文检索中，PostgreSQL 官方推荐 GIN作为常用索引类型；GiST 的全文索引用固定长度 signature 表示文档，可能误命中。这里的结论只适用于对应全文 operator class，不是所有 GIN 都无条件优于 GiST。

3.6 BRIN：保存连续块范围的摘要

BRIN（Block Range INdex，块范围索引）按连续 heap 块范围保存摘要。默认 minmax operator class 对每段记录最小值和最大值。查询 created_at 的一个小时间窗时，如果某段的 [min, max] 完全不相交，就跳过整段；相交则访问其中 heap 页并逐行复查。

CREATE INDEX events_created_brin
ON events USING brin (created_at)
WITH (pages_per_range = 128);

BRIN 成败取决于列值与物理行顺序的相关性。按时间持续追加的事件表通常很合适；若时间被完全随机地散布在所有页，每个范围的最小最大值都覆盖很宽，摘要便无法排除多少页面。它仍然「被使用」也不代表过滤有效。

BRIN 极小，是因为它没有为每行保存一条普通索引项。相应地，它返回的通常是候选块，而不是精确 TID。pages_per_range 越大，索引越小、摘要越粗；越小则索引和维护成本上升、过滤可能更精细。这是明确的空间—精度取舍。

四、写操作如何维护索引

读路径只解释了索引为什么可能快，写路径才解释索引为什么不能免费多建。PostgreSQL 的每个普通索引都是独立 relation。插入一行时，heap 和每个相关索引都要修改，并生成必要 WAL；更新和删除还受 MVCC、HOT 与 VACUUM 影响。

4.1 INSERT：先有 heap 行版本，再有索引指针

一条普通 INSERT 可以粗略理解为：

heap 通过 FSM 寻找可能容得下新元组的页；真正插入后获得 TID。执行器随后为表上需要维护的索引计算表达式、判断 partial index 谓词，并调用索引访问方法的插入接口。B-tree 要定位叶子页，GIN 要抽取多个键，GiST 要计算 penalty 选择子树。一个业务行可能因此产生远不止一条索引项。

若目标 B-tree 叶子页放不下新项，会发生 page split。大致过程是分配新页、在旧页和新页之间重新分配项、更新 sibling 链，并把分隔键向父层插入。父页也满时，分裂可以向上级联，极端情况下创建新 root。PostgreSQL 不会每次插入都把整棵树重写，但分裂会增加 WAL、脏页和锁协调。

递增主键通常写在 B-tree 右侧热点区域，空间局部性好，却可能形成并发热点；随机 UUID 会把插入散到更多叶子页，降低单点集中，却更容易碰到缓存不命中和各处页分裂。UUIDv7 等近似按时间递增的键改善局部性，但选择主键仍要同时考虑分布式生成、隐私、宽度与业务语义。

唯一索引还要判断冲突。MVCC 下，索引中可能存在指向尚未提交或已经死亡行版本的相同键，访问方法不能只看「索引里已有同值」就立即报错；它需要配合 heap/事务状态等待或判断该冲突是否真实。INSERT ... ON CONFLICT 还会使用 speculative insertion（推测式插入）协调竞争。唯一性不是 B-tree 键集合那么简单，而是和事务可见性相接的一条协议。

4.2 UPDATE：PostgreSQL 通常写一个新行版本

PostgreSQL 的 heap MVCC 通常不会原地覆盖业务行。UPDATE 创建新 tuple version，并让旧版本的 xmax 等信息表示它被更新。旧快照可能仍要看到旧版本，新快照看到新版本。

如果每次更新都为所有索引插入新 TID，哪怕只把 note 从「A」改成「B」，也会产生不必要的索引写放大。HOT（Heap-Only Tuple，只在 heap 中链接的元组）为此存在：当更新没有改变需要新索引项的列，并且同一个 heap 页有空间容纳新版本时，PostgreSQL 可以让旧 tuple 的页内链指向新 tuple，不为普通索引创建新条目。索引仍指向 HOT 链的根，回表后沿链找到对当前快照可见的版本。

普通 UPDATE
index(key,TID-old)       index(key,TID-new)
          │                        │
          ▼                        ▼
      old tuple  ─────────────► new tuple（可能在另一页）

HOT UPDATE
index(key,TID-root)
          │
          ▼
      root tuple ──HOT chain──► new tuple（同一 heap 页）

这说明两个容易忽略的设计后果。

第一，给频繁变化的列建索引不仅多维护一个索引，还可能让相关更新失去 HOT 机会。INCLUDE 列同样存入叶子项，修改它通常也需要新索引项；covering index 省下的读，要和更高的更新成本一起算。

第二，表的 fillfactor 可以为页内更新预留空间。把它从默认值调低可能提高 HOT 比例，却会扩大 heap、降低每页行数。是否值得，要观察 pg_stat_user_tables.n_tup_hot_upd、表膨胀、缓存与真实写入模式，不能把某个固定比例当成通用答案。

PostgreSQL 18 的 HOT 存储文档给出了这套机制的公开边界，具体 heap 更新路径位于源码 src/backend/access/heap/heapam.c。源码中有很多并发、锁和可见性分支，上面的流程是机制摘要，不是逐行等价伪代码。

4.3 DELETE：提交不等于同步物理删除索引项

DELETE 首先让 heap 元组在删除事务提交后对新快照不可见。旧事务仍可能需要它，所以不能立刻回收空间；索引项也不会在 SQL 返回时同步从每棵索引树中物理删除。

等旧行版本不再可能被任何快照看到，VACUUM 才能清理 dead tuple，并调用各索引访问方法的 bulk delete/cleanup。B-tree 还有 bottom-up index deletion 等机制，尝试在版本 churn 将要引发页分裂前清掉不再需要的索引项；但这不意味着 VACUUM 失去作用。长事务、失效复制槽相关的旧快照边界或 autovacuum 跟不上，都会让 heap 和索引垃圾积累。

这也是为什么「表删掉一半数据，索引文件怎么没缩小」通常不是故障。普通 VACUUM 让空间可复用，不承诺把 relation 文件尾部所有空间归还操作系统。REINDEX 会重建索引，VACUUM FULL 会重写表且需要强锁；是否执行取决于实际膨胀、维护窗口与额外磁盘，不应作为无条件执行的日常维护操作。

4.4 WAL、脏页与崩溃恢复

heap 插入、B-tree 页分裂、GIN posting list 变化等持久结构修改都需要遵守 WAL rule：描述数据页修改的 WAL 必须先于对应脏页落到稳定存储。提交时通常等待 WAL 持久化，而 heap 与索引页可以稍后由后台写出。崩溃后再按 WAL redo。

因此每多一个索引，成本不只是多占磁盘：还会增加 CPU、buffer churn、锁竞争、WAL 体积、复制流量、检查点写入和 VACUUM 工作。反过来，删除索引也不能只看 idx_scan = 0：唯一约束、外键相关查询、月度任务、备用查询以及统计重置都可能使短期计数无法反映完整用途。

五、什么样的 SQL 条件才算能用索引

现在回到规划器。一个索引能成为候选路径，首先要通过语义匹配，而不是成本比较。

5.1 操作符类决定索引支持哪些查询

索引列绑定 operator class。它把 SQL 操作符映射成访问方法理解的 strategy，并提供比较、抽取、consistent 等 support function。B-tree 对一个类型的 =、<、> 能形成搜索边界；GIN 的 JSONB operator class 可能支持 @>、? 等；GiST 的范围 operator class 支持重叠与包含。

CREATE INDEX events_payload_idx
ON events USING gin (payload jsonb_path_ops);

jsonb_path_ops 与默认 jsonb_ops 支持的操作符集合和索引项组织不同。写了 USING gin 并不代表所有 JSONB 谓词都能使用它。PostgreSQL 的扩展索引接口文档正是把访问方法、operator class、operator family 与 support function 串起来的地方。

数据类型转换、collation、操作符方向也会影响匹配。一个看起来等价的函数包装可能使规划器无法直接把条件匹配到原列索引：

-- 普通 created_at 索引不能直接为这个表达式建立搜索边界
WHERE date(created_at) = DATE '2026-08-28'

-- 改成原列上的半开区间，通常更直接
WHERE created_at >= TIMESTAMPTZ '2026-08-28 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-08-29 00:00:00+08'

也可以建立表达式索引，但查询表达式要能被规划器识别为同一表达式，且其中函数必须满足索引表达式要求：

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

SELECT * FROM users WHERE lower(email) = 'a@example.com';

表达式索引用更多写入 CPU 与空间，换取读路径。若只是估算表达式分布不准，PostgreSQL 也支持 expression statistics，而不一定要建索引。

5.2 Partial Index 还要证明查询蕴含谓词

部分索引只保存满足 predicate 的行：

CREATE INDEX orders_unpaid_tenant_idx
ON orders (tenant_id, created_at)
WHERE status = 'unpaid';

查询若明确带有 status = 'unpaid'，它可能使用这个小索引。若条件是应用参数 status = $1，通用计划在规划时不能保证 $1 永远是 unpaid，就不能轻率使用只覆盖 unpaid 行的索引。正确性判断先于成本。

PostgreSQL 只能识别一定范围内的逻辑蕴含，并不是一个任意定理证明器。x < 1 可以蕴含 x < 2，但把同一业务规则写成复杂函数或等价却不同形的布尔表达式，规划器未必证明得出。查询无法使用部分索引时，应先检查 predicate implication，再考虑成本参数。

5.3 多列索引中，过滤与缩小扫描范围不是一回事

对 (a, b, c) B-tree，某个条件可以出现在 Index Cond 中，不代表它对减少读取的索引页同等有效。前导列等值约束和第一个非等值范围通常最能确定连续扫描区间；后续列可以在索引层过滤，避免回表，却可能无法避免扫描这段索引。

CREATE INDEX demo_abc_idx ON demo (a, b, c);

WHERE a = 10 AND b >= 20 AND c = 30

树可以直接定位 a = 10, b >= 20 的起点和范围；c = 30 能过滤索引项，但一般不能把 b >= 20 这段变成一个单独连续小区间。skip scan 在适合时会通过多次定位改善某些组合，仍然要算重复搜索是否比顺扫索引或表成本更低。

INCLUDE 列更明确：它只是 payload，不参与搜索和排序语义。它的主要用途是让索引包含查询输出列，从而具备 Index Only Scan 的数据条件。

六、规划器如何从「能用」走到「选择它」

一条 SQL 经过解析、重写后进入 planner/optimizer。这里不需要穷举整个规划器，只跟踪单表访问路径：

PostgreSQL 源码 src/backend/optimizer/path/indxpath.c 负责为 relation 考虑 index paths、匹配 restriction/join clauses、生成 BitmapOr 以及选择 BitmapAnd 组合；src/backend/optimizer/path/costsize.c包含多类计划节点的成本估算。函数名和细节会随版本演进，文章引用的是 REL_18_STABLE 分支，不应把某一行号当永久接口。

6.1 统计信息先估算会有多少行

ANALYZE 采样并把列统计写入 pg_statistic，普通用户可从 pg_stats 查看整理后的信息。常见内容包括：

• null_frac：空值比例；
• n_distinct：不同值数量的估计；
• most_common_vals 与 most_common_freqs：高频值及频率；
• histogram_bounds：排除 MCV 后的等频直方图边界；
• correlation：列逻辑顺序与 heap 物理顺序的相关性。

例如 status 只有 paid、unpaid、cancelled 三个值，若 paid 占 95%，查询 status = 'paid' 与 status = 'cancelled' 虽然使用同一列和同一索引，最佳计划也可能不同。只用「不同值有 3 个，所以每个占三分之一」会严重误判，MCV 正是为了捕捉偏斜。

范围条件则会结合直方图估算位置。多个独立条件默认需要组合选择率，但真实数据可能相关：country = 'CN' 与 currency = 'CNY' 并不独立。如果简单相乘，行数估计会过低。可用扩展统计记录 dependencies、ndistinct 或多列 MCV：

CREATE STATISTICS customers_country_currency_stats
  (dependencies, mcv)
ON country, currency
FROM customers;

ANALYZE customers;

PostgreSQL 18 的 CREATE STATISTICS 文档也明确写着：扩展统计目前还没有用于表连接场景的选择率估算。它不能解决所有 cardinality estimation 问题。

统计信息是采样和摘要，不是精确计数。数据刚批量导入、分布突变、统计目标太低、表达式没有统计、列间相关性未记录，都会让 estimated rows 与 actual rows 相差很大。索引选择「错」经常只是上游行数估计错，规划器在错误前提下做出了内部一致的决定。

6.2 成本是比较指标，不是实际耗时

规划器把 I/O 与 CPU 换算到同一套 cost unit。默认以顺序读取一页的 seq_page_cost = 1.0 为基准，随机页 random_page_cost 默认 4.0；还会计算 cpu_tuple_cost、cpu_index_tuple_cost、cpu_operator_cost，以及并行启动与传输成本。

一条 Index Scan 的成本可以粗略拆分为：

总代价 ≈ 索引启动和扫描成本
       + 命中索引项的 CPU 成本
       + 预计访问 heap 页的 I/O 成本
       + heap 元组过滤与表达式成本

这不是源码公式，只是帮助理解的拆账。实际估算要考虑缓存复用、页数、相关性、循环次数、并行性等。每种索引访问方法通过 amcostestimate 返回启动成本、总成本、选择率、index correlation 与预计页数；官方的索引成本估算接口给出了接口含义。

random_page_cost = 4.0 也不表示数据库断言随机 I/O 恰好慢四倍。官方配置文档说明，默认值已经假设不少随机访问会命中缓存。NVMe、网络块存储、数据是否常驻缓存会改变相对关系。应以系统级测试和真实计划验证参数，不要为了某一条 SQL 走索引就把全局值压到极端。

6.3 物理相关性会改变回表成本

设两个索引都命中 10,000 行。索引 A 的 TID 集中在连续的 500 个 heap 页，索引 B 的 TID 散布在 8,000 个页上；二者回表成本不会一样。

pg_stats.correlation 接近 +1 或 -1，表示列顺序与 heap 物理顺序高度相关。按该列扫描索引时，heap 访问更接近顺序，规划器估算的成本也会更低。持续递增写入的时间列常有较高相关性；随机值则接近 0。CLUSTER 可以按某索引一次性重写表来改善物理顺序，但 PostgreSQL 不会像 InnoDB 聚簇主键那样在之后自动永远维持它，后续写入会逐渐削弱相关性。

6.4 Sort、LIMIT 与 Join 会改变完整计划的成本

只比较扫描节点还不够。下面的查询也许用索引读全部 pending 订单并不占优，但 (status, created_at DESC) 能按目标顺序吐出前 20 行，避免全量排序：

SELECT id, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

规划器比较的是完整方案：Seq Scan + Sort + Limit 对 Index Scan + Limit。同样，Join 内侧的 parameterized index scan 会被外侧每一行重复调用，loop_count 与跨次缓存复用都会进入估算。一条单独看起来很快的索引路径，放进十万次 Nested Loop 后可能变成灾难；反过来，对外侧只有几行时它又非常合适。

因此，不能只根据计划中出现 Seq Scan 就判断索引选择错误。执行计划是一棵树，索引节点的价值由上下游共同决定。

七、PostgreSQL 为什么能同时使用多个索引

下面讨论多索引组合。PostgreSQL 不是由一个普通 Index Scan 同时扫描两棵索引树，而是让各索引先产出 TID 集合，再用 BitmapAnd 或 BitmapOr 合并，最后按 heap 页访问表。

假设只有两个单列索引：

CREATE INDEX orders_tenant_idx ON orders (tenant_id);
CREATE INDEX orders_status_idx ON orders (status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE tenant_id = 42
  AND status = 'unpaid';

一种可能计划是：

Bitmap Heap Scan on orders
  Recheck Cond: ((tenant_id = 42) AND (status = 'unpaid'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on orders_tenant_idx
              Index Cond: (tenant_id = 42)
        ->  Bitmap Index Scan on orders_status_idx
              Index Cond: (status = 'unpaid')

两个索引分别回答「哪些 TID 属于租户 42」与「哪些 TID 还是 unpaid」，BitmapAnd 取交集。OR 条件则可由 BitmapOr 求并集，同一索引甚至可以被扫描多次：

SELECT *
FROM orders
WHERE tenant_id = 42 OR tenant_id = 84;

PostgreSQL 官方组合多个索引文档明确说明，组合结果按物理 heap 顺序访问，因此原索引顺序会丢失；有 ORDER BY 时可能需要额外排序。每多扫一个索引也有成本，所以「可组合」绝不等于「一定组合」。

7.1 TIDBitmap 不是简单的一行一个 bit

PostgreSQL 的 TIDBitmap 以 heap block 为中心组织候选位置。内存足够时，它保存 exact page 及页内 offset；位图使用的内存接近 work_mem 限制时，一部分会退化为 lossy page/chunk，只记「这一页或这一组页可能有答案」，不再记精确槽位。

Exact page
heap block 1842 → offsets {2, 7, 19}

Lossy page/chunk
heap block 1842 → 只知道该页可能命中，必须检查页内可见元组

这时 Bitmap Heap Scan 要重新执行条件，EXPLAIN (ANALYZE, BUFFERS) 可能显示：

Heap Blocks: exact=812 lossy=96
Rows Removed by Index Recheck: 1432

Recheck Cond 不等于一定发生大量复查；有些索引访问方法本来就可能 lossy，TIDBitmap 内存降级也会导致复查。实际是否丢失精度，要看 exact/lossy 与移除行数。盲目提高 work_mem 可能让这条查询的位图更精确，却要记住 work_mem 可被一条复杂查询的多个节点、并行 worker 和并发会话分别使用。

TIDBitmap 与 Bitmap Heap Scan 的实现分别位于 src/backend/nodes/tidbitmap.c 和 src/backend/executor/nodeBitmapHeapscan.c。这层 executor 原语很关键：它把「多个访问方法产出的候选行位置」统一成了能做布尔运算、能按 heap 页批量消费的中间表示。

7.2 为什么不把所有可用索引全部 AND 起来

假设 tenant_id = 42 已把一亿行缩到 100 行，status = 'unpaid' 单独命中两千万行。为了再从 100 行排除其中 20 行，扫描拥有两千万个候选项的 status 索引通常会增加总成本。直接用 tenant 索引回表并过滤 status 成本更低。

规划器需要比较：

• 单个最强索引的扫描与回表成本；
• 再扫描一个索引能减少多少 heap 页；
• 构建、合并位图的 CPU 与内存；
• 位图是否可能变 lossy；
• 是否丢失 B-tree 顺序；
• LIMIT 能否提前停止；
• 一个复合索引是否已经同时表达多个条件。

源码 choose_bitmap_and() 并没有穷举所有索引的幂集。它先按成本等指标排序候选，并避免把使用相同 WHERE clause 的路径重复 AND；再从不同起点尝试加入能降低估算总成本的路径。这是规划期搜索空间与计划质量之间的工程折中。索引有十几个时，暴力枚举所有组合很快会让「优化查询」本身变成最慢的部分。

7.3 多个单列索引还是一个复合索引

能组合多个索引，并不表示复合索引不重要。

独立索引更灵活：tenant_id、status 各自查询都能用，也可以对 AND/OR 做位图组合；写入时每棵树较窄，组合选择由规划器按查询决定。

复合索引更有针对性：它可以一次树扫描同时缩小多个条件，保留列顺序，为 ORDER BY 和 LIMIT 服务，也可能通过 INCLUDE 覆盖查询。代价是列序固定、占空间、维护成本集中，而且未必适合只查后导列。

这不是二选一公式。最终要用真实 SQL、数据分布与 EXPLAIN (ANALYZE, BUFFERS) 验证。

八、MySQL 并非不能合并索引，只是边界和代价不同

「PostgreSQL 能同时用多个索引，MySQL 只能挑一个」是一句流传很广但不准确的话。MySQL 有 Index Merge，能够对同一张表的多个 range scan 做 intersection、union 和 sort-union；EXPLAIN 的 type 可显示 index_merge，key 会列出使用的多个索引。MySQL 8.4 文档沿用这套优化，官方的Index Merge 说明也列出了适用形式与限制。

更准确的说法是：PostgreSQL 把基于 TIDBitmap 的 AND/OR 组合做成了成熟、通用的访问路径；MySQL 也能 Index Merge，但在 InnoDB 的聚簇存储、适用条件、排序与成本权衡下，工程实践中往往仍更依赖精心设计的复合索引。 PostgreSQL 同样会优先选择成本更低的复合索引，不能把差异归结为数据库产品之间的绝对能力高低。

8.1 最根本的差异先来自行放在哪里

PostgreSQL 默认 heap 与主键索引分离。主键 B-tree 的叶子项本质上仍指向 heap TID，和其他普通 B-tree 在「回到表」这件事上没有 InnoDB 那样的身份差异。

InnoDB 是聚簇索引组织。MySQL 8.4 的聚簇与二级索引文档说明：主键通常就是 clustered index，其叶子记录保存整行；没有主键时选择第一个所有键列都 NOT NULL 的唯一索引，否则生成隐藏 clustered index。二级索引叶子记录保存二级键和主键值，缺少所需列时，再用主键回聚簇树取整行。

PostgreSQL
secondary B-tree leaf: (secondary_key, heap TID)
primary B-tree leaf:   (primary_key, heap TID)
                                      │
                                      └──► heap page 中的行版本

InnoDB
secondary B-tree leaf: (secondary_key, primary_key)
                                      │
                                      └──► clustered B-tree 再查一次
clustered leaf:        (primary_key, 完整行)

因此 InnoDB 二级索引的「row ID」其实是主键值。宽主键会被复制进每个二级索引，增加空间和缓存压力；二级索引查非覆盖列要再走一次聚簇 B-tree。PostgreSQL 的 TID 固定而紧凑，但 UPDATE 产生新 heap 版本、VACUUM 处理死亡版本，又带来另一套成本。只比较一次点查的树高，无法得出哪种存储模型整体更优。

8.2 两边组合的中间表示不同

PostgreSQL Bitmap Index Scan 先生成按 heap block/offset 组织的 TIDBitmap。BitmapAnd/Or 是显式 executor 节点，合并后自然按 heap 物理页读取，内存不足还能以 lossy 表示退化并复查。这条路径与 heap 的物理寻址方式非常契合。

MySQL Index Merge 合并的是多个 range scan 产生的 row IDs。intersection 要求输入具备适合合并的顺序；union 不能直接满足时可能使用 sort-union，先收集并排序 row ID 才返回行。InnoDB 的 row ID 是主键，合并后取完整行仍围绕 clustered index 展开。MySQL 还会结合 Multi-Range Read 等机制改善二级索引回表，但这不把它变成 PostgreSQL 的 page-oriented TIDBitmap。

所以 PostgreSQL 经常能够组合索引，是因为以下几层设计相互配合：

1. 所有内置索引访问方法都能围绕 heap TID 表达候选位置。
2. executor 有统一的 BitmapAnd、BitmapOr 与 Bitmap Heap Scan。
3. TIDBitmap 按物理 heap 页组织，合并之后还能改善回表局部性。
4. exact/lossy 两级表示让集合很大时不必无限吃内存。
5. planner 会把单索引、组合索引、顺序扫描和并行路径放在同一成本体系比较。

8.3 为什么实际观察中 MySQL 往往不做 Index Merge

先区分两个完全不同的结果：一种是 Index Merge 根本没有成为合法候选，另一种是它成为候选后因为成本更高而落选。只看 EXPLAIN 最终没有出现 index_merge，无法判断发生了哪一种。

8.3.1 Intersection 要求的不只是两个条件都有索引

MySQL 8.4 官方文档对 Index Merge intersection 的适用条件写得很具体。AND 连接的每个子扫描必须符合以下两类条件之一：

1. 对一个恰好有 N 个 key parts 的索引，使用 key_part1 = const1 AND ... AND key_partN = constN 覆盖全部 key parts。
2. 对 InnoDB 主键使用范围条件。

这意味着两个单列索引上的常量等值条件最容易符合要求：

CREATE INDEX orders_tenant_idx ON orders (tenant_id);
CREATE INDEX orders_status_idx ON orders (status);

SELECT *
FROM orders
WHERE tenant_id = 42
  AND status = 'unpaid';

tenant_id = 42 覆盖单列索引的全部 key parts，status = 'unpaid' 也一样，所以二者可以成为 intersection 候选。但下面两种写法已经不同：

CREATE INDEX orders_tenant_created_idx
ON orders (tenant_id, created_at);

-- 只约束复合索引的第一部分，不符合「全部 key parts 常量等值」
WHERE tenant_id = 42

-- created_at 是普通二级索引范围，不是 InnoDB 主键范围
WHERE tenant_id = 42
  AND created_at >= '2026-08-01'

第一条没有覆盖 (tenant_id, created_at) 的全部 key parts。第二条即使另有单列 created_at 索引，created_at >= ... 也是二级索引范围条件，并不属于文档列出的 intersection 形式。它们各自可能产生普通 range、ref 或其他访问路径，却不能由此推导出 MySQL 一定会将它们求交集。

这里的限制不是任意规定，而是由 streaming intersection 的输入顺序决定。InnoDB 二级索引实际按 (secondary_key, primary_key) 排列。扫描 status = 'unpaid' 这样的完整等值范围时，secondary key 固定，输出自然按 primary key 排列；扫描 tenant_id = 42 也一样。两个输入都按相同 row ID 顺序输出后，intersection 才能像合并两个有序序列一样向前推进，无需先物化全部结果。

若扫描 created_at >= ...，输出顺序首先是 created_at，只有相同时间值内部才按 primary key 排列，它不再是全局 primary-key order。要和另一个二级索引做流式 intersection，就缺少共同顺序。MySQL 源码把这类输入称为 ROR（Rowid-Ordered Retrieval）；MySQL 8.4.8 源码中的 get_best_ror_intersect()接收的正是一组 rowid-ordered scans。

InnoDB 主键范围可以参与是因为 clustered index 本身按主键，也就是 row ID 排列。不过官方文档还有一条容易忽略的限定：在 intersection 中，主键条件用于过滤其他索引扫描取得的 row ID，而不是再单独用于取得完整行。这和「两棵任意二级索引都能高效求交集」并不是同一种能力。

8.3.2 Union 能覆盖更多 OR，但可能需要先收集和排序

Index Merge union 要求 OR 的每个分支符合与 intersection 相近的 row-ID 有序条件，或者分支本身就是一个可用的 intersection。各输入按 row ID 有序时，可以流式归并、去重：

SELECT *
FROM orders
WHERE tenant_id = 42
   OR status = 'unpaid';

若 OR 分支是普通范围，不能直接保持统一 row-ID 顺序，MySQL 可能考虑 sort-union：

SELECT *
FROM orders
WHERE created_at < '2026-01-01'
   OR total > 10000;

sort-union 必须先取完各扫描的 row ID，再排序和去重，之后才能读取结果行。它的启动成本、内存和临时数据成本都高于流式 union，也无法为很小的 LIMIT 立即返回前几行。若任一分支命中很多记录，全表扫描或扫描一个索引后执行剩余过滤可能成本更低。

PostgreSQL 不要求每个 Bitmap Index Scan 都先按 heap TID 排序。各索引可以按自己的结构扫描，并在 TIDBitmap 中设置相应 block/offset；最后统一按 heap block 消费。这个中间表示使 PostgreSQL 可以对更一般的等值、范围和 operator-class 条件做 BitmapAnd/Or。其代价是构造完整位图、可能变为 lossy，而且会失去原索引顺序。PostgreSQL 的优势是组合输入更通用，不是多索引组合没有成本。

8.3.3 复杂布尔表达式可能无法形成可合并的 range tree

Index Merge 是 MySQL range optimizer 的一部分。它的输入不是原始 SQL 文本，而是从 WHERE 中提取出的同表 range conditions。函数、隐式类型转换、无法在优化期确定的值、不同 collation，以及复杂的 AND/OR 嵌套，都可能使条件无法转换成所需 range tree。

MySQL 官方文档直接承认深层 AND/OR 存在限制，并建议在计划不理想时尝试分配律变换：

(x AND y) OR z  => (x OR z) AND (y OR z)
(x OR y) AND z  => (x AND z) OR (y AND z)

两个表达式在布尔逻辑上等价，却可能让 range optimizer 产生不同候选，说明候选生成依赖表达式形态，而不是先把任意布尔表达式化简到完整规范形式。Index Merge 也不能用于 FULLTEXT 索引，只能组合同一张表内部的扫描，不能把不同表上的索引称为一次 Index Merge。

候选生成还有资源上限。MySQL 8.4 的范围优化文档说明，range_optimizer_max_mem_size 限制 range optimizer 可用内存；预计超过限制时，服务器会放弃该查询的 range optimization，转而考虑包括全表扫描在内的其他方法，并产生 warning 3170。大量 OR 或多个很长的 IN (...) 取笛卡尔组合时尤其容易扩张。此时没有 Index Merge 不是成本比较后的结论，而是相关候选根本没有完成生成。

8.3.4 候选合法，仍要在成本比较中胜出

假设两个单列等值索引满足 intersection 条件，MySQL 仍然不会无条件合并。大致需要比较：

Index Merge 成本
≈ 扫描索引 A
+ 扫描索引 B
+ 按 row ID 求交集或并集
+ 对最终 row ID 访问 clustered index
+ 读取与计算剩余条件

若 tenant_id = 42 单独只返回 100 行，再扫描一个命中两千万行的 status 索引，只为从中排除 20 行，显然会增加总成本。用 tenant 索引访问 100 行，然后计算 status 条件更合理。反过来，如果两个条件各自命中很多行，交集也很多，合并之后仍要做大量 clustered lookup，全表扫描可能成本更低。

InnoDB 的回表不是按 heap block 设置位图后统一读取，而是拿最终主键值访问 clustered index。MySQL 可以通过 Multi-Range Read先收集键、按主键排序，减少二级索引范围扫描的随机访问；但这又增加缓冲、排序和启动成本。对 ORDER BY ... LIMIT 20，能按目标顺序尽早停止的单个复合索引通常明显优于先合并完整 row-ID 集合。

覆盖查询是一项例外。如果查询所需列能由合并使用的索引共同覆盖，MySQL 文档说明 intersection 可以不读取完整行，EXPLAIN Extra 会出现 Using index。但业务查询常需要多个非索引列，最终仍会访问 clustered index。因此，实验中只执行 COUNT(*) 可能触发 intersection，改为 SELECT * 后计划变化，并不矛盾：两条 SQL 的回表成本不同。

8.4 MySQL 选择索引为什么没有那么智能

把规划器称为「智能」或「不智能」不够准确。索引选择由三件事共同决定：能生成哪些候选、掌握什么统计信息、怎样估算候选成本。 在多索引组合这个具体问题上，MySQL 8.4 相比 PostgreSQL 18 的限制可以落实到这三个层面。

第一，候选空间更窄。PostgreSQL 可以把多种索引访问方法产生的 TIDBitmap 统一做 AND/OR；MySQL Index Merge 是 range optimizer 中一组边界明确的特殊路径，intersection 尤其依赖 ROR 输入。一个理论上可以通过「扫描两个集合再求交集」实现的条件，不等于 MySQL 实现中存在对应候选。

第二，多列相关性信息较弱。InnoDB 持久统计主要通过抽样索引页估计索引及其前缀的 distinct count；innodb_stats_persistent_sample_pages 默认抽样 20 页，可以按表提高。MySQL 8.4 的优化器统计也有单列 histogram，可以描述一个列内部的偏斜。从 MySQL 8.4 公开的统计能力看，它没有 PostgreSQL CREATE STATISTICS 中 dependencies、multivariate MCV 和多列 ndistinct 的直接对等能力，难以显式记录两个非索引列或两个独立索引列之间的相关性。

例如 country = 'CN' 与 currency = 'CNY' 高度相关。若只知道两个单列条件各自的比例，却不知道它们经常同时出现，交集行数就容易估错。估得过少，可能错误选择 Index Merge 或 Nested Loop；估得过多，又可能提前放弃原本有效的索引组合。PostgreSQL 也会估错，扩展统计也不覆盖所有场景，但它至少为同表多列条件提供了更直接的校正机制。

第三，成本模型必须近似真实缓存与 I/O。MySQL 要估算二级索引扫描、row-ID 合并、clustered lookup、是否覆盖、MRR 和排序；其中任一项与实际 buffer pool 命中或数据分布偏离，都可能改变最终选择。PostgreSQL 同样依赖近似成本模型，因此不能据此推出 PostgreSQL 的所有计划都优于 MySQL。

第四，两边都会限制规划期搜索。PostgreSQL 的 choose_bitmap_and() 也不会穷举任意索引子集，而是用启发式方法控制规划时间；MySQL 的 ROR intersection 同样从符合条件的扫描中寻找低成本组合，并受 range optimizer 内存限制。区别在于 PostgreSQL 的 bitmap 抽象先让更多类型的扫描进入同一组合框架，MySQL 则在 ROR/range 阶段就排除了更多输入。因此在「多个独立索引能否临时组合」这一维度上，PostgreSQL 通常表现得更灵活；把这个结论扩展成「PostgreSQL 优化器在所有方面都更智能」则没有依据。

要判断 MySQL 到底在哪一步放弃 Index Merge，不能只看传统 EXPLAIN 的最终 key。可以打开 Optimizer Trace：

SET optimizer_trace = 'enabled=on';

SELECT *
FROM orders
WHERE tenant_id = 42
  AND status = 'unpaid';

SELECT JSON_PRETTY(trace)
FROM information_schema.optimizer_trace;

trace 可以显示 range analysis、候选索引、行数与成本估算、候选未被选择的 cause。还应检查 SHOW WARNINGS 是否出现 range optimizer 内存限制，使用 EXPLAIN ANALYZE 比较 estimated rows 与 actual rows，并用 ANALYZE TABLE 更新 InnoDB 索引统计。INDEX_MERGE hint 适合验证被成本模型拒绝的候选，不应在没有测量读行数、回表和排序成本时作为长期修复。

因此，MySQL Index Merge 难触发可以按下面的顺序判断：

1. 条件是否能转换成同表 range scans？
2. intersection 的每个二级索引是否满足「全部 key parts 常量等值」，从而按 row ID 输出？
3. OR 分支能否流式 union，还是必须使用成本更高的 sort-union？
4. range optimizer 是否因表达式规模或内存限制放弃候选？
5. 单个索引加剩余过滤、复合索引或全表扫描是否成本更低？
6. 统计信息是否正确估算了每个扫描以及交集、并集的行数？
7. 读取完整行、排序和 LIMIT 是否进一步提高了合并成本？

这七步把「MySQL 不够智能」拆成了可以通过文档、trace 和实际执行验证的问题。只有第一个到第四个属于候选表达能力；第五个可能是正确的成本选择；第六个和第七个才是常见的估算输入与完整计划问题。

8.5 DML 与 MVCC 的对比也会反馈到索引设计

PostgreSQL UPDATE 通常在 heap 创建新版本；满足条件时 HOT 可以不新增索引项。DELETE 后的旧 heap tuple 与索引项由 VACUUM 等机制逐步清理。索引本身通常不携带完整 MVCC 可见性，Index Only Scan 借助 VM 判断能否跳过 heap。

InnoDB 的聚簇记录包含事务与回滚相关字段，一致性读通过 undo log 构造旧版本；二级索引没有同等完整的版本信息，必要时仍要到聚簇记录判断。更新主键会改变聚簇位置，也会影响所有携带主键的二级索引；更新二级键则要维护对应二级树。PostgreSQL 没有「更新主键就移动整行到另一棵聚簇叶子」这一模一样的路径，但 TID/行版本变化和索引维护也绝不免费。

表格描述的是默认核心路径，省略了 change buffer、purge、不同隔离级别、扩展访问方法等大量细节。它只用于解释与索引选择有关的主要差异，不能代替对两个数据库完整实现的比较。

九、通过 EXPLAIN 检查规划器的估算

索引问题不要从猜参数开始，先取得执行计划。对只读查询，可以使用：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE)
SELECT ...;

ANALYZE 会真实执行 SQL。对 INSERT、UPDATE、DELETE 和有副作用的函数，生产环境不能随手加；即使包在事务后 ROLLBACK，执行期间的锁、序列变化、WAL、触发器外部副作用和资源消耗也未必消失。先用纯 EXPLAIN，或在安全副本和可控数据集验证。

9.1 先看估算行数，再看节点类型

Bitmap Heap Scan on orders
  (cost=120.00..4200.00 rows=800 width=64)
  (actual time=2.100..85.300 rows=82000 loops=1)

估算 800，实际 82,000，差两个数量级。规划器可能以为位图很小、回表不多，实际却访问大量页。此时优先检查统计新鲜度、MCV、直方图、相关列与参数值，不是先禁用 Bitmap Scan。

loops 也必须乘进去。节点单次只花 0.1 ms，如果被 Nested Loop 执行 100,000 次，就是十秒级工作。actual time 的显示方式和父子节点包含关系也要结合整棵树看，不能机械相加。

9.2 区分 Index Cond、Recheck Cond 与 Filter

• Index Cond：访问方法可用来限制索引扫描的条件。
• Recheck Cond：heap 阶段需要再次确认的索引条件，常见于 bitmap lossy 或 lossy access method。
• Filter：取到候选 tuple 后再执行的普通过滤，不能缩小前面的索引访问范围。

如果一个条件落在 Filter，大量 Rows Removed by Filter，说明索引只是用另一条件取得了很宽的候选集。此时可能需要调整复合索引、表达式索引、部分索引，或者接受顺序扫描本来更合理。

9.3 Index Only Scan 要看 Heap Fetches

Index Only Scan using orders_cover_idx on orders
  Heap Fetches: 15342

这说明计划类型虽是 Index Only Scan，执行时仍为不少索引项访问 heap。表刚经历大量写入时，相关 VM bit 会被清除；VACUUM 确认页上 tuple 都对所有事务可见后才能重新设置 all-visible。官方日常 VACUUM 文档明确说明了 VM 与 index-only scan 的关系。

因此 covering index 在追加后长期只读、VACUUM 跟得上的表上更容易获得预期收益；在持续更新的热点表上，INCLUDE 会扩大索引并阻碍部分 HOT，而执行时仍可能发生较多 heap fetch。

9.4 用 Buffers 分析页面访问

shared hit 表示在 PostgreSQL shared buffers 命中，shared read 表示需要由 PostgreSQL 请求读取块；它不等同于一定打到物理介质，因为操作系统可能缓存。还要看 temp read/write、I/O timing（需相应配置）、WAL 与系统层存储指标。

同一 SQL 第二次快很多，可能只是缓存热了。比较方案时要说明冷缓存、热缓存、并发、参数和数据规模；没有这些边界的「索引快 100 倍」几乎没有迁移价值。

十、几个最常见的误判

10.1 返回超过某个固定百分比就不会走索引

不存在跨表、跨硬件通用的 5% 或 20% 分界线。行宽、聚簇相关性、缓存、索引覆盖、表达式成本、并行能力、LIMIT、要读取的列都会移动临界点。窄表返回 5% 可能顺扫成本更低；宽表的覆盖索引返回 30% 也可能仍有价值。

正确问法是：这条路径预计访问多少索引页和 heap 页，能否避免排序或回表，估算与实际是否一致。

10.2 小表不走索引，说明索引坏了

小表可能只占几页，顺扫它的固定成本非常低；走索引还要访问根、叶子和 heap。表页又通常全在缓存，顺扫完全合理。随着数据量和分布变化，计划可能自然切换。要验证索引结构可用，可查看 catalog、做 EXPLAIN，必要时使用 amcheck；不要用「没有被选中」推导「索引损坏」。

10.3 把 enable_seqscan 关掉就是强制索引

这些 enable_* 参数会显著降低相应计划类型被选中的可能性，但不是绝对禁令；当没有合法替代路径时，顺序扫描仍可能出现。它们适合诊断和比较备选计划，不适合长期掩盖统计、SQL 或索引设计问题。

10.4 COUNT(*) 应该只扫最小索引

PostgreSQL 必须按 MVCC 快照判断哪些行可见。若 VM 条件合适，某个小而覆盖的索引可以 Index Only Scan；若大量 heap 页不是 all-visible，回表判断可能让它不如并行顺扫。数据库不会因为索引条目数量接近行数就假设所有条目都对当前快照有效。

10.5 给每个 WHERE 列都建单列索引，自动拼出最佳方案

Bitmap 组合有扫描、内存、合并、回表和丢序成本。多个低选择率单列索引可能都无法降低成本；高频固定组合通常仍值得设计复合索引。自动组合提供了额外的候选路径，但不能替代针对核心查询的索引设计。

10.6 MySQL 一次只能用一个索引

MySQL 有 Index Merge；PostgreSQL 的一个计划也可能在不同表、不同 partition、不同 join 层使用很多索引。讨论「一次」必须明确是单个 table access path，还是整条 SQL。只有明确比较层次，结论才有助于排障。

十一、从一条慢查询开始设计索引

假设业务查询是：租户查看最近一个月仍未支付的订单，按时间倒序取前 50 条。

SELECT id, total, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'unpaid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

不要立刻创建三四个索引。按下面顺序推演。

第一步，确认语义与负载。tenant_id 是否总有？unpaid 占比多少，是否因租户而异？时间窗通常是 30 days 还是可选？结果是否永远按时间倒序并且有小 LIMIT？查询频率与订单状态更新频率分别是多少？这些答案决定索引究竟服务稳定产品路径，还是偶发后台筛选。

第二步，看现有计划和统计。检查 estimated/actual rows、pg_stats 中 MCV 与 correlation、现有索引大小、写入与 HOT 比例。若统计已过期，应先执行 ANALYZE；否则后续设计会建立在失真的行数估算上。

第三步，比较候选设计。

-- 通用复合索引
CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC);

-- 若 unpaid 是稳定且较小的核心子集，可评估部分索引
CREATE INDEX orders_unpaid_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (total)
WHERE status = 'unpaid';

通用索引支持不同 status，但更大；部分索引只维护 unpaid 行，尺寸可能明显更小，并直接匹配排序。可当订单从 unpaid 高频更新为 paid 时，它也要删除/新增索引相关状态，INCLUDE (total) 会增大叶子项，并影响更新成本。id 是否需要 INCLUDE 要看主键与该索引的实际覆盖，PostgreSQL 二级索引不会像 InnoDB 那样自动把主键值作为逻辑 payload 供查询直接返回。

第四步，在近似生产数据与并发下验证。使用 EXPLAIN (ANALYZE, BUFFERS, WAL) 比较读路径，也测量写入 WAL、索引大小、更新延迟和 VACUUM。只测试一条热缓存 SELECT，会低估宽 covering index 的写入和维护成本。

第五步，保留最少但足够的索引。若新复合索引完全覆盖旧单列索引的真实用途，可以观察后再删除冗余项；但列序不同、排序不同、外键检查、部分谓词和 Index Only Scan 都可能让「看起来前缀相同」并不等价。删除前记录 pg_stat_user_indexes、查询日志和完整业务周期。

十二、维护与观测：索引选择会随数据变化

索引设计不是一次 DDL 后永久正确。表从十万行长到十亿行、status 分布从均匀变成 99.9% paid、存储从本地 NVMe 换成网络盘、业务从点查变成批量导出，临界点都会变。

可以从下面几组信息建立定期观察。

-- 表与索引的扫描、读取 tuple 统计
SELECT schemaname,
       relname,
       indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, indexrelname;

-- 索引大小
SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

-- HOT 与 dead tuple 概况
SELECT relname,
       n_tup_ins,
       n_tup_upd,
       n_tup_hot_upd,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

这些计数有边界：统计可能被重置，低频关键查询可能跨月运行，idx_tup_read 与 idx_tup_fetch 在 bitmap 和 index-only 等路径上的含义不能简单相减成「命中率」。它们用于提出问题，不负责自动给出 DROP INDEX 清单。

创建大索引也要考虑锁。普通 CREATE INDEX 构建更快但会阻塞并发写；CREATE INDEX CONCURRENTLY 降低写阻塞，却需要多阶段扫描、耗时更长、产生更多工作，而且失败后可能留下 invalid index 需要处理。生产变更前确认磁盘、WAL、复制延迟和失败恢复步骤。

索引疑似逻辑损坏时，可用 amcheck 的 bt_index_check 等能力做针对性检查。若只是膨胀，先查根因：长事务、autovacuum 不足、高频非 HOT 更新、随机写与过低 fillfactor 都可能影响结果。重建只能清理现在的结构，不会消除造成膨胀的工作负载。

十三、把选择机制压缩成一条可操作的判断链

遇到「为什么没走这个索引」，我会按如下顺序排查：

1. 合法性：操作符、类型、collation、operator class、表达式与 partial predicate 是否真的匹配？
2. 选择率：规划器估计返回多少行，实际多少？MCV、直方图、相关列统计是否足够？
3. 物理读取：预计触碰多少索引页和 heap 页，TID 是否集中，缓存与 correlation 如何？
4. 路径附加价值：索引能否提供排序、覆盖查询、参数化 Join 或 LIMIT 提前停止？
5. 替代路径：Seq Scan、Parallel Seq Scan、单 Index Scan、Bitmap Heap Scan、多个索引组合，谁的完整计划成本更低？
6. 写入代价：新增索引会增加多少 WAL、页分裂、缓存压力，是否降低 HOT，VACUUM 能否跟上？
7. 版本与边界：结论是否只在 PostgreSQL 18、特定扩展、某类存储和当前数据分布成立？

这条判断链也适用于 MySQL，只是物理模型要换成 InnoDB 聚簇索引、二级索引携带主键、undo/purge 与 Index Merge 的约束。把抽象层对齐后，两边许多执行计划差异都可以从存储布局和成本模型中得到解释。

结语

索引选择最值得记住的，不是哪个参数能提高索引路径的采用概率，而是数据库到底在比较什么。

索引先把谓词变成候选位置；heap、MVCC 与 Visibility Map 决定是否还要回表；页面数量、物理相关性和缓存决定 I/O；统计信息让规划器在执行前猜测这些数字；排序、LIMIT、Join 又把单表访问放回整棵计划。多索引组合也不是特殊语法，而是多个索引共同产出 TIDBitmap，再由 executor 做集合运算和按页读取的自然结果。

PostgreSQL 因为 heap TID、统一 Bitmap 节点和 page-oriented TIDBitmap 的配合，确实更容易把多个独立索引组合成有竞争力的路径。但 MySQL 并非完全做不到，它有 Index Merge；只是 InnoDB 的 clustered/secondary index 布局、row-ID merge 条件与回聚簇树成本，让复合索引经常更直接。与此同时，PostgreSQL 在固定条件、排序和小 LIMIT 下也一样会偏爱复合 B-tree。

所以，下次再看到「明明建了索引却不用」，应先通过 EXPLAIN 检查规划器估计会读取多少行、多少页，条件落在 Index Cond 还是 Filter，位图是否 lossy，索引是否提供了顺序与覆盖。规划器的估算可能出错；只有找出估算与实际数据、存储和负载之间的差异，才能修正索引选择问题，而不是仅仅让执行计划换成另一种节点。