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

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