用大模型给 Postgres 建议索引简直就是开盲盒，不能直接信

只要你把 SQL 丢给模型，它绝对会给你甩出一行 CREATE INDEX，而且看起来逻辑非常合理。但问题是，模型根本不知道你的查询其实已经足够快了，或者它建议的索引在实际执行计划里根本没被命中。很多人直接把这个建议贴进 PR，结果没人敢审，最后就这么成了数据库里的冗余负担。

我写了个小工具来验证这些建议到底行不行，结论是：40% 的建议索引其实是垃圾，根本没被 Planner 使用。

为什么不能只看执行时间

很多人验证索引习惯对比 before 和 after 的耗时，这在毫秒级查询里完全没意义。我实测过一个案例，加了索引后 14.2ms 变成了 12.19ms，快了 17%，但查 EXPLAIN 发现 index used: False。这 2ms 的波动纯粹是噪声，如果只看表盘就决定保留这个索引，你就是在给数据库增加无谓的写入开销。

所以验证索引必须满足两个硬指标：
1. 查询速度是否有显著提升。
2. 执行计划里必须明确出现了这个新索引。

怎么构建这个验证闭环

最核心的技巧是利用 Postgres 的事务特性：在事务里创建索引，验证完直接 ROLLBACK。这样无论模型建议得多么离谱，都不会污染正式环境。

具体的验证逻辑是这样的：

# 伪代码逻辑，核心在 TRANSACTION
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，它不能在事务里运行。我的工具会强行把这个关键字删掉，因为在验证阶段，保证能回滚比异步创建更重要。等最后确定索引有效，手动上线时再把 CONCURRENTLY 加回去。

另外两个实操细节：

• 不要算平均值：我跑 5 次取最快的一次。因为平均值受干扰太大，而最快的一次代表了缓存命中后的底线，这个值最稳，能更准确地对比 30% 以上的性能增益。
• 必须跑 ANALYZE：创建完索引不跑 ANALYZE，Planner 可能会因为统计信息过期而拒绝使用新索引，导致你误以为索引无效。

实际测试环境和结果

我用的是 Postgres 18，笔记本跑，三张表总共 150 万行数据，主表 events 有 120 万行（约 104MB），这个规模刚好能感觉到全表扫描（Seq Scan）的卡顿。

我选了 8 个最常见的业务查询（比如按邮件查用户、查某个用户的最近 20 条事件、按日期查待处理订单等），把 Schema、现有索引和当前的 EXPLAIN (ANALYZE, BUFFERS) 输出全部喂给模型，让它每个查询提 3 个建议。

结果非常残酷，很多模型给出的索引虽然在语法上是对的，但在实际执行计划里根本没被选中。这意味着如果你盲目信任 AI 的索引建议，你的数据库里会堆积大量毫无用处的索引，白白浪费磁盘空间并拖慢写入速度。

如果你也想尝试，可以参考这个 Prompt 结构，重点是必须把当前的执行计划喂给它，否则它在瞎猜。

# Role: Postgres Index Optimization Expert

# Context:
I have a table with the following schema:
[粘贴你的建表语句]

Existing indexes:
[粘贴 \d 表名 的输出]

The query I want to optimize:
[粘贴你的 SQL]

Current Execution Plan:
[粘贴 EXPLAIN (ANALYZE, BUFFERS) 的结果]

# Task:
Analyze the current execution plan. If the query is already optimal, tell me "No index needed". Otherwise, suggest up to 3 potential indexes that could reduce the shared hit buffers or execution time. 

# Requirement:
Only provide the `CREATE INDEX` statement. Do not explain unless the current plan shows a clear sequential scan that can be avoided.