Postgres 索引建议存在盲区，直接采纳容易引发性能隐患

将 SQL 交给 AI 模型处理时，得到的往往是单行的 CREATE INDEX 语句，但这类建议未必贴合实际负载。模型难以评估现有查询是否已处于高效状态，也无法预判执行计划是否会真正调用新索引。若不加甄别地应用这些建议，数据库内便会堆积冗余索引，进而拖慢写入速度。

验证索引效果不能只看耗时变化。比如某次测试中，查询耗时从 14.2ms 缩短至 12.19ms，表面看提升了 17%，但 EXPLAIN 结果却显示 index used: False。这 2ms 的缩减仅是噪声，并无实质意义。有效的验证需同时满足两个条件：查询速度需有显著改善（通常超过 30%），且执行计划必须明确命中新索引。

借助 Postgres 的事务机制可以低成本完成验证。在事务内创建索引，确认无误后执行 ROLLBACK，从而避免污染生产环境。操作流程如下：

# 伪代码逻辑
cur.execute('BEGIN')
# 1. 执行模型建议的 DDL
cur.execute(ddl)
# 2. 必须运行 ANALYZE，否则 Planner 可能拒绝使用新索引
cur.execute('ANALYZE')
# 3. 检查 EXPLAIN (ANALYZE, BUFFERS) 是否命中新索引
after = measure(cur, sql)
# 4. 撤销所有操作
cur.execute('ROLLBACK')

操作时需留意细节：若 DDL 带有 CONCURRENTLY 关键字，验证阶段应将其移除以保证可回滚，待最终确认有效后再手动添加。此外，统计基准应取多次测试的最快值而非平均值，以排除干扰。创建索引后立即执行 ANALYZE 同样关键，否则 Planner 可能因统计信息滞后而忽略新索引。

在 Postgres 18 环境中，针对 3 张表（合计 150 万行，其中主表 events 占 120 万行）进行的测试表明，约 40% 的模型建议索引并未被采用。这种盲目跟随不仅浪费磁盘空间，还会削弱写入效率。为提升建议准确率，应向模型提供完整的 Schema、现有索引列表及当前执行计划（EXPLAIN (ANALYZE, BUFFERS)），避免其基于碎片化信息做出猜测。推荐的 Prompt 结构如下：

# 角色：Postgres 索引优化专家

# 背景：
我有一个表，结构如下：
[粘贴建表语句]

现有索引：
[粘贴 \d 表名 输出]

我想要优化的查询：
[粘贴 SQL]

当前执行计划：
[粘贴 EXPLAIN (ANALYZE, BUFFERS) 结果]

# 任务：
分析当前执行计划。如果查询已经最优，请告诉我“无需新建索引”。否则，推荐最多 3 个可能减少共享击数或执行时间的索引。

# 要求：
只提供 `CREATE INDEX` 语句。如有明显顺序扫描可避免，可附上解释。

这种对数据基础设施的精细管理并非新鲜事，《卫报》的内容生产流程便经历了类似的演变。其大部分内容——包括文章、直播博客、画廊和视频——均通过内部 CMS 工具 Composer 生成，该工具此前由托管在 AWS 上的 Mongo DB 支撑，作为所有在线发布内容的唯一真实源，涵盖约 2 亿条记录。近期，Guardian 完成了从 Mongo 到 Postgres SQL 的迁移。Composer 及其数据库最初诞生于 Guardian Cloud，即 King's Cross 办公室地下室的数据中心，并在伦敦设有故障转移节点。2015 年 7 月的一个炎热午后，高温导致数据中心运行不佳，严峻考验了故障切换流程，随后 Guardian 加速了向 AWS 的迁移步伐，并为此购买了 OpsManager 及 Mongo 支持合同以辅助云迁移工作。