在 PostgreSQL 中，索引是提升查询性能的核心手段。然而，“建了索引却未被使用” 是 DBA 和开发者最常遇到的性能陷阱。索引失效不仅浪费存储和写入开销，更会导致查询慢如蜗牛。本文将系统剖析 PostgreSQL 索引失效的十大常见原因，结合执行计划分析、原理说明和修复方案，助你精准定位并解决索引未命中问题。

一、前置知识：如何判断索引是否生效？

在分析原因前，需掌握验证方法：

1.1 使用 EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'alice@example.com';

关键观察点：

• Index Scan / Bitmap Index Scan：索引被使用
• Seq Scan：全表扫描，索引未生效
• Rows Removed by Filter：过滤掉的行数（值大说明选择性差）

1.2 检查索引是否存在及类型

-- 查看表的所有索引
\d+ users

-- 查看索引定义
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

1.3 索引失效的本质和解决思路

索引失效的本质，是 “优化器认为索引扫描的成本高于其他方式”。这可能是由于：

• 查询写法问题（函数、OR、前导通配符）
• 数据分布问题（倾斜、NULL、小表）
• 元数据问题（统计信息过期）
• 索引设计问题（列顺序、缺失）

解决思路应为：

1. 用 EXPLAIN 确认是否失效
2. 对照十大原因逐项排查
3. 优先改写查询或调整设计，而非强制索引

1.4 预防索引的建议

1. 设计阶段：

   • 根据查询模式设计索引（而非盲目建索引）
   • 复合索引遵循“等值列在前，范围列在后”原则

2. 开发阶段：

   • 避免在索引列上使用函数/表达式
   • 优先使用前缀匹配（LIKE 'abc%'）

3. 运维阶段：

   • 定期执行 ANALYZE（尤其大批量写入后）
   • 监控慢查询日志，及时发现未命中索引的 SQL

4. 监控阶段：

   • 使用 pg_stat_user_indexes 查看索引使用率：

     SELECT schemaname, tablename, indexname, idx_scan
     FROM pg_stat_user_indexes
     WHERE idx_scan = 0;  -- 从未使用的索引
     

   • 删除无用索引，减少写入开销

---

二、十大索引失效原因详解

原因一：查询条件未使用索引列（最基础错误）

1、现象
索引建在 email 列，但查询条件使用 name。

-- 索引：CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE name = 'Alice';  -- 无法使用 idx_users_email

2、修复

• 为 name 列单独建索引
• 或创建复合索引 (name, email)（若常联合查询）

注意：索引只能加速其包含的列的查询。

---

原因二：对索引列使用函数或表达式

1、现象
在 WHERE 条件中对索引列进行计算、函数调用或类型转换。

-- 索引：CREATE INDEX idx_users_email ON users(email);

-- ❌ 失效：函数包裹
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

-- ❌ 失效：表达式
SELECT * FROM users WHERE email || '@domain.com' = 'alice@domain.com';

-- ❌ 失效：隐式类型转换
SELECT * FROM users WHERE email = 123;  -- email 是 text，123 是 integer

2、原理
PostgreSQL 无法将函数结果与索引树直接比对，必须先计算每行的函数值。

3、修复方案

方案 A：改写查询（推荐）

-- 改为常量比较
SELECT * FROM users WHERE email = 'alice@example.com';

方案 B：创建函数索引（Function-Based Index）

CREATE INDEX idx_users_upper_email ON users(UPPER(email));

-- 查询需完全匹配索引表达式
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

注意：函数索引需精确匹配表达式，大小写、空格均敏感。

---

原因三：使用 != 或 NOT IN 导致全表扫描

1、现象

-- 索引：CREATE INDEX idx_users_status ON users(status);

-- ❌ 通常失效
SELECT * FROM users WHERE status != 'inactive';

-- ❌ 可能失效（尤其当 'inactive' 占比很小时）
SELECT * FROM users WHERE status NOT IN ('banned', 'deleted');

2、原理

• != 和 NOT 类操作的选择性难以预估
• 若排除的值占比很小（如 1%），则需扫描 99% 的数据，优化器认为全表扫描更高效

3、修复方案

方案 A：改用正向条件（若业务允许）

-- 假设只有三种状态
SELECT * FROM users WHERE status IN ('active', 'pending');

方案 B：确保排除值占比较高

• 若 status = 'inactive' 占 90%，则 status != 'inactive' 仅返回 10%，索引可能被使用
• 需通过 ANALYZE 更新统计信息，让优化器准确估算

---

原因四：LIKE 查询以通配符开头

1、现象

-- 索引：CREATE INDEX idx_users_name ON users(name);

-- ❌ 失效：前导通配符
SELECT * FROM users WHERE name LIKE '%Alice%';

-- ✅ 有效：后缀通配符
SELECT * FROM users WHERE name LIKE 'Alice%';

2、原理
B-tree 索引基于前缀排序，无法加速任意位置的子串匹配。

3、修复方案

方案 A：使用全文检索（Full-Text Search）

-- 创建 tsvector 列并建索引
ALTER TABLE users ADD COLUMN name_ts tsvector;
UPDATE users SET name_ts = to_tsvector('english', name);
CREATE INDEX idx_users_name_ts ON users USING GIN(name_ts);

-- 查询
SELECT * FROM users WHERE name_ts @@ to_tsquery('Alice');

方案 B：使用 pg_trgm 扩展（支持任意 LIKE）

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建 GIN 或 GiST 索引
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);

-- 查询（可使用 '%Alice%'）
SELECT * FROM users WHERE name LIKE '%Alice%';

注意：pg_trgm 索引体积较大，仅适用于高价值模糊查询。

---

原因五：复合索引的列顺序不当

1、现象
复合索引 (a, b, c)，但查询只使用 b 或 c。

-- 索引：CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- ❌ 失效：跳过首列
SELECT * FROM orders WHERE order_date > '2026-01-01';

-- ✅ 有效：使用首列
SELECT * FROM orders WHERE user_id = 123 AND order_date > '2026-01-01';

2、原理
PostgreSQL 的 B-tree 复合索引遵循 最左前缀原则（Leftmost Prefix）：

• 可用于 (a)、(a,b)、(a,b,c) 的查询
• 无法用于 (b)、(c)、(b,c) 的查询

3、修复方案

方案 A：调整索引列顺序

• 将高选择性或常单独查询的列放在前面
• 例如：若常查 order_date，则建 (order_date, user_id)

方案 B：创建额外索引

• 为高频单列查询单独建索引
• 权衡：写入性能 vs 查询性能

---

原因六：数据分布倾斜导致优化器放弃索引

1、现象
某值占比极高（如 99%），即使有索引，查询该值仍走全表扫描。

-- 假设 status = 'active' 占 99%
-- 索引：CREATE INDEX idx_users_status ON users(status);

-- ❌ 可能失效：返回大量数据
SELECT * FROM users WHERE status = 'active';

2、原理

• 索引扫描需回表（Heap Fetch），当返回行数接近全表时，I/O 成本高于顺序扫描
• 优化器基于统计信息估算成本，选择更优方案

3、验证方法

-- 查看值分布
SELECT status, COUNT(*) FROM users GROUP BY status;

-- 查看统计信息
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'users' AND attname = 'status';

4、修复方案

方案 A：接受全表扫描（合理行为）

• 若查询确实需返回大部分数据，全表扫描反而是最优解

方案 B：优化查询逻辑

• 避免查询超高频值，改用分页或聚合

方案 C：强制使用索引（不推荐）

-- 临时禁用 seqscan（仅用于测试！）
SET enable_seqscan = off;
-- 执行查询后立即恢复
RESET enable_seqscan;

警告：强制索引可能导致性能更差，仅用于诊断。

---

原因七：未更新表统计信息（ANALYZE 缺失）

1、现象
新导入大量数据后，索引突然不生效。

2、原理
PostgreSQL 优化器依赖 pg_statistic 中的统计信息估算行数。若数据变更后未执行 ANALYZE，统计信息过期，导致错误的执行计划。

3、验证

-- 查看上次 analyze 时间
SELECT schemaname, tablename, last_analyze, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables WHERE tablename = 'orders';

4、修复

-- 手动更新统计信息
ANALYZE orders;

-- 或调整 autovacuum 参数（自动触发）
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.05);

建议：大批量数据导入后，显式执行 ANALYZE。

---

原因八：索引列存在大量 NULL 值且查询未处理

1、现象
索引列允许 NULL，查询条件未考虑 NULL。

-- 索引：CREATE INDEX idx_users_phone ON users(phone);

-- ❌ 可能低效：NULL 值不存于 B-tree 索引（默认）
SELECT * FROM users WHERE phone IS NOT NULL;

2、原理

• PostgreSQL 的 B-tree 索引默认不存储 NULL 值
• 若 phone IS NOT NULL 返回大量行，优化器可能选择全表扫描

3、修复方案

方案 A：创建包含 NULL 的索引（Partial Index）

-- 仅索引非 NULL 值（实际同默认行为）
CREATE INDEX idx_users_phone_notnull ON users(phone) WHERE phone IS NOT NULL;

-- 查询需匹配条件
SELECT * FROM users WHERE phone = '123' AND phone IS NOT NULL;

方案 B：明确处理 NULL

-- 若业务需排除 NULL，显式写出
SELECT * FROM users WHERE phone IS NOT NULL AND phone LIKE '123%';

注意：若需查询 IS NULL，应单独为 NULL 建部分索引：

CREATE INDEX idx_users_phone_null ON users((1)) WHERE phone IS NULL;

---

原因九：使用 OR 条件且非所有分支有索引

1、现象

-- 索引：CREATE INDEX idx_users_email ON users(email);

-- ❌ 失效：name 无索引
SELECT * FROM users WHERE email = 'a@example.com' OR name = 'Alice';

2、原理

• OR 条件要求所有分支均可索引扫描，才能合并结果
• 若任一分支无法使用索引，优化器可能放弃全部索引

3、修复方案

方案 A：为所有 OR 分支建索引

CREATE INDEX idx_users_name ON users(name);
-- 此时 OR 查询可能使用 BitmapOr

方案 B：改写为 UNION（推荐）

SELECT * FROM users WHERE email = 'a@example.com'
UNION
SELECT * FROM users WHERE name = 'Alice';

优势：每个子查询独立使用索引，避免优化器误判。

---

原因十：数据量过小，优化器认为索引无必要

1、现象
测试表仅 10 行数据，即使有索引也走 Seq Scan。

2、原理

• 索引扫描需额外 I/O（读索引页 + 读数据页）
• 当表很小时，全表扫描的 I/O 成本低于索引扫描

3、验证

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));

4、修复

• 无需修复：这是优化器的正确决策
• 生产环境数据量增大后，索引会自动生效

建议：在接近生产规模的数据集上测试索引效果。

---

三、高级诊断技巧

技巧 1：强制索引扫描（仅用于测试）

SET enable_seqscan = off;
EXPLAIN SELECT * FROM users WHERE ...;
RESET enable_seqscan;

• 若强制后性能更差，说明优化器选择正确
• 若强制后性能更好，需检查统计信息或配置

技巧 2：查看优化器成本参数

SHOW random_page_cost;   -- 默认 4.0（SSD 建议设为 1.1）
SHOW cpu_tuple_cost;

• 在 SSD 环境下，降低 random_page_cost 可促使更多使用索引

技巧 3：使用 pg_hint_plan 扩展（谨慎）

/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'a@example.com';

• 绕过优化器强制使用索引
• 仅用于临时救急，非长久之计

---