9 巧夺天工：索引设计与效果验证

索引不是“给字段加速”的装饰，而是为一组操作符、谓词、排序与返回形状维护的额外数据结构。每增加一个索引，读取路径多一个候选，写入路径也多一份维护、WAL、缓存和生命周期成本。

因此索引设计从第 8 章的已证实 workload 开始：

query family + representative parameters + SLO
  → predicate operators and expression semantics
  → join/order/group/limit and returned columns
  → data distribution, correlation and write pattern
  → access method + operator class + key order/predicate/include
  → before/after read evidence
  → size, HOT, WAL, write and maintenance evidence
  → build/failure/recovery plan
  → retain, merge or reject

“出现 Index Scan”不等于成功，“仍是 Seq Scan”也不等于失败。小表、低选择率、大范围、缓存与成本参数都可能让 Seq Scan 合理；一个被 planner 使用的索引也可能只优化冷门参数，却让所有写入变贵。

本章目标

完成本章后，读者应当能够：

• 把 access method、data type、operator class 与 query operator 对应起来；
• 解释 B-tree、Hash、GiST、SP-GiST、GIN、BRIN 与 Bloom 的边界；
• 从等值、范围、连接、排序、Top-N 与返回列推导 key order；
• 知道“最具选择性的列放最前”不是通用多列索引算法；
• 说明 PostgreSQL 18 B-tree skip scan 的能力及 14–17 的版本边界；
• 正确设计 expression index，并处理函数 volatility、collation 与语义一致性；
• 解释 partial index 的 predicate implication 与 generic parameter 陷阱；
• 使用 INCLUDE，同时理解 visibility map、heap fetch 与宽 payload 成本；
• 量化索引的空间、写放大、WAL、cache、vacuum 与复制代价；
• 解释 HOT 的两个条件，以及 indexed/update column 如何使 HOT 失效；
• 区分重复、重叠、未使用、无效与约束支撑索引；
• 设计数据、cache、参数、并发一致的 before/after 实验；
• 选择普通或 concurrent build，监控阶段并处理 INVALID 残留；
• 在 Pigsty 中关联 query、table/index、WAL、锁、I/O 与复制延迟；
• 为订单、库存、全文搜索与时间序列给出 retain/reject 决策；
• 把索引收益与代价证据追加到 PREF-PLAN-005。

实验边界

实验基线为 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04 L1；主体 SQL 保持 PostgreSQL 14–18 可用，PG18 skip scan 单独标注。实验只在 shop_private 建七张带 marker 的 ch09_* fixture：

orders        200000 rows / placed=5% / customer 42 target=10
inventory     300000 rows / 30 warehouses / SKU 4242 target=30
search        100000 rows / full-text target=100
events        400000 rows / physically time-correlated / target=600
write twins   50000 + 50000 rows
unique probe  10000 rows / 5000 duplicate groups

setup 在 marker 完全匹配后重建，属于 R1。reset 删除专属对象，属于 R2，需要 action/target 双 token。实验不为 shop 业务表新增或删除任何索引。

候选索引使用 CREATE INDEX CONCURRENTLY，但这只是 L1 机制演练：生产上线还要独立评估长事务、两次扫描、CPU/I/O、WAL、磁盘峰值、复制延迟、唯一语义与失败恢复。

下载资产：

• 实验合同
• 上下文 guard
• 机器计划上下文
• 确定性 fixture
• 订单查询
• 订单参数计划
• 库存查询
• 全文检索查询
• 事件范围查询
• 候选索引生命周期
• 候选 VACUUM
• 无额外索引写对照
• 有 volatile 索引写对照
• 写统计快照
• 写 fixture 恢复
• 并发失败注入
• 索引 catalog 快照
• 索引决策账本
• 语义分析器
• v0.4 规则提案
• 状态验收
• 双 token reset
• 任务入口

本章目录

9.1 索引方法与操作符类

• 9.1.1 B-tree 与 Hash 的适用查询
• 9.1.2 GiST、SP-GiST 与空间、范围、近邻问题
• 9.1.3 GIN 与数组、JSONB、文本检索
• 9.1.4 BRIN 与物理相关的大表；Bloom 的扩展边界

9.2 从谓词、连接与排序推导索引

• 9.2.1 等值、范围与多列顺序
• 9.2.2 连接键、排序、分组与 Top-N
• 9.2.3 选择率、相关性与访问路径

9.3 表达式、部分与覆盖索引

• 9.3.1 表达式必须与查询语义一致
• 9.3.2 部分索引的谓词蕴含与参数陷阱
• 9.3.3 INCLUDE、index-only scan 与可见性图

9.4 索引也有写入和生命周期成本

• 9.4.1 写放大、缓存占用与 WAL
• 9.4.2 HOT 更新、页分裂与填充因子
• 9.4.3 重复、未使用与失效索引的判断

9.5 验证而不是“加完就快”

• 9.5.1 计划、缓冲区、延迟分布与写入代价
• 9.5.2 数据规模和缓存状态一致的 A/B 对照
• 9.5.3 线上创建、失败回收与监控窗口

9.6 实战：为订单、库存与搜索入口设计索引

• 9.6.1 从真实查询清单提出候选索引
• 9.6.2 在 Pigsty L1 保留、合并或拒绝并记录证据
• 9.6.3 将索引审查规则追加到规约

实测摘要

一次 PostgreSQL 18.6 全量验收得到：

order:
  literal/custom → partial covering index-only / Heap Fetches=0
  generic status parameter → cannot use partial predicate
inventory:
  before → warehouse-first primary key skip scan
  after  → reverse covering index-only / Heap Fetches=0 / rows=30
search:
  generated tsvector GIN / rows=100
event:
  BRIN retained / B-tree comparison rejected and removed
  BRIN/B-tree size fraction=0.00273
write:
  unindexed volatile column HOT ratio=1.0
  indexed volatile column HOT ratio=0
  statement WAL bytes=11257432→11631392
concurrent:
  SQLSTATE 23505 / INVALID observed / exact drop / remaining=0
decisions:
  retain=4 / reject=4
final:
  worker=0 / rejected-or-failed index=0
  relation checksum=f8a7bfae59c6d16cd323abecfefe1014

时间、cost、buffers、WAL 精确值与节点组合不是 golden；它们受硬件、cache、checkpoint、版本和数据布局影响。稳定断言是 partial/generic 语义、结果行数、index-only heap fetch、BRIN 相对空间、HOT 失效方向、WAL 增长方向、INVALID 生命周期和最终 catalog。

章节验收

1. 每个候选先有 query/parameter/order/return shape 与 SLO；
2. 能从 operator class 证明某个 clause 可被索引，而非只看列名；
3. 多列顺序由 equality/range/order/workload 推导；
4. PG18 skip scan 不被写成 PG14–17 的通用前提；
5. partial index 的 query predicate 可在 planning time 蕴含 index predicate；
6. generic parameter 不能证明任意值满足 partial predicate；
7. covering index 同时检查 projection、VM/heap fetch 与 payload 宽度；
8. GIN/BRIN 的 lossy/recheck、write 与物理相关边界明确；
9. 索引评审包含 size、WAL、HOT、write latency 与 cache；
10. 不以 idx_scan=0 单独删除索引；
11. constraint、replica identity、rare critical query 与统计 epoch 已排除；
12. A/B 使用同一数据、统计、参数、cache、并发和重复方法；
13. concurrent build 的阶段、额外扫描、长事务与磁盘水位已评估；
14. build 失败后查询 pg_index.indisvalid/indisready 并精确回收；
15. Pigsty 面板结论能落回 query、catalog、plan 与 WAL/复制证据；
16. task.sh all 与双 token reset 均通过，业务 checksum 不变。

下一章 ch10《顾此失彼：并发控制与隔离异常》 将验证即使单条查询和索引都正确，并发交错仍可能破坏业务不变量。

参考资料

• PostgreSQL 18：Indexes
• PostgreSQL 18：Index Types
• PostgreSQL 18：Multicolumn Indexes
• PostgreSQL 18：Partial Indexes
• PostgreSQL 18：Index-Only Scans
• PostgreSQL 18：CREATE INDEX
• PostgreSQL 18：Heap-Only Tuples
• Pigsty：PostgreSQL Dashboards

上一章：抽丝剥茧：慢 SQL 诊断方法论 · 返回上卷导读 · 下一章：顾此失彼：并发控制与隔离异常 · 查看全书目录 · 查看索引中心

9.1 索引方法与操作符类

判断一个 clause 能否使用索引，要同时回答：

index access method
  + indexed data type/expression
  + operator class/family
  + query operator
  + collation/order/predicate

“这个列有索引”不够。payload @> ...、name LIKE ...、point <-> ... 分别需要与其操作符语义匹配的 operator class；同一数据类型也可能有不止一种索引语义。

可查询当前环境的 operator class：

SELECT
    am.amname AS index_method,
    opc.opcname AS opclass_name,
    opc.opcintype::regtype AS indexed_type,
    opc.opcdefault AS is_default
FROM pg_am AS am
JOIN pg_opclass AS opc
  ON opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;

扩展可新增 type、operator 与 opclass，所以最终答案来自目标环境 catalog 和扩展文档，而不是一张静态“索引类型速查表”。

9.1.1 B-tree 与 Hash 的适用查询

B-tree：默认不是偶然

B-tree 支持有全序关系的数据，核心 operator 为：

<  <=  =  >=  >

BETWEEN、IN、IS NULL/IS NOT NULL 等可以转成相应搜索；它还可以按索引顺序输出，支持 uniqueness、多列、expression、partial、INCLUDE 与 index-only scan。因此以下 workload 通常先考虑 B-tree：

WHERE customer_id = $1
WHERE placed_at >= $1 AND placed_at < $2
WHERE customer_id = $1 ORDER BY placed_at DESC LIMIT 20
WHERE lower(email) = lower($1)  -- 前提是表达式/语义匹配

单列 B-tree 能正向或反向扫描，所以仅为了 ORDER BY occurred_at DESC 通常不必再建一个 DESC 单列索引。多列混合顺序才有区别：

CREATE INDEX event_tenant_time_idx
ON event (tenant_id ASC, occurred_at DESC);

它可以直接提供 ORDER BY tenant_id ASC, occurred_at DESC；普通 (tenant_id, occurred_at) 的整体反向扫描会得到两列同时反向，不能产生“一升一降”。

前缀 pattern search 要特别看 collation/operator class。LIKE 'foo%' 有机会变成范围扫描，LIKE '%foo' 不能靠普通 B-tree 从左定位。非 C locale 下，可能需要 text_pattern_ops/varchar_pattern_ops；但 pattern opclass 不替代普通 locale ordering，需要范围比较时可能要保留默认 opclass 索引。不要看到 LIKE 就盲目加普通 B-tree。

Hash：只有等值

Hash index 保存值的 32-bit hash code，只处理简单等值：

CREATE INDEX session_token_hash_idx
ON session USING hash (token_hash);

它不能提供范围、排序、unique、多 key 组合或 INCLUDE。PostgreSQL 14–18 的 Hash index 已是 WAL-logged、crash-safe 的正式能力，不应继续引用早期版本“hash index 不可靠”的旧结论；但 B-tree 也能处理 equality，并有更广能力，所以 Hash 需要实测证明 size/cache/lookup 收益，而不是因字段名叫 hash 就选 Hash。

Hash 适合候选的条件通常很窄：

• 只有一个宽值的 equality lookup；
• 不需要排序、范围、unique 或 covering；
• 真实数据与 cache 下比 B-tree 有明确收益；
• hash collision recheck 与额外 heap access 可接受；
• write、WAL、build、backup 与维护成本已比较。

如果 equality 本身已经由 PK/unique B-tree 支持，再建 Hash 多半只是重复成本。

9.1.2 GiST、SP-GiST 与空间、范围、近邻问题

GiST 和 SP-GiST 都是扩展索引策略的基础设施，不是“一种固定的空间树”。是否可用取决于 operator class。

GiST

GiST 可承载平衡树式的广义搜索。核心与扩展生态常见：

• 几何/空间 overlap、containment；
• range/multirange overlap 与 containment；
• btree_gist 提供 B-tree-like GiST opclass；
• pg_trgm 的相似/模糊匹配；
• PostGIS geometry/geography 空间 operator；
• 支持的 opclass 上做 K-nearest-neighbor ordering。

核心 point 示例：

CREATE INDEX place_location_gist_idx
ON place USING gist (location);

SELECT place_id
FROM place
ORDER BY location <-> point '(101,456)'
LIMIT 10;

这里 <-> 是该 operator class 的 distance ordering operator。把表达式改成未经索引支持的自定义距离函数，GiST 不会因“语义看起来一样”自动使用。

GiST 也用于 exclusion constraint：

EXCLUDE USING gist (
    room_id WITH =,
    occupied_during WITH &&
);

它表达“同一房间的时间范围不得重叠”。这是约束语义，不只是性能；删除此类索引可能破坏 constraint，不能按 idx_scan 清理。

SP-GiST

SP-GiST 支持非平衡、space-partitioned 结构，例如 radix tree、quadtree、k-d tree。常见候选：

• 有前缀/层次分割特征的数据；
• core point/quadtree；
• inet prefix；
• text prefix 与特定 operator class；
• 支持 distance ordering 的 opclass 上做 KNN。

GiST 与 SP-GiST 谁更好不能由“空间数据”四字决定。要对照：

operator/operator class support
data clustering and skew
query predicate and KNN shape
index size/build/update
lossy recheck
concurrency and vacuum

第 17 章会用 PostGIS 具体讨论 bounding box、distance、SRID 与 exact recheck；本章只固定索引方法的选择方式。

9.1.3 GIN 与数组、JSONB、文本检索

GIN 是 inverted index：把一个值拆成多个 component/token，再从 token 找到包含它的行。典型数据：

• array elements；
• tsvector lexemes；
• JSONB keys/values/path tokens；
• pg_trgm trigrams；
• 扩展定义的可分解值。

数组：

CREATE INDEX article_tags_gin_idx
ON article USING gin (tags);

SELECT *
FROM article
WHERE tags @> ARRAY['postgresql'];

全文检索：

CREATE INDEX product_search_gin_idx
ON product USING gin (search_document);

SELECT product_id
FROM product
WHERE search_document @@
      websearch_to_tsquery('simple', 'postgresql observability');

查询必须沿用同一 text search configuration 和 document 构造。索引 to_tsvector('english', title)、查询 to_tsvector('simple', title) 并非同一语义。生产常把 document 做成 stored generated column，使构造、统计与索引合同可见。

JSONB 有两个常用 core GIN opclass：

-- 默认，支持更广的 key/value/existence 类 operator
CREATE INDEX doc_ops_idx ON doc USING gin (payload);

-- 更紧凑、常适合 @> 与 jsonpath，但能力边界不同
CREATE INDEX doc_path_idx
ON doc USING gin (payload jsonb_path_ops);

哪一个更好由实际 operator 与数据决定。给任意大 JSONB 建默认 GIN，可能索引大量从不查询的 token，增加 size、pending list、写入和 vacuum 成本。

GIN 不提供有序输出，也不能 index-only 返回原值，因为 entry 通常只保存 component。结果经常是 Bitmap Index Scan → Bitmap Heap Scan，并对 lossy/候选项 recheck。出现 Recheck Cond 是实现证据，不自动表示索引坏。

GIN 的 fastupdate 默认把更新先放 pending list，以批量合并摊薄写成本；这可能让个别读或清理出现尖峰。评审需观察 pending-list 行为、autovacuum、写入 burst、index size 和 WAL，而不是只测静态查询。

9.1.4 BRIN 与物理相关的大表；Bloom 的扩展边界

BRIN：索引 block range summary

BRIN 不为每行保存精确 key，而为连续 heap block range 保存 min/max 等 summary。因此它依赖“值与物理行顺序相关”：

CREATE INDEX event_occurred_brin_idx
ON event USING brin (occurred_at)
WITH (pages_per_range = 32, autosummarize = on);

适合：

• append-only/mostly append 时间序列；
• 单调增长 ID；
• 极大关系、宽范围查询；
• 能接受 lossy bitmap + heap recheck；
• B-tree 空间/cache/write 成本不划算。

不适合：

• 物理顺序已与值随机化；
• 每次只查极少行且需要精确 point latency；
• range 内大量无关行的 recheck 不可接受；
• 误以为 BRIN 能提供 ORDER BY——有序输出仍只有 B-tree。

pages_per_range 越大，索引通常越小但 summary 越粗；越小则更精确、索引和维护更大。新页范围还需要 summarization，可用 autosummarize、vacuum 或 BRIN 函数管理。

本章 400000 行按 occurred_at 物理写入。相同 600 行范围：

BRIN bytes=24576
B-tree bytes=9003008
fraction≈0.00273

具体字节不是通用阈值；稳定结论是这种分布下 BRIN 以显著更小的空间支持范围，B-tree 的额外精度对声明 workload 不值成本。

BRIN 不是 partitioning。它不会改变 retention、约束、每分区索引或 drop lifecycle；partition pruning 与 BRIN filtering 可以并用，但解决不同层次问题。

Bloom：contrib extension，不是 core 默认方法

bloom 是随 PostgreSQL 提供的 extension access method，需安装扩展后使用。它把多列 equality 特征编码成 lossy signature：

CREATE EXTENSION bloom;

CREATE INDEX asset_bloom_idx
ON asset USING bloom (tenant_id, region, kind, state);

适合“很多列、查询任意 equality 组合、维护所有 B-tree 组合过贵”的特定问题。false positive 必须回 heap recheck；signature 越大，误报少但索引更大。其限制包括：

• core module 只带有限类型 opclass；
• 只支持 equality；
• 不支持 unique；
• 不支持 NULL lookup；
• 不能排序、范围或替代约束。

Bloom 与 BRIN 都可能很小且 lossy，但机制不同：BRIN 按物理 block range summary，Bloom 为每行/索引项保存 signature。选择前先写 query operators 和数据布局。

返回本章目录 · 下一节：从谓词、连接与排序推导索引 · 查看全书目录 · 查看索引中心

9.2 从谓词、连接与排序推导索引

索引设计的输入不是表结构，而是查询合同。面对一条 SQL，先把它改写成下面这张工作单：

query family:
  predicates = column/expression + operator + representative value
  joins      = outer/inner side + join key + expected cardinality
  order      = exact key/direction/NULLS + LIMIT
  output     = returned columns and width
  workload   = parameter distribution + frequency + concurrency + SLO
  writes     = INSERT/UPDATE/DELETE columns and rate

然后才提出：

access method (key columns [direction]) [INCLUDE payload]
[WHERE stable predicate]

这样做会自然排除“这个字段经常查，所以单独给它建索引”一类脱离操作符、组合方式和代价的建议。

9.2.1 等值、范围与多列顺序

B-tree 的有效搜索区间

对多列 B-tree (a, b, c)，传统且跨 PostgreSQL 14–18 都成立的基本推导是：

1. 从最左侧开始的等值条件不断缩小连续索引区间；
2. 第一个没有等值、但有不等式的列确定该区间的起止边界；
3. 更右侧条件仍可在索引内检查，却不一定进一步减少需要扫描的索引项；
4. 排序能否直接复用，还取决于等值前缀、列顺序、方向和 NULLS 规则。

例如：

WHERE tenant_id = $1
  AND state = 'open'
  AND created_at >= $2
  AND created_at <  $3
ORDER BY created_at DESC
LIMIT 50

一个自然候选是：

CREATE INDEX ticket_tenant_state_time_idx
ON ticket (tenant_id, state, created_at DESC);

两个 equality key 固定前缀，created_at 同时承担 range 与 ordering。若把 created_at 放到 state 前面，进入时间范围后，右侧的 state 通常只是过滤条件；它仍可能减少 heap visit，却不能像等值前缀那样缩短该时间范围本身。

这不是要求把所有等值列机械放在所有范围列之前。真正的问题是：

• 哪些条件总是一起出现，哪些只是某个 query family 才有；
• 哪个条件在 planning time 可见；
• 是否要支持某个 ORDER BY ... LIMIT；
• 同一个索引还要服务哪些前缀查询；
• 写入是否频繁改变这些列；
• 一个较短、可复用索引是否已足够。

“最具选择性的列放最前”不是算法

设 tenant_id=$1 选出全表 1%，state='open' 选出 10%。对总是同时出现的两个等值条件，(tenant_id, state) 与 (state, tenant_id) 最终都能把搜索收敛到相同组合；不能只凭全局选择率宣布第一种必然更快。顺序更应考虑：

• 单独按 tenant_id 与单独按 state 的真实 workload；
• 后续 range/order 列如何衔接；
• distinct 数、数据倾斜和参数分布；
• 是否能省掉另一个索引；
• index tuple、prefix compression/dedup 与 write cost 的实测结果。

“选择率最高在前”最多是一条需要上下文的启发式，不是 PostgreSQL 多列索引的正确性规则。

从订单查询推导，而不是从订单表推导

本章订单 query family 是：

SELECT order_no, amount_minor, placed_at
FROM shop_private.ch09_order_probe
WHERE customer_id = 42
  AND order_status = 'placed'
ORDER BY placed_at DESC
LIMIT 20;

fixture 有 200000 行，placed 占 5%，目标客户恰有 10 行已下单记录。候选不是把所有 WHERE 列都当普通 key，而是：

CREATE INDEX CONCURRENTLY ch09_order_placed_cover_idx
ON shop_private.ch09_order_probe
    (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor)
WHERE order_status = 'placed';

推导逐项对应：

这只是“有资格”的设计；9.3 会证明 generic parameter 可能无法使用这个 partial predicate，9.4–9.5 还要证明它值得让写入长期维护。

PostgreSQL 18 的 B-tree skip scan：能力，不是默认设计借口

本章库存表已有主键：

PRIMARY KEY (warehouse_id, sku_id)

但查询是：

WHERE sku_id = 4242

在 PostgreSQL 14–17，不能把“后导列也在联合主键里”当成高效定位的通用保证；通常要么扫描大量索引项，要么选择其他路径。因此反向 query family 的自然候选是：

CREATE INDEX ch09_inventory_sku_cover_idx
ON shop_private.ch09_inventory_probe (sku_id, warehouse_id)
INCLUDE (available, reserved, updated_at);

PostgreSQL 18 引入 B-tree skip scan。若前导列 distinct 很少、后导列条件足够有用，planner 可以为若干可能的前导值重复发起 index search，跳过不可能匹配的大段索引。本章 30 个 warehouse、300000 行的 fixture 上，before plan 确实对 warehouse-first 主键使用了 skip scan；这是一条真实的 PG18 路径。

边界必须同时保留：

• skip scan 是 PostgreSQL 18 新能力，不能倒写成 14–17 的前提；
• 它由 cost model 选择，不保证每次出现；
• 前导 distinct 很大时，重复搜索可能不划算；
• 即使 before 已能 skip scan，专用 (sku_id, warehouse_id) 仍可能更直接、更小或更容易覆盖；
• 最终保留哪一个由读收益、索引大小和写成本决定，不由节点名决定。

因此章节验收不把“before 必须出现 Skip Scan”设为跨版本 golden，只要求 after 候选能正确支持 declared SKU lookup。

9.2.2 连接键、排序、分组与 Top-N

连接索引建在被反复探测的一侧

“JOIN 列要建索引”同样太粗。以下 nested loop 中，外侧每产生一个 customer，内侧就按 order.customer_id 探测：

SELECT c.customer_id, o.order_no
FROM customer AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.region = $1;

若外侧很小而内侧很大，orders(customer_id) 可能让每次探测便宜。若两侧都要读很大比例，planner 可能选择 hash join 或 merge join；此时新索引未必有价值。评审至少记录：

outer rows × inner probes
join cardinality estimate vs actual
inner predicate/order/output
available uniqueness
hash/sort memory and spill

主键或 UNIQUE 约束会创建唯一索引，PostgreSQL 不会自动为外键的引用列创建索引。外键索引的理由不是“约束要求”，而是两类真实动作：

• 从父表删除/更新 key 时，快速检查子表引用；
• 应用从子表按 parent key 查询或连接。

例如 order_item(order_id) 常常值得索引，但应由 delete/update parent 的风险和查询频率验证。不要重复创建一个已经由复合索引左前缀覆盖的 order_id 单列索引。

排序是一种可被索引提供的属性

B-tree 能按 key order 输出，planner 可在三种路径间权衡：

index path already ordered
bitmap/seq path + explicit Sort
partially ordered path + Incremental Sort

对返回大部分表的查询，顺序 index scan 仍可能产生大量随机 heap access，Seq Scan + Sort 反而更便宜。对 Top-N，索引价值通常更高，因为它可能在找到前 N 行后停止：

SELECT order_no, placed_at
FROM orders
WHERE customer_id = $1
ORDER BY placed_at DESC
LIMIT 20;

候选 (customer_id, placed_at DESC) 能在固定 customer 前缀内直接取前 20 行。若没有 ORDER BY，LIMIT 20 只是任意 20 行，不能把偶然的索引输出顺序当业务语义。

方向需要按整组 key 判断：

CREATE INDEX mixed_order_idx
ON metric (tenant_id ASC, recorded_at DESC);

单列 B-tree 可正反扫描；多列索引整体反向会同时翻转各列，因此 (tenant_id ASC, recorded_at ASC) 的反向扫描不能提供 tenant_id ASC, recorded_at DESC。NULLS FIRST/LAST 也属于 order contract。只有查询要求的 order 与一种扫描方向吻合，才可省掉 Sort。

分组、去重与窗口不能只看关键字

有序输入可能帮助 GroupAggregate、DISTINCT、merge join、窗口函数或 incremental sort，但不保证 planner 一定利用索引：

SELECT tenant_id, count(*)
FROM event
WHERE occurred_at >= $1
GROUP BY tenant_id;

如果时间范围覆盖很多行，按 (occurred_at, tenant_id) 扫描再聚合未必比 Seq Scan + HashAggregate 好；若查询需要按 tenant 分组且只读少量 tenant，另一个 key order 才可能合适。为 GROUP BY 新建索引前，要比较：

• 过滤后实际行数；
• 现有输入是否已排序；
• hash aggregate 的内存与 spill；
• sort/incremental sort 的内存、磁盘与并行；
• 最终是否还有 order/limit；
• 这个 query 的频率是否能抵消写成本。

同样，窗口函数的 PARTITION BY/ORDER BY 是完整序列需求，不是见到某列就建单列索引。

9.2.3 选择率、相关性与访问路径

选择率属于“谓词 + 值”，不只属于列

state='failed' 可能命中 0.01%，state='success' 可能命中 99%。同一 prepared query 的 hot/cold 参数可能对应完全不同的最佳路径。先看统计对 planner 描述了什么：

SELECT
    attname,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = 'shop_private'
  AND tablename = 'ch09_order_probe';

MCV 捕获常见值，histogram 描述其余分布，n_distinct 描述 distinct 规模；多列相关则需要第 7 章的 extended statistics 或更合适的数据模型。统计是抽样模型，不是精确计数，数据漂移后必须 ANALYZE，但也不能把无限提高 statistics target 当第一反应。

判断一个索引路径时，应同时看：

estimated rows vs actual rows
rows removed by filter
loops
heap blocks touched and cache hits/reads
sort/spill
result rows and correctness
parameter bucket

若 cardinality 根本错了，节点选择往往只是后果。

物理相关性改变 heap 访问代价

pg_stats.correlation 近似描述列逻辑顺序与 heap 物理顺序的相关程度。高度相关的 range scan 往往按邻近 heap page 读取；随机分布的相同行数可能触碰更多 page。相关性不是永久属性：

• append 时间列通常天然相关；
• UPDATE、乱序导入和长期 churn 会改变布局；
• CLUSTER 可重写表，但不会自动持续维持物理顺序；
• BRIN 依赖 block range summary，相关性漂移会扩大 recheck；
• partitioning 能缩小关系范围，却不等同于每个分区内部有序。

所以不能把另一个环境的 random_page_cost 或 correlation 照搬为本环境真相。

Index、Bitmap 与 Seq Scan 各有合理区间

可以用一个粗略模型理解三类路径：

PostgreSQL 能用 BitmapAnd/BitmapOr 组合多个索引。这有时让两个短索引胜过一个专用复合索引，也可能因丢失 ordering 而需要 Sort。不能据此为每列各建一个索引：组合仍有 bitmap 建立、heap recheck、排序和所有单列索引的写成本。

EXPLAIN 的 cost 是在当前统计、参数、settings 与硬件成本假设下比较候选，不是毫秒。enable_seqscan=off 之类 GUC 可以做“是否存在某路径”的诊断，不得作为让 planner 听话的长期修复。正确闭环是：

1. 用代表参数捕获 before plan 与结果；
2. 提出能从 operator/order 证明的 candidate；
3. 在相同数据与统计下捕获 after；
4. 比较 read、write、size 与生命周期；
5. 即使 after 仍选 Seq Scan，也判断其是否符合真实成本；
6. 只有收益覆盖长期代价才保留。

本章实验正是这个闭环，而不是“让四条查询都出现 Index Scan”的演示。

延伸阅读

• PostgreSQL 18：Multicolumn Indexes
• PostgreSQL 18：Indexes and ORDER BY
• PostgreSQL 18：Combining Multiple Indexes
• PostgreSQL 18：Planner Statistics
• PostgreSQL 18 Release Notes：B-tree skip scan

上一节：索引方法与操作符类 · 返回本章目录 · 下一节：表达式、部分与覆盖索引 · 查看全书目录 · 查看索引中心

9.3 表达式、部分与覆盖索引

普通索引把表列作为 key；expression、partial 与 covering index 分别回答三个更精确的问题：

expression → 查询真正比较的是否是一个规范化表达式？
partial    → 是否只有一个可在 planning time 证明的稳定子集值得索引？
INCLUDE    → 定位完成后，是否值得复制少量 payload 来避免 heap visit？

三者可以组合，但每加一层都扩大合同：查询语义必须吻合，写入必须维护更多内容，验证必须覆盖更多失效条件。

9.3.1 表达式必须与查询语义一致

索引表达式与查询表达式要能被 planner 对应

大小写无关的登录查找可以写成：

CREATE UNIQUE INDEX account_email_ci_uidx
ON account (lower(email));

SELECT account_id
FROM account
WHERE lower(email) = lower($1);

索引 key 是 lower(email)，不是原始 email。下面的查询有不同语义，不能因为“都在处理邮箱”就期待复用：

WHERE email = $1
WHERE upper(email) = upper($1)
WHERE trim(lower(email)) = trim(lower($1))
WHERE lower(email) COLLATE "C" = lower($1) COLLATE "C"

planner 能识别一些等价变换，但不会证明任意业务函数、cast 或字符串处理“效果一样”。设计时应让规范化规则只有一个权威表达：

• 在 SQL 与索引中复用同一表达式；
• 或把它做成 generated column，再查询和索引该列；
• 若它定义身份唯一性，明确原值能否保留多个展示形式；
• 通过真实 parameter、collation 与 locale 做 correctness 测试。

UNIQUE(lower(email)) 表达“规范化后不得重复”，这已是数据约束，不再只是性能。不能按 unused index 清理。

volatility 是正确性边界

PostgreSQL 要求 index definition 中用到的函数和操作符是 IMMUTABLE。原因很直接：同一行的 index key 必须在未来仍表示同一个值。依赖当前时间、会话时区、配置、外部表或可变环境的函数不能安全成为 key。

典型陷阱是：

-- placed_at 为 timestamptz；结果会受会话 TimeZone 影响
CREATE INDEX bad_daily_idx
ON orders (date(placed_at));

服务器会拒绝非 immutable 表达式。正确方案不是把自定义函数随手标成 IMMUTABLE，而是先固定业务语义：

“自然日”到底是 UTC、租户时区还是订单发生时记录的当地日期？
时区规则未来变化时，历史归属要不要变化？

如果合同是固定 UTC 日，可以用明确、可验证的 UTC 派生值；如果每租户时区不同，往往应在写入时保存业务日期或按租户和 UTC range 查询。错误声明 volatility 会让 planner 相信一个并不成立的不变量，结果可能是漏行，而不仅是变慢。

还要检查：

• collation 版本升级后的排序/相等语义；
• ICU/libc locale 差异；
• extension 或自定义函数升级；
• implicit cast 是否改变 operator/opclass；
• expression 的返回类型和长度；
• 函数 schema qualification 与受控 search_path。

表达式通常只在插入及非 HOT 更新时计算，读取可直接用已保存 key；代价因此从读侧转移到写侧。复杂表达式要同时测 CPU、WAL、index size 与 build 时间。

expression 不能修复错误的数据模型

下面这些候选要先问是否应该改模型：

lower(trim(email))
(payload ->> 'tenant_id')::bigint
date_trunc('hour', occurred_at)
coalesce(deleted_at, 'infinity')

若 JSONB key 实际是高频连接键，生成强类型列或普通列通常比反复 cast 更可审计；若“未删除”是稳定热点子集，partial predicate 可能比把 infinity 混入 key 更清晰。expression index 是精确工具，不是把所有 schema 欠账藏进 planner 的办法。

9.3.2 部分索引的谓词蕴含与参数陷阱

查询必须在 planning time 蕴含 index predicate

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

CREATE INDEX open_ticket_customer_idx
ON ticket (customer_id, created_at DESC)
WHERE state = 'open';

它有资格服务：

WHERE customer_id = $1
  AND state = 'open'

因为查询条件明确蕴含 state='open'。PostgreSQL 能处理完全匹配及少量简单不等式蕴含，例如 x < 1 可蕴含 x < 2；它没有通用定理证明器，也不会在 runtime 取到值以后再重新证明 arbitrary predicate。

因此这些看似接近的条件可能不能使用同一 partial index：

WHERE state IN ('open', 'retry')
WHERE lower(state) = 'open'
WHERE state = current_setting('app.state')
WHERE state = $1                 -- generic plan 时未知

设计 partial index 时，把 predicate 连同 query text、parameterization 和 plan mode 一起写入合同。只保存一个手工 literal 的 EXPLAIN 不够。

generic parameter 为什么是确定性反例

本章候选：

CREATE INDEX ch09_order_placed_cover_idx
ON shop_private.ch09_order_probe
    (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor)
WHERE order_status = 'placed';

literal query 可以证明 predicate：

WHERE customer_id = 42
  AND order_status = 'placed'

实验随后准备带两个参数的语句：

PREPARE ch09_order_lookup(bigint, text) AS
SELECT order_no, placed_at, amount_minor
FROM shop_private.ch09_order_probe
WHERE customer_id = $1
  AND order_status = $2
ORDER BY placed_at DESC
LIMIT 20;

在 force_custom_plan 下，planner 为本次 EXECUTE (42, 'placed') 看见具体值，可以证明并使用 partial index；在 force_generic_plan 下，它必须生成适用于任意 $2 的计划，无法假设所有值都是 placed，因此不能使用该 partial index。

psql -X -w \
  --dbname='service=pg36-admin' \
  --set=plan_mode=force_custom_plan \
  --file=static/labs/ch09/order-parameter.sql

psql -X -w \
  --dbname='service=pg36-admin' \
  --set=plan_mode=force_generic_plan \
  --file=static/labs/ch09/order-parameter.sql

force_* 只用于构造确定性 A/B，不是生产修复。生产是否得到 custom/generic plan 还受 prepared statement 执行历史、driver/pool 协议和 planner 判断影响。可选方案按语义权衡：

• 让稳定状态保留为 SQL literal，只参数化 customer；
• 使用 custom plan，但要比较 planning cost 和所有参数桶；
• 建普通索引，接受索引更大、写成本更高；
• 为不同状态使用明确的 query family；
• 若状态集合与生命周期已成为数据分区问题，重新审视 schema/partitioning。

不要通过伪造 IMMUTABLE 函数、强制全局 plan mode 或复制大量近似 partial index 绕过合同。

partial index 也会漂移

“只索引 5% 活跃行”今天很划算，若状态分布变成 70%，大小和维护成本会完全不同。定期观察：

SELECT
    c.relname,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    i.indisvalid,
    pg_get_expr(i.indpred, i.indrelid) AS predicate,
    pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
WHERE i.indpred IS NOT NULL;

partial unique index 还能表达“只在满足条件的行中唯一”，例如每个用户最多一个 active token。这是业务约束，必须给并发写入做失败测试。partial index 不是 partition：它不提供 retention、独立 vacuum、partition pruning 或 detach/drop 生命周期。

9.3.3 INCLUDE、index-only scan 与可见性图

covering 是查询与索引的共同属性

考虑：

SELECT order_no, amount_minor, placed_at
FROM orders
WHERE customer_id = $1
ORDER BY placed_at DESC
LIMIT 20;

候选：

CREATE INDEX orders_customer_time_cover_idx
ON orders (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor);

customer_id, placed_at 是 search/order key；order_no, amount_minor 只是 payload：

• 它们不参与 B-tree 定位或排序；
• unique index 的唯一性只作用于 key，不包括 INCLUDE；
• payload 可以是 access method 不理解的类型，因为只需原样保存；
• query 若再读取一个未保存列，便不再 covered。

PostgreSQL 14–18 中 B-tree 总能支持 index-only scan；GiST/SP-GiST 只在部分 opclass 上能重建原值，GIN 不能。INCLUDE 本身只受支持它的 access method 接受，不能把“有 INCLUDE”与“本次一定 Index Only Scan”等同。

为什么仍可能访问 heap

MVCC 可见性信息不保存在每个 index tuple 中。执行器必须确认当前 snapshot 下 heap tuple 是否可见；只有对应 heap page 的 visibility map all-visible bit 已设置，才能跳过 heap。

所以 index-only scan 有两层条件：

query 所需值都能从 index 得到
AND
目标 heap page 对当前机制可由 visibility map 证明 all-visible

EXPLAIN (ANALYZE, BUFFERS) 中的：

Heap Fetches: 0

是这次执行没有回 heap 的证据，不是索引永久保证。INSERT/UPDATE/DELETE 会清除相关 page 的 all-visible bit，VACUUM 在满足条件后再设置。高 churn 表即使 covered，也可能频繁 heap fetch；为一次 benchmark 手动 VACUUM 只能证明静态上限，不能模拟生产稳态。

本章在候选创建后执行受控 VACUUM (ANALYZE)，订单和库存 after plan 都要求 Index Only Scan + Heap Fetches=0。这条断言只属于确定性 fixture；生产验收要在真实写入、autovacuum 和 snapshot 条件下看 heap fetch 比例。

payload 不是免费的

增加 INCLUDE 会：

• 复制 payload，增大 leaf tuple、index size 与 cache footprint；
• 增加 INSERT/UPDATE 的 WAL 和维护；
• 更新 included column 时需要维护该索引，也会影响 HOT；
• 让 build、backup、restore、replication 和 vacuum 多付成本；
• 宽值可能超过 index tuple 大小上限，导致写入失败；
• B-tree 只要有 non-key column，就不会使用 deduplication。

虽然 B-tree upper level 会移除 non-key payload，使导航层保持较小，leaf 层成本仍真实存在。不要 INCLUDE (*)，也不要为了“可能以后少一次 heap visit”复制 JSON、正文或频繁变化的状态。

一个可保留的 covering candidate 应同时满足：

1. declared query 高频且返回列稳定、窄；
2. 定位 key 与 ordering 已正确；
3. 实际 plan 使用 index-only，而非只在理论上可用；
4. 真实 VM/all-visible 状态下 heap fetch 明显减少；
5. size/cache/write/WAL/HOT 代价可接受；
6. payload 变化不会让维护成本压过读取收益；
7. 不与另一个更短索引形成无意义重叠。

如果表频繁更新或 query 本来就要访问 heap 中的宽列，普通短索引往往更好。

延伸阅读

• PostgreSQL 18：Indexes on Expressions
• PostgreSQL 18：Partial Indexes
• PostgreSQL 18：Index-Only Scans and Covering Indexes
• PostgreSQL 18：Function Volatility Categories
• PostgreSQL 18：Visibility Map

上一节：从谓词、连接与排序推导索引 · 返回本章目录 · 下一节：索引也有写入和生命周期成本 · 查看全书目录 · 查看索引中心

9.4 索引也有写入和生命周期成本

索引把一次读取节省的工作，变成所有相关写入都要长期承担的工作。一份完整收益表至少有两边：

read benefit:
  fewer heap/index blocks
  no sort or earlier LIMIT stop
  better latency/throughput/tail

lifetime cost:
  insert/update/delete CPU and latency
  extra index pages and cache displacement
  WAL, archive, backup and replication
  vacuum/analyze/build/reindex
  lock, disk peak and failed-build recovery
  lost HOT opportunities

只保存 after query 的执行时间，等于只记收益、不记负债。

9.4.1 写放大、缓存占用与 WAL

一次逻辑写会触碰多少物理结构

插入一行时，heap、每个相关 index、visibility/free-space metadata 和 WAL 都可能变化。更新在 MVCC 下创建新 row version；若不满足 HOT，它还要为各索引写新 tuple。删除先留下 dead version，之后 vacuum 才清理 heap/index 可回收空间。

索引越多，常见代价越大：

• 更多 access method/operator expression 计算；
• 更多 buffer 被读入、锁定并标脏；
• B-tree page split、GIN pending list、BRIN summary 等各自维护；
• 更多 WAL 传到 archive、streaming replica 与 logical decoding；
• checkpoint 写出更多 dirty page；
• base backup、restore、pg_upgrade --link 之外的重建与磁盘巡检范围更大；
• autovacuum/index cleanup 和故障修复窗口更长。

这不是说“索引数量越少越好”，而是每个索引都必须有消费者和证据。

用同一条写路径量化

PostgreSQL 可直接给 data-changing statement 取执行证据：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    FORMAT JSON
)
UPDATE counter
SET value = value + 1
WHERE bucket_id BETWEEN 1 AND 1000;

EXPLAIN ANALYZE 会真的执行写语句。安全实验应使用专属 fixture，或在能够完全回滚且不涉及 sequence/外部副作用的事务中运行。生产不能为了看 plan 对一条未知 DML 随手加 ANALYZE。

比较前后至少保存：

result/affected rows
execution time distribution, not one sample
shared/local/temp buffer hits/reads/writes
WAL records/FPI/bytes
table/index sizes
TPS and concurrent read/write latency
replica WAL receive/replay lag
checkpoint and I/O pressure

WAL bytes 会受 full-page image、checkpoint 时点、page 初始状态、compression 与版本影响，不能把本机某个精确数值写成阈值。A/B 的稳定断言通常是方向和相对幅度，并要重复、交替顺序。

索引大小也是 cache 决策

查看关系分解：

SELECT
    relid::regclass AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS heap,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes,
    pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

查看单个索引：

SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS bytes,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relid = 'public.orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

一个 9 GB 索引不等于需要 9 GB shared_buffers，操作系统 page cache 也参与；但热 working set 彼此竞争是真实的。增加大索引可能让某条 query 更快，却把另一条热路径的数据页挤出 cache。Pigsty 的 table/index、buffer、I/O 和 instance 指标应在同一时间窗关联，而不是孤立看 idx_scan。

本章事件候选正是空间决策：400000 行物理时间相关数据上，BRIN 为 24576 bytes，对照 B-tree 为 9003008 bytes，比例约 0.00273。字节值只属于本次 fixture；保留 BRIN、拒绝 B-tree 的理由是 declared range workload 不需要为额外精度和 cache footprint 付费。

9.4.2 HOT 更新、页分裂与填充因子

HOT 省掉什么

Heap-Only Tuple update 让新 row version 留在旧 row 所在 heap page，并沿 page 内 HOT chain 查找，因此无需为该更新创建普通 index tuple。它同时减少索引写入和以后清理旧 index entry 的负担。

在 PostgreSQL 16–18，HOT 的关键条件可表述为：

1. 新 tuple 能放进旧 tuple 所在 heap page；
2. 更新没有改变任何 non-summarizing index 引用的列。

这里“引用”包括 key、expression、INCLUDE payload 和 partial-index predicate。核心 BRIN 是 summarizing access method；PostgreSQL 16 起，如果只改变 BRIN-indexed key，仍可允许 HOT，但若改变 partial predicate 引用列仍会阻止 HOT。

版本边界：PostgreSQL 14–15 还没有“只更新 BRIN 列仍可 HOT”的改进，应按更保守的规则理解：更新任何索引引用列都会阻止 HOT。它是 PostgreSQL 16 引入的能力，不能回写到整个 14–18 范围。

监控：

SELECT
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    CASE
      WHEN n_tup_upd = 0 THEN NULL
      ELSE n_tup_hot_upd::numeric / n_tup_upd
    END AS hot_ratio
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

统计是累计观测，先记录 reset epoch 和时间窗；不同写 workload 混在一起时，整体 ratio 不能解释某条 UPDATE。

本章 HOT/WAL 对照

两个 50000 行表结构、数据和 table fillfactor=50 相同，唯一差别是：

CREATE INDEX ch09_write_indexed_counter_idx
ON shop_private.ch09_write_indexed (volatile_counter);

随后分别执行：

UPDATE ... SET volatile_counter = volatile_counter + 1;

一次 PostgreSQL 18.6 实测：

without volatile index:
  HOT ratio = 1.0
  WAL bytes = 11257432

with volatile index:
  HOT ratio = 0
  WAL bytes = 11631392

精确 WAL 会漂移；稳定关系是：

相同更新 + 同样预留 page space
  → 未引用 volatile_counter 的索引集合允许 HOT
  → 把 volatile_counter 放入普通 B-tree 后 HOT 消失
  → 本次 statement WAL 增加

这个 counter 没有 declared read query，所以候选被拒绝。若未来确有关键 point lookup，评审要比较读取收益与 HOT/WAL 代价，而不是把“阻止 HOT”当绝对禁令。

table fillfactor 与 index fillfactor 不同

降低 table fillfactor 会在 heap page 预留空间，提高后续 row version 留在同页、形成 HOT 的机会：

ALTER TABLE hot_account SET (fillfactor = 80);

它不会把现有 page 自动重写成 80% 装载；要等待 churn 或受控重写，并承担表更大、顺序扫描更多 page 的代价。

降低 B-tree index fillfactor 则在 build 时给 leaf page 留空间，可能减少后续 insertion/page split，但会让索引更大、cache density 更低。它不创造 HOT 所需的 heap page 空间。两个同名参数作用在不同结构，不能混为一谈。

page split 不是“索引损坏”，是 B-tree 正常维护；真正要评估的是：

• insert key 是否随机、单调或集中在热点；
• page split/WAL 与 tail latency 是否成为问题；
• 低 fillfactor 的空间成本是否值得；
• REINDEX CONCURRENTLY/重建是否有真实 bloat 证据；
• 去重、key width 与 payload 是否可优化。

不要把周期性重建所有索引当保养仪式。

9.4.3 重复、未使用与失效索引的判断

idx_scan=0 只能生成调查清单

pg_stat_user_indexes.idx_scan=0 不能单独授权 DROP INDEX，因为它可能表示：

• statistics 刚 reset，观察窗太短；
• rare but critical 月结、故障切换或合规查询尚未发生；
• 该索引只在 replica 被读，primary 本地统计看不到；
• planner 用另一条等价路径只是暂态；
• 它支撑 PRIMARY KEY、UNIQUE、exclusion constraint；
• 它用于 foreign-key parent delete/check 或运维任务；
• 它是 logical replication 的 replica identity；
• 应用版本/feature flag 尚未完整覆盖；
• partition child 各自 workload 不同；
• 统计语义和计数方式在版本间有差异。

至少把数据库统计 reset 时点一并保存：

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

然后覆盖一个能代表周、月、批处理和故障流量的时间窗，并查 primary、read replicas 与 query history。

“重复”要比较完整定义与职责

两个索引列名相似，不代表重复。审查结构至少包含：

access method
key expressions and order
operator classes and collations
ASC/DESC and NULLS
partial predicate
INCLUDE payload
unique/nulls-not-distinct/exclusion semantics
valid/ready/live state
partition attachment
constraint and replica-identity ownership

可先取 catalog：

SELECT
    i.indexrelid::regclass AS index_name,
    am.amname,
    i.indisunique,
    i.indisprimary,
    i.indisexclusion,
    i.indisreplident,
    i.indisvalid,
    i.indisready,
    i.indislive,
    pg_get_indexdef(i.indexrelid) AS definition,
    pg_get_expr(i.indpred, i.indrelid) AS predicate
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
JOIN pg_am AS am
  ON am.oid = c.relam
WHERE i.indrelid = 'public.orders'::regclass;

(a, b) 可以支持一部分 a lookup，但不等价于 (a)：它更宽，可能有不同排序/payload/uniqueness，也可能让短索引更适合 cache。反过来，若所有 (a) consumers 都被 (a,b) 等价覆盖，短索引才进入候选合并清单。必须用 before/after workload 验证。

INVALID 是状态，不是自动删除理由

并发创建过程中，catalog 会先出现尚未 valid 的 index。失败可能留下：

indisvalid = false
indisready = true or false depending on failed phase

某些 INVALID index 仍会被写路径维护，却不能被查询采用；它既有成本又没有读收益，需要处置。但先确认：

• 是否仍有合法 build/reindex 正在运行；
• index 与 table 的 exact OID/schema/name；
• 是否由 constraint/partition operation 管理；
• 失败 SQLSTATE、phase 与原始 DDL；
• 是否已有人在做恢复；
• drop/recreate 的锁、磁盘、唯一性与 replica 风险。

本章用故意重复数据执行 CREATE UNIQUE INDEX CONCURRENTLY，要求：

SQLSTATE 23505
  → catalog 观察到 exact INVALID unique index
  → 保存定义、大小与 flags
  → DROP INDEX CONCURRENTLY exact schema-qualified target
  → remaining=0

这不是“定时删除所有 INVALID”的脚本模板。生产应由变更单绑定 exact identity、owner、证据和回退；若失败对象承担约束语义，还要先恢复约束正确性。

删除本身也要 A/B 和回退

安全清理流程是：

1. 生成 candidate，不执行 drop；
2. 排除 constraint、replica identity、partition 与 rare critical consumers；
3. 保存完整 definition、owner、size、usage epoch 和依赖；
4. 在可代表的 primary/replica workload 中验证替代路径；
5. 评估 DROP INDEX 或 DROP INDEX CONCURRENTLY 的限制与窗口；
6. 一次处理少量对象，观察 query latency、CPU/I/O 与 write；
7. 保留可审计的 recreate DDL 与停止条件。

索引生命周期的终点不是“catalog 更干净”，而是正确性不变、关键 SLO 不退化、写入和空间确有改善。

延伸阅读

• PostgreSQL 18：Heap-Only Tuple Updates
• PostgreSQL 16 Release Notes：BRIN-indexed columns and HOT
• PostgreSQL 18：Monitoring Statistics
• PostgreSQL 18：System Catalog pg_index
• PostgreSQL 18：Routine Reindexing

上一节：表达式、部分与覆盖索引 · 返回本章目录 · 下一节：验证而不是“加完就快” · 查看全书目录 · 查看索引中心

9.5 验证而不是“加完就快”

“建完出现 Index Scan”只能证明 planner 在一次条件下选了它。索引验收必须回答四组问题：

只要其中一列为空，结论就是 candidate，不是可上线变更。

9.5.1 计划、缓冲区、延迟分布与写入代价

保存机器可读 before/after

对可安全执行的只读查询：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    SUMMARY,
    FORMAT JSON
)
SELECT ...;

JSON 便于保留完整 node tree 并自动断言。证据包还要保存：

query identity and exact text
representative parameter bucket
result row count or semantic fingerprint
server version/database/role
relevant settings
table/index definitions and sizes
ANALYZE/statistics timestamp or snapshot
capture UTC time and workload window

阅读计划时按因果顺序：

1. root actual rows 与业务结果是否正确；
2. 每个节点 Plan Rows/Actual Rows × Actual Loops 是否偏离；
3. predicate 是 Index Cond、Recheck Cond 还是 Filter；
4. 是否有 Rows Removed by Filter；
5. shared/local/temp blocks 的 hit/read/dirtied/written；
6. sort method、memory、disk spill；
7. index-only 的 Heap Fetches；
8. planning 与 execution time；
9. 写语句的 WAL records/FPI/bytes。

节点名不是最终 KPI。Index Scan 读取大量随机 heap page 可能比 Seq Scan 慢；Bitmap Heap Scan 带 recheck 可能正是最合理路径；BRIN 本来就是 lossy。稳定结论来自结果、资源与延迟关系。

EXPLAIN ANALYZE 增加测量开销，且对 DML 会执行真实写入。节点很多时可用 TIMING OFF 降低逐节点计时开销，但不能消除 instrumentation 本身。调查生产 DML 时优先看已有 pg_stat_statements、日志、采样计划和 replica/L1 重放，不要直接执行未知副作用。

单次 elapsed 不是延迟分布

一次 warm-cache、单连接执行无法代表：

p50 / p95 / p99
throughput
queueing under concurrency
hot/cold parameter mix
lock and I/O interference
planning overhead

pg_stat_statements 可提供 query family 的 calls、总/均值执行时间、rows、block 与 WAL 累计；它不是逐请求 percentile 存储。tail latency 应来自应用 tracing、指标 histogram 或负载工具，并与相同 query identity 和时间窗关联。

索引可能把 hot parameter 从 2 s 降到 20 ms，却让占 99% 流量的写入多 10%；也可能只优化 cache 已热的 microbenchmark。评审要用 traffic weight 算总体收益：

weighted read benefit
  = Σ(query frequency × latency/resource delta)

weighted write cost
  = Σ(write frequency × latency/WAL/resource delta)

公式不要求伪装成精确货币值，作用是迫使评审记录频率，而不只比较最好看的样本。

Pigsty 提供时间窗，SQL/catalog 提供语义

在 Pigsty 中，把同一 UTC 窗口的观测串起来：

query family latency/calls/rows
  → table/index scans and tuple fetches
  → instance CPU/load/memory
  → PostgreSQL buffer and system I/O
  → WAL generation/archive
  → replica receive/replay lag
  → locks/long transactions/autovacuum

不同 Pigsty 版本的仪表盘名称和布局会变化，本书不冻结点击路径；以当前 PostgreSQL Dashboard 文档 和实际变量为准。面板负责说明“何时、影响多大”，最终仍要落回：

• query text/parameters；
• EXPLAIN 与统计估算；
• pg_index/pg_class definition 与 validity；
• WAL、锁和 replica 证据；
• correctness/SLO 验收。

没有 query identity 的 CPU 曲线不能证明某个索引有效；没有时间窗的 plan 也不能证明它解释了事件。

9.5.2 数据规模和缓存状态一致的 A/B 对照

一次只改变候选索引

一个可复核 A/B：

same PostgreSQL major/minor and settings
same schema/data/statistics
same SQL/parameter/result
same connection protocol and plan mode
same cache category and run order
same concurrency/background workload
only candidate index differs

推荐流程：

1. 固定 query family、参数分桶和 SLO；
2. 保存目标 relation checksum/row counts 与 baseline catalog；
3. ANALYZE 后捕获 before plan；
4. 创建一个 candidate，等待/执行与生产可比的统计和 vacuum 条件；
5. 捕获 after plan；
6. 交替执行 A/B 或在等价环境重复，避免永远 before 冷、after 热；
7. 验证结果集合、顺序与业务不变量；
8. 测同样的 read concurrency；
9. 测代表性的 INSERT/UPDATE/DELETE 与 WAL/HOT；
10. 给出 retain、merge 或 reject，拒绝对象也要清理并验证。

不能在同一生产表上随意来回 drop/create 只为跑 A/B。可选机制包括独立 L1 clone、可恢复 staging、同数据快照、hypothetical index 作早期筛选，以及受控 shadow workload；真正上线前仍要用真实索引验证 build 和写成本。

cache 不是“清掉才公平”

至少区分：

• cold-ish：工作集尚未被本次 query 预热；
• warm：稳定重复访问后；
• mixed/production：与其他 workload 共同竞争 cache。

不要在共享服务器用 Linux drop_caches，它会全局影响其他进程且仍不能模拟真实 workload；重启 PostgreSQL 也改变连接、checkpoint、background worker 等大量变量。DISCARD ALL 只清会话状态，不清 shared buffers 或 OS page cache。

更可靠的方法是：

• 独立 disposable 实例做受控 cold 测试；
• A/B 交替顺序并多轮；
• 报告 buffers 的 hit/read，而不是只说“冷/热”；
• 在生产相似 mixed workload 中验证 cache displacement；
• 不把首次 build 后的缓存副作用算成稳态收益。

数据与参数必须能代表真实分布

小表上 Seq Scan 合理，大表才出现索引价值；全均匀合成数据会掩盖 hot tenant、MCV、相关性与 null skew。fixture 应固定并公开：

row count and width
distinct/MCV/null distribution
physical correlation
representative hot/cold values
target result cardinality
write/update distribution

本章故意设置：

• 订单 placed=5% 且目标 customer 返回 10 行；
• 库存只有 30 个 warehouse，暴露 PG18 skip scan；
• 搜索目标命中 100/100000；
• 事件按时间物理写入，目标范围 600/400000；
• 两个 write twin 完全等价，只改变 volatile index。

这些数字让机制可重复，不宣称代表每个生产库。迁移结论前，用真实 pg_stats、query 参数桶和 workload 重做。

optimizer GUC 只能用于反事实

enable_seqscan=off、enable_bitmapscan=off、force_custom_plan 可回答：

“候选路径是否存在？”
“若看见具体参数，估算/路径是否改变？”

它们不能证明强制路径在生产更快，更不能作为全局长期修复。实验必须把 SETTINGS 保存到 plan，避免一个被强制出来的节点冒充自然选择。本章只用 plan_cache_mode 构造 partial/generic 的语义反例，最终 candidate plan 仍由正常 cost model 验收。

9.5.3 线上创建、失败回收与监控窗口

普通与 concurrent build 的真实差别

普通：

CREATE INDEX orders_customer_time_idx
ON orders (customer_id, placed_at DESC);

可在一次 table scan 中完成，通常比 concurrent build 更快、更省总工作；构建期间允许普通读取，但会阻塞会修改该表的写入。因此适合维护窗口、空表、新分区或能明确停写的场景。

并发：

CREATE INDEX CONCURRENTLY orders_customer_time_idx
ON orders (customer_id, placed_at DESC);

不会用同样方式阻塞日常 INSERT/UPDATE/DELETE，但它绝不是“无锁、无影响”：

catalog 创建 INVALID index
  → 等待可能修改表的旧事务
  → 第一次 table scan/build
  → index becomes ready for new writes
  → 等待旧 snapshot
  → 第二次 table scan/validate
  → mark valid

它要做两次扫描，持续更久，并产生 CPU、I/O、WAL、磁盘和 replica 压力；长事务/旧 snapshot 可让某个 phase 长时间等待。一个表同一时刻只能有一个 concurrent index build。命令不能放在 transaction block 内。

唯一索引还有额外边界：在第二次扫描开始时，系统已可能对其他事务执行 uniqueness enforcement；其他 session 可能在该索引正式 valid 前收到 uniqueness violation。若 build 最终失败，INVALID 对象仍可能继续执行唯一性检查。上线前必须先做 duplicate preflight，并理解这段时间的应用错误语义。

对 partitioned table，PostgreSQL 18 仍不支持直接 concurrent build 整个 partitioned index。可以在各 leaf partition 上分别 CREATE INDEX CONCURRENTLY，再用短暂的 parent metadata 操作 attach/建立 partitioned index；具体 DDL、锁和失败恢复必须在目标版本演练。

开始前定义水位与停止线

生产变更单至少写：

exact schema/table/index definition and owner
query evidence and expected benefit
table/index current size and growth
free disk plus build/recovery peak
CPU/I/O/WAL/replica-lag ceilings
long transaction and lock preflight
statement/lock timeout policy
connection/session survivability
progress and alert owner
abort criteria
INVALID cleanup/retry plan
after correctness/read/write acceptance

“磁盘够放最终索引”不等于够用：并发 build、WAL、temp、失败对象和 replica 都可能需要峰值空间。也不要让一个普通应用连接在不可控 timeout、pool recycle 或网络中断下承担数小时 DDL。

用 progress view 观察阶段，不猜百分比

SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    index_relid::regclass AS index_name,
    command,
    phase,
    lockers_total,
    lockers_done,
    current_locker_pid,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done,
    partitions_total,
    partitions_done
FROM pg_stat_progress_create_index;

不同 phase 只有部分计数有意义，blocks_done/blocks_total 不能代表整个 concurrent lifecycle 的统一完成率。要同时查：

• pg_stat_activity 的 session identity、state/wait event；
• pg_locks 与 exact blocker edge；
• long transaction/snapshot；
• host and PostgreSQL I/O；
• WAL/archive/replica lag；
• target index pg_index flags 与 size。

Pigsty 负责把这些指标放入统一时间轴，catalog/progress view 决定当前语义。取消也只能针对 PID + backend_start + database + application/DDL identity 精确命中；不能看到“建索引慢”就取消任意 backend。

失败后先辨认状态，再精确回收

检查：

SELECT
    i.indexrelid::regclass AS index_name,
    i.indisunique,
    i.indisready,
    i.indisvalid,
    i.indislive,
    pg_relation_size(i.indexrelid) AS bytes,
    pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index AS i
WHERE i.indrelid = 'public.orders'::regclass;

若确认是本次失败遗留且无约束/partition/其他 owner 依赖，按 exact schema-qualified identity 回收：

DROP INDEX CONCURRENTLY public.orders_customer_time_idx;

DROP INDEX CONCURRENTLY 也有约束：不能放进 transaction block，不能配 CASCADE，且 partitioned parent 有额外限制。失败对象是否 drop、reindex 或重新 build 取决于 phase、依赖和变更计划，不能用全库 WHERE NOT indisvalid 自动删除。

本章 failure injection 让 5000 对重复 key 触发 SQLSTATE 23505，先把 INVALID 的 flags/size 保存为证据，再精确 drop，最后断言同名对象为 0。这才是可复核的失败闭环。

延伸阅读

• PostgreSQL 18：Using EXPLAIN
• PostgreSQL 18：CREATE INDEX
• PostgreSQL 18：CREATE INDEX Progress Reporting
• PostgreSQL 18：pg_stat_statements
• Pigsty：PostgreSQL Dashboards

上一节：索引也有写入和生命周期成本 · 返回本章目录 · 下一节：实战：为订单、库存与搜索入口设计索引 · 查看全书目录 · 查看索引中心

9.6 实战：为订单、库存与搜索入口设计索引

本节把前五节合并成一个完整评审：

4 个读取 query family
  → 5 个真实 candidate
  → before/after plans + result assertions
  → HOT/WAL twin
  → concurrent unique failure injection
  → retain/reject ledger
  → final catalog/model/state verification
  → PREF-PLAN-005 candidate evidence

实验只在带 marker 的 shop_private.ch09_* fixture 上执行。它不会触碰 shop 业务表的索引，也不会模拟生产点击动作。

9.6.1 从真实查询清单提出候选索引

先确认目标与身份

准备一个只包含本地 L1 凭据、权限为 0600 的 service file：

[pg36-admin]
host=/absolute/socket/or/host
port=5432
dbname=pg36_shop
user=...

然后：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin

psql -X -w \
  --dbname='service=pg36-admin application_name=pg36-ch09-preflight' \
  --command="
    SELECT current_database(), current_user,
           current_setting('server_version'),
           pg_is_in_recovery();
  "

只在已确认可写、可重建的 L1/本地数据库继续。脚本还会执行 ch05 model verification，要求数据库、schema、角色、fixture marker 与业务 checksum 均符合前章合同。没有 PGSERVICEFILE、action 非法或 context 不符时 fail closed。

下载并阅读：

• 实验合同
• 任务入口
• 确定性 fixture
• 上下文 guard
• 计划上下文

setup 在七个同名关系全部缺失，或全部带精确 marker 时重建；任何同名异物都会拒绝。它属于 R1 fixture rebuild，不需要 reset token，但只能在 disposable L1 使用。

cd static/labs/ch09
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/candidates-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh candidates

四份 query contract

实验不是先给答案，而是先保存 before：

原始查询分别在：

• 订单
• 订单 custom/generic 参数
• 库存
• 全文检索
• 事件范围

候选由 create-candidates.sh 以独立的 CREATE INDEX CONCURRENTLY 创建：

-- 订单：状态在 predicate，customer/time 为 key，窄返回列为 payload
CREATE INDEX CONCURRENTLY ch09_order_placed_cover_idx
ON shop_private.ch09_order_probe
    (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor)
WHERE order_status = 'placed';

-- 库存：反转已有 PK 的查询方向
CREATE INDEX CONCURRENTLY ch09_inventory_sku_cover_idx
ON shop_private.ch09_inventory_probe
    (sku_id, warehouse_id)
INCLUDE (available, reserved, updated_at);

-- 搜索：query 与 generated document 使用相同全文语义
CREATE INDEX CONCURRENTLY ch09_search_document_gin_idx
ON shop_private.ch09_search_probe
USING gin (search_document);

-- 事件：对物理相关范围保存 block summary
CREATE INDEX CONCURRENTLY ch09_event_occurred_brin_idx
ON shop_private.ch09_event_probe
USING brin (occurred_at)
WITH (pages_per_range = 32, autosummarize = on);

创建后对专属 fixture 做 VACUUM (ANALYZE)，再捕获 after。这里 vacuum 是为了制造稳定的 all-visible 教学条件；生产 index-only 收益必须按真实 autovacuum/churn 复测。

候选要允许被拒绝

event B-tree 也是实际创建的 candidate：

CREATE INDEX CONCURRENTLY ch09_event_occurred_btree_idx
ON shop_private.ch09_event_probe (occurred_at);

脚本保存它的 plan 与 size 后，证明 declared workload 已由小得多的 BRIN 满足，于是按 exact table/index identity 执行：

DROP INDEX CONCURRENTLY
    shop_private.ch09_event_occurred_btree_idx;

“创建成功并被使用”不等于必须保留。能输出 reject 且清理干净，是索引设计实验的重要能力。

9.6.2 在 Pigsty L1 保留、合并或拒绝并记录证据

一键运行完整闭环

cd static/labs/ch09
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

执行顺序：

manifest + ch05 preflight
  → marker-guarded fixture rebuild
  → four before plans
  → four retained candidates + VACUUM
  → after + custom/generic plans
  → event B-tree comparison and exact rejection
  → HOT/WAL twin updates
  → concurrent unique failure and exact recovery
  → final fixture/catalog/worker/model verify
  → semantic analyzer + rule-proposal validation

PG36_EVIDENCE_DIR 应使用每次唯一的目录。脚本以 umask 077 创建证据，不覆盖旧结果；manifest.txt 保存 server/client/Python 版本、target identity 与所有 source hash。

一次 PostgreSQL 18.6 实测输出：

status=ok
order=partial-covering/index-only/custom:true/generic:false/rows:10
inventory=reverse-covering/heap-fetches:0/rows:30
search=gin/rows:100
event=brin-retained/btree-rejected/size-fraction:0.00273
write=hot:1.0->0/wal:11257432->11631392
concurrent=23505/invalid-observed/exact-drop/remaining:0
decisions=retain:4/reject:4
proposal=0.1.0->0.4.0/PREF-PLAN-005/depends-on-v0.2+v0.3
final=workers:0/rejected:0/checksum:f8a7bfae59c6d16cd323abecfefe1014

不要把 WAL 字节、cost、elapsed、buffer 个数或 exact node tree 当 golden。分析器只断言：

• 结果行数与语义不漂移；
• literal/custom 能用 partial，generic status parameter 不能；
• 订单/库存 after 是 index-only 且 heap fetch 为 0；
• 搜索使用专属 GIN；
• event BRIN/B-tree 都能返回 600 行，BRIN 至少小一个数量级；
• unindexed volatile update 有高 HOT ratio，indexed twin 为 0 且 WAL 更多；
• unique concurrent build 以 23505 失败，INVALID 被观察并精确删除；
• rejected index=0、worker=0、业务 checksum 不变。

PG18 before inventory 在本机使用 warehouse-first PK skip scan。分析器记录该事实但不要求 exact node：PG14–17 没有该能力，PG18 也可能因 cost/data 改选其他路径。

读、写、失败三类 artifact

证据目录包含：

manifest.txt
preflight.txt
setup.txt

order-before.json
order-after.json
order-custom.json
order-generic.json
inventory-before.json
inventory-after.json
search-before.json
search-after.json
event-before.json
event-brin.json
event-btree.json

catalog-before-rejection.csv
catalog-final.csv
write-base.json
write-indexed.json
write-stats.csv

concurrent-failure/
  create.stdout
  create.stderr
  invalid-index.csv
  drop.stdout
  drop.stderr
  summary.txt

verify.txt
index-summary.json
index-summary.txt

raw plan/catalog 用于复核，index-summary.json 用于稳定关系，不能只保留最后一行 status=ok。

4 个保留，4 个拒绝

index-decisions.json 为每个结论绑定 query 和 artifact：

本 fixture 没有需要 merge 的 pair，但生产 ledger 应允许 merge：例如一个较完整候选在验证后替代两个真正重叠索引。merge 仍需先排除 constraint/replica identity，并验证所有 consumer，不能只做字符串前缀比较。

在 Pigsty 中复核相同关系

实验运行时或生产 shadow 验证时，在同一 UTC 窗口观察：

Query:
  calls, rows, mean/tail, shared/temp blocks, WAL

Table/Index:
  seq/index scans, tuple fetch, relation/index size,
  HOT/update/vacuum behavior

Instance:
  CPU, load, memory, disk IOPS/latency, checkpoint

Replication:
  WAL rate, archive status, receive/replay lag

Session/Lock:
  build application_name, phase, waits, long transactions

面板结论回链 manifest + plan JSON + catalog CSV + query identity。L1 的“retain”是机制验收，不自动授权生产上线；生产必须另建时间窗、磁盘/replica 水位、审批与回退。

9.6.3 将索引审查规则追加到规约

从“应验证计划”升级为可运行规则

baseline-v0.4-proposal.json 不改写第 6 章的不可变 v0.1 baseline，而是为 PREF-PLAN-005 追加 candidate evidence：

索引候选必须绑定真实 query/parameter/order/return shape；
保存 before/after plan、结果、buffers、大小、写/HOT/WAL 代价；
记录 concurrent build 失败回收；
明确 retain/merge/reject。

提案绑定三层 provenance：

base:
  0.1.0 canonical checksum

dependencies:
  ch07 v0.2 proposal canonical checksum
  ch08 v0.3 proposal canonical checksum

candidate:
  0.4.0 / PREF-PLAN-005 / ch09 evidence paths

analyze_indexes.py 每次 all/review 都重新 canonicalize JSON 并核对 checksum、依赖顺序、rule id 和 artifact existence。只改 proposal 中的声明、不同步依赖内容会 fail；这避免一条后续规则悄悄引用已漂移的前章证据。

它仍标记为：

{
  "candidate_baseline": "0.4.0",
  "status": "candidate"
}

只有以下条件完成后才可晋升：

1. PostgreSQL 14–18 compatibility matrix 通过并保留 PG18 skip-scan 差异；
2. 至少一个真实 Pigsty workload 窗口复测 read/write/build 水位；
3. 先审查并晋升依赖的 v0.2/v0.3；
4. 生成新的不可变 release artifact 和 canonical checksum。

章节成功不等于治理基线已发布。

reset 需要两个独立确认

all 最终保留七个 ch09 fixture 和四个 retained candidate，便于复核。若要删除，只在已确认的 L1 执行 R2：

cd static/labs/ch09
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/reset-$(date -u +%Y%m%dT%H%M%SZ)"

export PG36_RESET_TOKEN=RESET_CH09_INDEX_LAB
export PG36_RESET_TARGET=pg36_shop/shop_private/ch09
./task.sh reset

两个 token 各自防一类误操作：

• action token 证明调用者明确要求 reset；
• target token 绑定 database/schema/chapter。

脚本还会逐一核对 marker；同名异物存在时，即使 token 正确也拒绝。成功后必须看到：

status=ok
reset_target=pg36_shop/shop_private/ch09
remaining_ch09_relations=0

并再次运行 ch05 verification，业务 checksum 仍为：

f8a7bfae59c6d16cd323abecfefe1014

负向验收同样重要：空 token、错 action token、错 target、无 service file 和非法 action 都必须非零退出，并保持对象与业务 checksum 不变。

最终复现清单

# shell/Python/JSON 静态检查
bash -n static/labs/ch09/*.sh
PYTHONPYCACHEPREFIX=/tmp/pg36-pycache \
  python3 -m py_compile static/labs/ch09/analyze_indexes.py
python3 -m json.tool static/labs/ch09/index-decisions.json >/dev/null
python3 -m json.tool static/labs/ch09/baseline-v0.4-proposal.json >/dev/null

# 完整机制验收
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch09/final-$(date -u +%Y%m%dT%H%M%SZ)"
static/labs/ch09/task.sh all

# 独立状态验收
static/labs/ch09/task.sh verify

验收通过后，团队得到的不是“索引速查表”，而是一套可以迁移到真实 query review 的方法：先证明 operator/predicate/order，再证明收益覆盖写入与生命周期成本，最后让 retain/reject 都有可审计证据。

上一节：验证而不是“加完就快” · 返回本章目录 · 下一章：顾此失彼：并发控制与隔离异常 · 查看全书目录 · 查看索引中心