第02章：AI帮我建了索引，但 EXPLAIN 说没有用到

“你让 AI 建了索引，EXPLAIN 还是显示 Seq Scan。原因可能是统计信息过期、索引选择率不够、或者查询优化器认为全表扫描更快——AI 不会主动告诉你这些。”

---

ℹ️ 版本说明：本章基于 PostgreSQL 18.4。

2.1 AI默认会生成什么

你告诉 AI “users 表查询很慢”，它帮你建了索引：

CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_email ON users(email);

然后你运行 EXPLAIN，发现查询还是 Seq Scan：

Seq Scan on users  (cost=0.00..18456.00 rows=1000000 width=64)
  Filter: (status = 'active')

AI 建的索引"看起来对"，但 PostgreSQL 就是不用——为什么？

---

2.2 AI通常遗漏的4个坑

⚠️ 坑1：索引选择率太低

PostgreSQL 决定用不用索引，取决于索引选择率（Selectivity）：过滤之后还剩多少比例的数据。

如果 status 字段只有两个值 active/inactive，且 95% 的用户是 active：

WHERE status = 'active'  -- 过滤后还剩 95% 的数据

PostgreSQL 认为：扫描索引 → 再读取 95% 的数据行，不如直接顺序扫描全表。

索引适合：选择率高（过滤后只剩 1-5% 的数据）的查询，如 user_id = 123、email = 'abc@example.com'。

---

⚠️ 坑2：统计信息过期（ANALYZE 没有运行）

PostgreSQL 的查询优化器依赖统计信息（每个字段的值分布）来估算"过滤后剩几行"。

如果你刚刚插入了大量数据，但 ANALYZE 还没运行，优化器可能用的是过期的统计信息，做出错误的执行计划选择（认为表数据量很少，走全表扫描反而快）。

ANALYZE users;  -- 手动更新统计信息
-- 或者等 autovacuum 自动运行（可能有几分钟延迟）

---

⚠️ 坑3：复合索引字段顺序错误

AI 可能给你建了这个索引：

CREATE INDEX idx_orders_status_user ON orders(status, user_id);

但你的查询是：

WHERE user_id = 123  -- 只用了 user_id，没有用 status

B-tree 索引只能从最左前缀开始使用。(status, user_id) 索引无法支持只查 user_id 的查询，需要把 user_id 放在前面：

CREATE INDEX idx_orders_user_status ON orders(user_id, status);  -- 正确顺序

---

⚠️ 坑4：隐式类型转换让索引失效

-- users.id 是 bigint（8字节整数）
-- 但传入的是字符串 '123'
WHERE id = '123'  -- 隐式转换：text → bigint，索引可能失效

更危险的：

-- users.phone 是 varchar，索引已建
WHERE phone = 12345678901  -- 整数字面量 → 与 varchar 类型不匹配，可能全表扫描

---

2.3 更好的提示词

提示词 P01：让 AI 帮你读懂 EXPLAIN ANALYZE 输出

使用时机：EXPLAIN 显示没有用到你以为有效的索引

比默认多了什么：

• 找出为什么索引没被使用
• 建议修复方案

帮我分析以下 PostgreSQL EXPLAIN ANALYZE 输出，找出查询慢的原因。

查询：
[粘贴你的 SQL]

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 输出：
[粘贴输出内容]

我已有的索引：
[粘贴 \d tablename 的输出]

请告诉我：
1. 哪里是瓶颈？（Seq Scan、Hash Join、Sort 等）
2. 优化器为什么选择了这个执行计划？（选择率估算、统计信息）
3. 如果存在 Seq Scan，为什么没有用到索引？
   - 索引选择率问题？
   - 统计信息过期？（estimated rows 和 actual rows 差距大）
   - 字段类型不匹配？
   - 索引定义和查询条件不匹配？
4. 如何修复？给出具体的索引 DDL 或 SQL 改写方案。

"Buffers: shared hit=" 和 "shared read=" 分别代表什么？

基于 PostgreSQL 18.4。

---

提示词 P02：设计高选择率的正确索引

使用时机：需要为特定查询模式设计最优索引

比默认多了什么：

• 复合索引字段顺序的计算依据
• 部分索引（Partial Index）的应用
• 索引覆盖（Covering Index）减少回表

帮我为以下查询场景设计最优 PostgreSQL 18.4 索引策略。

表结构：
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL,
    status text NOT NULL,      -- 值：pending/processing/completed/cancelled
    amount decimal(10,2),
    created_at timestamptz DEFAULT now()
);

主要查询场景：
1. 查询某个用户的最近10笔订单：
   SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10
   
2. 查询所有 pending 状态的订单（只有约2%的订单是pending）：
   SELECT id, user_id, amount FROM orders WHERE status = 'pending'
   
3. 查询某用户某状态的订单：
   SELECT * FROM orders WHERE user_id = ? AND status = ?

对每个查询：
1. 推荐的索引（考虑字段顺序）
2. 是否适合使用部分索引（Partial Index）？为什么？
3. 是否适合使用覆盖索引（INCLUDE 子句）？
4. 这个索引能减少多少 I/O？

同时告诉我：
- orders 表有1000万行时，这些索引的磁盘占用大概是多少？
- 索引过多有什么代价？（写操作性能影响）

基于 PostgreSQL 18.4。

---

提示词 P03：强制验证索引是否生效

使用时机：建好索引后，验证查询确实在使用它

比默认多了什么：

• 对比有无索引的执行计划
• 强制 PostgreSQL 走索引（排除其他干扰）
• 统计信息手动更新

帮我写验证 PostgreSQL 索引是否生效的完整步骤。

我建了这个索引：
[粘贴你的 CREATE INDEX 语句]

我的查询是：
[粘贴你的 SELECT 语句]

验证步骤：
1. 强制刷新统计信息：
   - ANALYZE tablename
   - 什么情况下 autovacuum 会漏掉更新？

2. 查看执行计划（几种方式）：
   - EXPLAIN (ANALYZE, BUFFERS)：什么意思？
   - EXPLAIN (ANALYZE, BUFFERS, SETTINGS)：PostgreSQL 18 新增的 SETTINGS 显示什么？
   
3. 如果 PostgreSQL 不走索引，如何强制测试：
   - SET enable_seqscan = off（临时禁用顺序扫描）
   - 这样做的风险？（仅用于测试，不要在生产使用）

4. 验证索引真正在使用后的性能提升：
   - 对比 Seq Scan 和 Index Scan 的 cost 估算和实际运行时间

5. 如何确认索引选择率足够高（值得建索引）：
   - 查询 pg_stats 里的 correlation 和 n_distinct
   - 什么数值说明这个索引是有效的？

基于 PostgreSQL 18.4。

---

2.4 验收清单

检查项	验证方法	AI辅助
EXPLAIN 显示 Index Scan	EXPLAIN (ANALYZE, BUFFERS) [query] 无 Seq Scan	让 AI 分析 EXPLAIN 输出
estimated rows 接近 actual rows	EXPLAIN 输出中两者差距 < 10倍	让 AI 解释统计信息差距
复合索引字段顺序正确	查询中的最常见等值条件字段在前	让 AI 验证索引定义
无隐式类型转换	查询参数类型与列类型一致	让 AI 检查类型匹配
统计信息及时更新	autovacuum 正在运行 + 手动 ANALYZE	让 AI 检查 autovacuum 配置
索引数量合理	每个写频繁的表索引 < 6个	让 AI 审查索引必要性

---

2.5 本章小结

如果你只记一件事：建完索引后，用 EXPLAIN (ANALYZE, BUFFERS) 验证查询真的走了 Index Scan。如果还是 Seq Scan，先跑一下 ANALYZE tablename 更新统计信息，再看有没有改变。

索引不生效的三大原因：

1. 选择率太低：status = 'active' 匹配了 95% 的数据，PostgreSQL 觉得直接扫表更快
2. 统计信息过期：大量数据插入后，优化器还用的是旧的数据分布，运行 ANALYZE 解决
3. 复合索引顺序错：(status, user_id) 支持按 status 过滤，但不支持只按 user_id 过滤——最常用的过滤字段要放第一位

→ 第3章：AI生成的 GRANT ALL PRIVILEGES，权限给得太大了