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

[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();
  "

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

-- 订单：状态在 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);

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

DROP INDEX CONCURRENTLY
    shop_private.ch09_event_occurred_btree_idx;

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

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

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

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

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

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

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

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

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

f8a7bfae59c6d16cd323abecfefe1014

# 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