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

LLMS 索引： [llms.txt](/llms.txt)

---

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

```text
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 从真实查询清单提出候选索引 {#item-9-6-1}

### 先确认目标与身份

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

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

然后：

```bash
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。

下载并阅读：

- [实验合同](/labs/ch09/lab-contract.md)
- [任务入口](/labs/ch09/task.sh)
- [确定性 fixture](/labs/ch09/setup.sql)
- [上下文 guard](/labs/ch09/context.sql)
- [计划上下文](/labs/ch09/plan-context.sql)

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

```bash
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：

| family | predicate/order/result | fixture 特征 | 候选 |
|---|---|---|---|
| order | customer equality + literal placed + time DESC Top-N；10 行 | 200000 行，placed 5% | partial B-tree + INCLUDE |
| inventory | SKU equality + warehouse order；30 行 | 300000 行，30 warehouses，现有 warehouse-first PK | reverse covering B-tree |
| search | generated `tsvector @@ tsquery`；100 行 | 100000 行，同一 `simple` config | GIN |
| event | 10 分钟 timestamptz range；600 行 | 400000 行，物理时间相关 | BRIN；另建 B-tree 对照 |

原始查询分别在：

- [订单](/labs/ch09/order-query.sql)
- [订单 custom/generic 参数](/labs/ch09/order-parameter.sql)
- [库存](/labs/ch09/inventory-query.sql)
- [全文检索](/labs/ch09/search-query.sql)
- [事件范围](/labs/ch09/event-query.sql)

候选由 [create-candidates.sh](/labs/ch09/create-candidates.sh) 以独立的 `CREATE INDEX CONCURRENTLY` 创建：

```sql
-- 订单：状态在 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：

```sql
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 执行：

```sql
DROP INDEX CONCURRENTLY
    shop_private.ch09_event_occurred_btree_idx;
```

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

## 9.6.2 在 Pigsty L1 保留、合并或拒绝并记录证据 {#item-9-6-2}

### 一键运行完整闭环

```bash
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
```

执行顺序：

```text
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 实测输出：

```text
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

证据目录包含：

```text
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](/labs/ch09/index-decisions.json) 为每个结论绑定 query 和 artifact：

| candidate | 结论 | 依据 |
|---|---|---|
| order partial covering | retain | literal workload 稳定、Top-N、index-only；同时记录 generic 边界 |
| inventory reverse covering | retain | SKU-first 是 declared access path，返回 30 个 warehouse 且 heap fetch=0 |
| search document GIN | retain | document/query 使用相同 `simple` config 与 `@@` 语义 |
| event occurred BRIN | retain | 物理相关范围可用，约为 B-tree 大小的 0.27% |
| event occurred B-tree | reject | 对同一 workload 的额外精度/空间不值 |
| warehouse-only B-tree | reject | 现有 PK 已以 warehouse 为左前缀 |
| generic attributes JSONB GIN | reject | 没有 declared containment consumer，却会索引大量 token |
| volatile counter B-tree | reject | 无读取消费者，HOT 1→0 且 WAL 增加 |

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

### 在 Pigsty 中复核相同关系

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

```text
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 将索引审查规则追加到规约 {#item-9-6-3}

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

[baseline-v0.4-proposal.json](/labs/ch09/baseline-v0.4-proposal.json) 不改写第 6 章的不可变 v0.1 baseline，而是为 `PREF-PLAN-005` 追加 candidate evidence：

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

提案绑定三层 provenance：

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

它仍标记为：

```json
{
  "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：

```bash
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 正确也拒绝。成功后必须看到：

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

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

```text
f8a7bfae59c6d16cd323abecfefe1014
```

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

### 最终复现清单

```bash
# 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 都有可审计证据。

---

[上一节：验证而不是“加完就快”](../05/) · [返回本章目录](../) · [下一章：顾此失彼：并发控制与隔离异常](/concurrency-isolation/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)