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

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

---

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

| 维度 | 要证明的事实 |
|---|---|
| 正确性 | 返回集合、排序、唯一/约束语义不变 |
| 读取 | 哪些参数桶、并发与 cache 状态改善，tail 是否达标 |
| 写入 | INSERT/UPDATE/DELETE、HOT、WAL、CPU/I/O 和 replica 是否可接受 |
| 生命周期 | build、失败、磁盘峰值、监控、回退与以后清理是否可控 |

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

## 9.5.1 计划、缓冲区、延迟分布与写入代价 {#item-9-5-1}

### 保存机器可读 before/after

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

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

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

```text
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、单连接执行无法代表：

```text
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 算总体收益：

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

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

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

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

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

```text
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 文档](https://pigsty.io/docs/pgsql/dashboard/) 和实际变量为准。面板负责说明“何时、影响多大”，最终仍要落回：

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

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

## 9.5.2 数据规模和缓存状态一致的 A/B 对照 {#item-9-5-2}

### 一次只改变候选索引

一个可复核 A/B：

```text
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 应固定并公开：

```text
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` 可回答：

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

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

## 9.5.3 线上创建、失败回收与监控窗口 {#item-9-5-3}

### 普通与 concurrent build 的真实差别

普通：

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

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

并发：

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

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

```text
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、锁和失败恢复必须在目标版本演练。

### 开始前定义水位与停止线

生产变更单至少写：

```text
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 观察阶段，不猜百分比

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

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

检查：

```sql
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 回收：

```sql
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`](https://www.postgresql.org/docs/18/using-explain.html)
- [PostgreSQL 18：`CREATE INDEX`](https://www.postgresql.org/docs/18/sql-createindex.html)
- [PostgreSQL 18：CREATE INDEX Progress Reporting](https://www.postgresql.org/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING)
- [PostgreSQL 18：`pg_stat_statements`](https://www.postgresql.org/docs/18/pgstatstatements.html)
- [Pigsty：PostgreSQL Dashboards](https://pigsty.io/docs/pgsql/dashboard/)

---

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