Skip to content
中间件
PostgreSQL
索引实战

索引实战

PostgreSQL 的索引类型比多数数据库都多，但选型逻辑只有一句话：先看清查询和数据的形态，再选索引。本章按"B-tree 为什么够用、什么时候不够用、建了为什么不走、建错了怎么收拾"的顺序展开；EXPLAIN 的完整解读方法在 09-查询优化与EXPLAIN.md，这里只点到为止。

本章基于 02-数据类型与DDL.md 建立的示例底座：users(id, email, nickname, referrer_id, balance, created_at, updated_at)、products(id, title, price, stock, tags text[], attrs jsonb, created_at, updated_at)、orders(id, order_no, user_id, product_id, quantity, unit_price, status, paid_at, total_amount, created_at, updated_at)（total_amount 是 VIRTUAL 生成列——注意它不能被索引，索引只认实体列，这也是本章会再提一次的坑）。少数场景（软删除、外部单号、日志表）底座里没有对应对象，会在用到处就地补 DDL。

一、B-tree：默认选择不是没有理由

结构与一次插入的旅程

B-tree 是一棵多路平衡搜索树：根页、内部页、叶子页之间用键值范围衔接，叶子层自己再串成双向链表。千万行级别的表，索引高度通常只有 3–4 层——这意味着一次定位平均 3–4 个页的读取，而且内部页几乎常驻缓存，真正随机的只有叶子页这一层。可以理解成一本书：目录页逐层缩小范围，正文（叶子）按序排列且可以顺着链表横向扫。

一次插入的旅程：从根下降找到目标叶子页，页里有空闲就放下；页满了就分裂——一页变两页，把分隔键上插到父页，父页再满就继续往上裂，极端情况长出一层新根。两个工程含义：

  • 写放大主要花在页分裂和随机落页上，所以每多一个二级索引，每次 INSERT 就多一处潜在的分裂成本；
  • 单调递增的键（bigserial、now()）永远挤在最右侧页上，分裂出的左半页很快又满，热点集中但顺序好；反而是随机键（uuid 直接做主键）把写入均匀打散到整棵树，缓存命中率最差——这是 16-常见陷阱与最佳实践.md 里 uuid 主键那条陷阱的索引层根源。

复合索引的最左前缀：等值在前，范围在后 

CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at);

索引里的行按 (user_id, status, created_at) 整体排序。能被利用的查询形态：

WHERE user_id = 42                                    -- 前缀 seek
WHERE user_id = 42 AND status = 'pending'             -- 前缀 seek
WHERE user_id = 42 AND status = 'pending'
  AND created_at >= '2026-10-01'                       -- 前两列 seek，created_at 范围定位
WHERE user_id = 42 AND created_at >= '2026-10-01'     -- 只能用到 user_id 前缀

WHERE status = 'pending' 单独一条则完全用不上——跳过第一列去查第二列，就像合并按章节排的字典里按页码找"笔画是 5 的字"。列序设计的经验法则因此是：等值条件的列放前面（谁被放最前由查询频率决定），范围条件的列放最后。原因上面第三、四条例子已经演示了：一旦某个位置出现范围，后面的列就无法再参与 seek 定位，只剩页内过滤的价值。反过来把 created_at 放最前，则最常见的 user_id = ? AND status = ? 点查直接退化。

还有一个常被忽略的维度：ORDER BY。索引本身就是有序序列，WHERE user_id = 42 ORDER BY status, created_at 能直接沿索引输出、省掉排序节点，前提是 WHERE 的等值列恰好是索引前缀、ORDER BY 按索引剩余列顺序书写。

PG 14 起：重复值去重（deduplication）

14 之前，B-tree 每个重复值都独占一条索引元组。status 只有五种取值、city 只有几百种取值的列，索引里塞满了键值相同的条目，体积和缓存占用都虚高。PG 14 起，叶子页在本来要发生页分裂的时刻懒地把一批重复键合并成 posting list 元组——一个键值带一串堆 TID，TID 数组还做压缩。收益直接体现在 pg_relation_size 上：低基数列的索引能明显缩水，扫描时一次比对换回一串定位。开关是每个 B-tree 索引的 deduplicate_items（默认 on，用 CREATE INDEX ... WITH (deduplicate_items = off) 可关）。

PG 18：多列 B-tree 在更多场景可用（skip scan）

PG 18 release notes 原文是 “Support for ‘skip scan’ lookups that allow using multicolumn B-tree indexes in more cases”。对上面那个 (user_id, status, created_at) 索引，查询只约束了 status 时，18 之前基本没辙（只能再建一个以 status 开头的索引）；18 起优化器可以按第一列在索引里出现的离散值段逐段跳跃——相当于自动帮你对每个 user_id 值各做一次 WHERE status = 'pending' 的 seek 再合并。第一列 distinct 值越少这招越划算，第一列几千万个不同值时跳跃成本压过收益，优化器会放弃，所以它是兜底而不是万能。

注意：skip scan 不能替代索引设计。高频查询把 WHERE 的等值列按"等值在前、范围在后"排好，永远比指望优化器兜底便宜。

二、其他索引类型：先问它们解决什么问题

GIN：一行里有很多值时，B-tree 无能为力

B-tree 索引的是"这一行的这个字段等于什么"。可 tags text[] 里一行存了十个标签、attrs jsonb 里存了一堆键值对，查询形态是"哪几行包含 X"——B-tree 只能按整个字段值排序，对数组/文档内部的元素毫无办法。GIN（倒排索引）正是为"包含"而生：把每个元素反向映射到堆 TID 列表。

CREATE INDEX idx_products_tags  ON products USING gin (tags);
CREATE INDEX idx_products_attrs ON products USING gin (attrs jsonb_path_ops);

SELECT id, title FROM products WHERE tags  @> ARRAY['sale'];
SELECT id, title FROM products WHERE attrs @> '{"color":"red"}';

jsonb 两种 opclass 选一个：默认的 jsonb_ops 同时支持 @>、?（键存在）、?|、?&；jsonb_path_ops 只支持 @>，但索引更小、查询更快。只需要包含查询就用 jsonb_path_ops。

代价是写放大：一行 100 个数组元素，一次 INSERT 要更新 100 条倒排记录。GIN 的缓解手段是 fastupdate（默认 on）：新条目先进一个待合并的 pending list，攒够了（或 autovacuum 路过时）批量并入主结构——这是 gin_pending_list_limit（默认 4MB）控制的那块内存。副作用是刚写入的数据在并入前"看不见"，对写完立查有强要求、或者做大批量导入的场景，可以对该索引 WITH (fastupdate = off)，或者导入后手动把 pending list 清掉。全文检索（tsvector + GIN）是 GIN 最重要的战场，展开在 07-高级SQL.md。

GiST：几何、区间与"重叠"

GiST 的树节点存的不是精确键值，而是"包络区域"，查询时判断区域是否相交。它解决的是"和……重叠/包含/距离近"这类空间关系问题，典型例子是预订防撞：

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- 让 int 也能进 GiST，见下文

CREATE TABLE reservations (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id int  NOT NULL,
    during  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO reservations (room_id, during)
VALUES (3, '[2026-10-21 09:00, 2026-10-21 10:00)');

-- 失败：同房间、时间重叠，EXCLUDE 约束直接拦下
INSERT INTO reservations (room_id, during)
VALUES (3, '[2026-10-21 09:30, 2026-10-21 11:00)');

区间类型与 &&（重叠）算子见 02-数据类型与DDL.md。这个 EXCLUDE USING gist 一行顶得上"先查再插 + 加锁 + 重试"几百行代码，是防超卖/防双订的标准姿势。注意 room_id WITH = 那一半：int 类型本身没有 GiST 的等值 opclass，需要 btree_gist 扩展把 B-tree 的比较搬进 GiST——这也是 btree_gist 最常见的用途。

GiST 是 lossy 索引：包络相交只是必要条件，命中可能误报，所以每个候选都要回堆表重check && 真值——索引负责缩小范围，堆表负责最终裁决。GiST 还擅长最近邻（KNN）：ORDER BY location <-> point 可以顺着树按距离由近及远输出，地理查询见 07-高级SQL.md。

BRIN：超级大表的"目录的目录"

BRIN 不索引值本身，只记录每段物理块（默认 128 页）里该列的 min/max 摘要。它赌的是：日志表按时间追加，物理块顺序就是值的顺序，那么"查上周的数据"只要排除掉 min/max 覆盖不到上周的块范围即可。

-- 日志表底座里没有，就地造一张（30 天、每天 100 万行）
CREATE TABLE access_logs (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint,
    ts          timestamptz NOT NULL,
    path        text,
    http_status int
);
INSERT INTO access_logs (user_id, ts, path, http_status)
SELECT (random()*1000)::int,
       now() - (random()*30 || ' days')::interval,
       '/api/item/' || (random()*10000)::int,
       (ARRAY[200,200,200,200,404,500])[(random()*6)::int+1]
FROM generate_series(1, 1000000);

CREATE INDEX idx_access_logs_ts
    ON access_logs USING brin (ts) WITH (pages_per_range = 64);

SELECT count(*) FROM access_logs
WHERE ts >= '2026-10-19' AND ts < '2026-10-20';

体积大约是同一列 B-tree 的千分之一量级，建索引和扫描都便宜，是亿行级时序表唯一现实的"全列可查"方案。执行时 BRIN 先圈定候选块范围，回堆表再精确过滤——它做的是排除法，不负责精确定位。

边界情况要记牢：BRIN 的效力完全建立在"物理顺序 = 逻辑顺序"上。对会被随机更新的列（比如不断翻来覆去改 http_status），同一行的值散落在各块，摘要里的 min/max 会持续加宽直到索引丧失排除能力；而追加热点（最后一个块范围）被高频 UPDATE/INSERT 时，摘要页也会反复重写，形成小热点。所以 BRIN 适合"只追加、不重排、乱序不更新"的大表，UPDATE 多的列请交给别的索引。

PG 14 还允许 BRIN 每个范围记录多组 min/max（minmax_multi opclass），甚至用 bloom 过滤器做摘要——物理分布不那么好的数据也能吃到部分收益，随机值列（如 uuid）从此有了专门的 opclass。

Hash 索引

只支持等值 =，不支持范围和排序，体积略小于 B-tree。早年是"快但断电即坏"的半成品——不写 WAL，崩溃后不可用、也不进复制。PG 10 的 release notes 给它补上了 write-ahead logging（“This makes hash indexes crash-safe and replicatable”，10 之前建的 hash 索引 pg_upgrade 后需要重建）。即便如此，等值场景里它相对 B-tree 的体积优势太小、能力缺口太大，实际生产几乎不用，遇到就当认识了。

CREATE INDEX idx_orders_user_hash ON orders USING hash (user_id);
SELECT * FROM orders WHERE user_id = 42;   -- 仅等值可用

SPGiST 与 bloom：一句话各一条

SP-GiST 是空间非均衡划分树，内置 opclass 主要服务 point/box/polygon 和文本前缀这类形态，日常手工建它的场景很少。bloom 是 contrib 扩展：对多个列做签名位图，只支持等值、误判率不低，只在"宽表随机列等值组合查询、大到 B-tree 建不动"的日志型场景里考虑。

扩展索引：pg_trgm / btree_gist / pgvector

%xx% 这类中缀模糊匹配用 B-tree 无解，pg_trgm 把文本拆成三元组再交给 GIN/GiST：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_title_trgm ON products USING gin (title gin_trgm_ops);

SELECT id, title FROM products WHERE title LIKE '%咖啡%';
SELECT id, title FROM products WHERE title % '咖啡壶';   -- 相似度检索，也可走索引

btree_gist 上节已出场（把 B-tree 语义的列装进 GiST，服务 EXCLUDE 的混合约束）。向量相似度检索是 pgvector 扩展的 HNSW / IVFFlat 索引，RAG 应用的地基，三者都见 07-高级SQL.md。

三、特殊索引形态

部分索引：让索引只覆盖你在意的那部分

查询长期集中在一个小子集时（软删除后"活行"只占少数、待处理任务永远只有几个），给索引加 WHERE，只索引子集：

ALTER TABLE users ADD COLUMN deleted_at timestamptz;   -- 演示软删除：底座没有这列，按需补

CREATE INDEX idx_users_email_alive
    ON users (email) WHERE deleted_at IS NULL;

CREATE INDEX idx_orders_pending_created
    ON orders (created_at) WHERE status = 'pending';

体积可能小一个数量级，写放大也小——被"删除"的行根本不在索引里，软删除翻 deleted_at 时这个索引几乎不动，这正好是 16-常见陷阱与最佳实践.md 里"软删除大表全列索引越来越肥"那节陷阱的解法。

使用条件是硬的：查询必须带上匹配谓词（写了 WHERE deleted_at IS NULL，或优化器能从 WHERE id = 5 推出该行未删——它推不出，所以老老实实带条件）。忘带谓词的查询不会用这个索引；枚举列稍好，status = 'pending' 的查询能被部分索引接住。软删除表上所有查询统一经 DAO 层注入 deleted_at IS NULL，是这类索引能活好的工程前提。

表达式索引：对列套函数的解药 

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

SELECT id FROM users WHERE lower(email) = lower('Tom@Example.COM');

经典用途是大小写不敏感的唯一。不要存 lower(email) 冗余列，也别急着上 citext——直接在表达式上建 unique：

CREATE UNIQUE INDEX ux_users_email_lower ON users (lower(email));

Tom@x.com 和 tom@X.com 归一化后相等，第二条注册直接被索引层拒绝。表达式索引里存的是函数结果，所以 WHERE 侧写的表达式必须与索引定义完全一致（lower(email) 能匹配，lower(trim(email)) 不能）。

覆盖索引 INCLUDE（PG 11+）与 index-only scan 

CREATE INDEX idx_orders_user_cover
    ON orders (user_id) INCLUDE (quantity, created_at);

SELECT user_id, quantity, created_at FROM orders WHERE user_id = 42;

PG 11 起 INCLUDE 列不进排序键、只"搭便车"存在叶子里，让上面这条查询的所需列全部在索引内拿到——理想情况下完全不碰堆表，这就是 index-only scan 的完全体。为什么说"理想情况下"：index-only scan 省掉回表的前提是能从**可见性映射（VM）**确认"这一页所有行对所有事务都可见"；页的 all-visible 位要靠（auto）vacuum 来置位，vacuum 跟不上时它照样回表逐行检查，INCLUDE 的收益当场打折。VM 与 vacuum 的机制在 06-MVCC与Vacuum.md 展开，这里记住结论：覆盖索引的收益 = vacuum 的健康度。INCLUDE 列不参与唯一性/排序，所以 CREATE UNIQUE INDEX ... INCLUDE 的被覆盖列允许任意类型，连没有 B-tree 支持的类型都行。

UNIQUE … NULLS NOT DISTINCT（PG 15+）

SQL 传统里 NULL ≠ NULL，所以可空列上的 UNIQUE(external_no) 拦不住两行都为 NULL 的记录（底座 orders 没有这列，就地补一个"外部系统单号，可以没有"的场景）：

ALTER TABLE orders ADD COLUMN external_no text;

CREATE UNIQUE INDEX ux_orders_external
    ON orders (external_no) NULLS NOT DISTINCT;

两个 NULL 视为重复。默认仍是 NULLS DISTINCT，老库行为不变；多列唯一同理。

opclass：同一列的另一种"比较观"

opclass 决定索引用什么函数比较你的列。最常见的一处：库按非 C collation（如 en_US）初始化时，LIKE 'abc%' 的前缀匹配走不了默认 B-tree——因为排序规则与逐字节比较不一致，要用 text_pattern_ops：

CREATE INDEX idx_products_title_prefix ON products (title text_pattern_ops);
SELECT id FROM products WHERE title LIKE '咖啡%';

C collation 或 ICU “und-x-icu” 之下默认 opclass 通常可以直接支持前缀匹配，先 EXPLAIN 验证再决定要不要加。排序规则与字符集对索引的更多影响见 02-数据类型与DDL.md。

四、优化器为什么不走你的索引

这是索引问题里最高频的一类，逐条给"场景 → 原因 → 对策"。

对列套函数 / 隐式类型转换

  • 场景：WHERE created_at::date = current_date 全表扫。
  • 原因：索引按列的原始值排序，表达式结果不在索引里；隐式 cast 同理——只要转换发生在列这一侧，对优化器就等于"对列套了函数"。varchar 参数比对 text 列通常没事（二者二进制兼容，只算重标注）；会翻车的是列侧被塞了真函数的组合，比如 date 列去和 timestamptz 表达式比较（date 被提升为 timestamptz）、自定义类型缺 binary-coercible 关系。pgx 绑参的 Go 类型与列类型差一档时也会现场制造这类转换，编码细节见 08-Go客户端pgx.md。
  • 对策：把列裸露出来，改写为半开区间 WHERE created_at >= current_date AND created_at < current_date + 1；确实离不开表达式就上表达式索引。分不清 cast 落在哪一侧时，看 EXPLAIN 里该列有没有被包进函数。

LIKE 前置通配

  • 场景：WHERE title LIKE '%咖啡%'，B-tree 索引纹丝不动。
  • 原因：B-tree 的本事是"有序"，前导任意字符让起点无法确定，等于没有前缀可用；只有 LIKE 'abc%' 这类右通配能利用有序性（注意上一节的 collation 条件）。
  • 对策：中缀/模糊交给 pg_trgm + GIN（见第二节）；自然语言检索上全文检索，见 07-高级SQL.md。

ORDER BY 方向与 NULLS 位置不匹配

  • 场景：ORDER BY created_at DESC, id DESC LIMIT 20 在 (created_at, id) 索引上仍然出现 Sort 节点。
  • 原因：索引可以整体反向扫描，反向会同时翻转方向和 NULL 位置——单列 ASC（默认 NULLS LAST）倒着扫恰好给出 DESC NULLS FIRST，多数查询因此没问题。坏在方向混搭：索引 (a ASC, b DESC) 正向输出 (a ASC, b DESC)、反向输出 (a DESC, b ASC)，查询 ORDER BY a DESC, b DESC 两头都够不着；NULLS 位置同理，DESC NULLS LAST 需要索引本来就是 DESC NULLS LAST。
  • 对策：按查询的 ORDER BY 逐字照抄方向与 NULLS 位置建索引，例如 ON orders (user_id, created_at DESC NULLS FIRST)。有没有消掉排序，EXPLAIN 里看一眼便知（09-查询优化与EXPLAIN.md）。

选择性太低：成本模型说不

  • 场景：WHERE status = 'pending' 占全表三成，优化器坚持 seq scan。
  • 原因：走索引意味着三成行逐行随机回表，成本模型里随机 IO 单价远高于顺序扫描；命中行占比超过约百分之几到百分之十几后，索引+回表就是亏的。这是经济学，不是 bug。
  • 对策分两路：如果列本身低基数（5 种状态），接受现实，改用"低基数列放最后 + 其他过滤列放前"的复合索引；如果成本模型对 IO 的估价过时——机械盘时代默认 random_page_cost = 4.0——SSD/NVMe 环境把它调到 1.1 左右，优化器会对索引友好得多：
-- postgresql.conf，或会话级临时验证
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

另一条隐藏线是统计信息过期：长期未 ANALYZE 的表行数估歪，选择自然歪。先看 EXPLAIN 的估算行数和实际行数差多少，再决定动索引还是动统计。

小表：seq scan 才是正确答案

几千行的表总共几个页，走 B-tree 要 3–4 次页读取再回表，顺序扫描一趟就完事。优化器对小表全选 seq scan，属于正常履职。不要用 enable_seqscan = off 去"逼"它——那是调试开关，关掉一条路只会让它选更差的路，生产配置里出现它本身就该被 review 掉。

索引膨胀：MVCC 的死条目

UPDATE 一行的索引列，PG 并不原地修改索引，而是插入新条目、旧条目等 vacuum 回收（机制在 06-MVCC与Vacuum.md）。高频更新列的索引因此比理论体积肥出数倍：树更高、缓存更散，成本估算变差后优化器可能干脆弃用。症状是"明明有索引、idx_scan 也不低，但 index scan 就是慢，且 pg_relation_size 明显不对"。对策：确认后 REINDEX CONCURRENTLY（见第五节），并回头审视这个"高频更新 + 高价值索引"的组合本身是否合理——比如把只被状态列覆盖的索引改成部分索引，让它只装活跃行。

大批量 OR：bitmap 是正常形态

  • 场景：WHERE user_id IN (500 个值) 或一长串 OR，EXPLAIN 里出现 Bitmap Heap Scan，有人以为索引没生效。
  • 原因/事实：对大批量等值，逐条树下降会反复乱序回表；优化器改为每个分支查索引、把 TID 攒进位图、合并去重后按物理顺序回表——BitmapOr 正是"批量用索引"的经济解，多索引同表时可各自出位图再合并。
  • 对策：不需要对策，这是好计划。需要警惕的是反过来的退化：位图内存不够会退化成排序合并，见 09-查询优化与EXPLAIN.md 里 lossy bitmap 的部分。

五、索引维护与观测

CREATE INDEX CONCURRENTLY：为什么必须

普通 CREATE INDEX 在构建期间对表持 SHARE 锁——读没问题，INSERT/UPDATE/DELETE 全部排队。一张千万行的热表，建索引十分钟，业务就停摆十分钟。CONCURRENTLY 不阻塞写，代价是构建过程要扫表两遍并且中途等待所有存量事务结束：第一遍建条目，第二遍再扫一遍确认哪些条目对快照可见，中间还可能等待。耗时更长、随时可能失败，所以它是带风险的操作，不是免费的。

CREATE INDEX CONCURRENTLY idx_orders_user_created
    ON orders (user_id, created_at);

注意：CONCURRENTLY 不能在事务块里执行。Go 侧用 pgx 直接 Exec（连接不在事务中）即可；用 database/sql 时确认没有先 Begin；迁移工具（Flyway 等）里要把它标记为 non-transactional。pgx 的具体写法见 08-Go客户端pgx.md。

失败的 CONCURRENTLY 留下 INVALID 索引

第二遍失败（最常见原因：表里有违反唯一约束的存量数据），索引不会消失，而是以 INVALID 状态留下——占空间、拖慢每次写、查询却不使用它。主动巡检：

SELECT n.nspname AS schema, c.relname AS index_name
FROM pg_index i
JOIN pg_class c     ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;

处理方式是删掉重建，没有原地修复：

DROP INDEX CONCURRENTLY idx_orders_user_created;
-- 清理表中违反约束的数据后，再来一遍

ADD CONSTRAINT ... UNIQUE USING INDEX 失败也会留下同样的 invalid 索引/约束，排查思路一致。

REINDEX CONCURRENTLY（PG 12+）

膨胀多年要重建时，REINDEX INDEX/TABLE 默认锁写；PG 12 起加 CONCURRENTLY，原理等价于"并行新建同名索引再原子切换"，切换瞬间会短暂等锁。命令行对应 reindexdb --concurrently。个别对象不能并行重建（如带 exclude/表达式依赖的、系统目录上的），报错信息会说清楚原因，再退回普通 REINDEX 并安排在低峰窗口。

找出闲置索引

pg_stat_user_indexes 记录每个索引自上次统计重置以来被扫描的次数：

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

删除前先排除两类"假闲置"：支撑外键级联和唯一性底线的索引（如被引用的唯一键，删了业务语义就碎了），以及只被周期报表/月末批处理使用、统计窗口恰好没轮到的索引。pg_stat_reset 之后或大版本刚升级完，全表都会呈现 idx_scan = 0，别被吓到。确认要删的，用 DROP INDEX CONCURRENTLY，并保留一个完整业务周期观察。

体积核对 

SELECT c.relname, pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind = 'i' AND c.relname LIKE 'idx_%'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 10;

健康基准：同一批列、同等行数的索引体积应该在同一个数量级里。一个索引比同构表上的同类大出几倍，基本就是膨胀（结合 06-MVCC与Vacuum.md 确认），或者列型选错（把整段 jsonb 塞进了 B-tree 之类）。btree_gist 这类跨类型组合 opclass 建的索引体积特性与纯 B-tree 不同，对比时不要混在一起看。

六、外键到底要不要建索引

先把方向掰开。被引用侧（父表被 REFERENCES 的列）是 PK/唯一键，自带索引，不用操心。引用侧（子表的 FK 列）PG 默认不建索引，这是 PG 和某些数据库最不一样的地方：

CREATE TABLE order_items (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku      text   NOT NULL
);
-- order_items 上只有 id 的主键索引；order_id 一列裸奔

裸奔的两笔账：

  1. 删父行时，ON DELETE CASCADE（以及父表主键被更新时的 ON UPDATE CASCADE/SET NULL）必须在子表里找出"谁引用了这行"——没有索引就是子表全表扫描。子表越大，这条 DELETE 越像一次小型灾难，而且长时间持有锁、拖累其他写（锁与外键的交互见 05-事务与锁.md）。
  2. 业务最常见的正向查询"某订单的所有明细"其实通常有别的索引可走，真正的痛点是反方向：按父键删/查/校验。

判断方法就一句：这条 FK 有没有级联删除/更新，或者有没有"按父键反查子行"的热路径；有，就给 FK 列建索引：

CREATE INDEX idx_order_items_order ON order_items (order_id);

拿不准就默认全建——一个 FK 列的索引成本是每次插入多一条索引条目，漏掉一次级联全表扫描是线上事故，这笔账永远划算。

实战建议

  • 索引数量的上限由写放大决定，不由磁盘决定。 核心交易表一般控制在 5–6 个二级索引以内；逼近上限还缺索引时，先重新审视查询和表结构，而不是继续堆。
  • 每个新索引要在三个查询以内证明价值。 上线前写出它要加速的最多三条典型 SQL，EXPLAIN ANALYZE 给出真实使用的证据；证明不了的索引是纯负债——占空间、拖写入、还迷惑优化器。
  • 上线前索引 review 清单：
    1. 复合索引列序是否等值在前、范围在后；
    2. 软删除/活跃子集是否用部分索引，并且所有查询都带谓词；
    3. ORDER BY 的方向与 NULLS 位置是否和索引一致；
    4. 有级联语义的外键列是否有索引；
    5. random_page_cost 是否已按 SSD 调整；
    6. 大表 DDL 是否 CONCURRENTLY、是否移出事务、失败巡检（indisvalid）是否进了发布流程；
    7. idx_scan = 0 与索引体积核对是否接入了例行巡检。
  • 索引不是建完就结束：它随 MVCC 老化（06-MVCC与Vacuum.md）、随统计信息失真（09-查询优化与EXPLAIN.md）、随负载迁移（本节第五节的闲置查询）。把"索引健康"当作周例行，而不是查询慢了的临时抱佛脚。
SQL 查询实战事务与锁