PostgreSQL 索引没生效的几种常见情况

"我建了索引，为什么查询还是慢？"这个问题我在群里回答过很多次，自己也踩过很多次。下面是遇到最多的几种情况，基本都能用 EXPLAIN ANALYZE 看出来。

1. 对列做了函数或运算

-- 索引建在 created_at 上
SELECT * FROM orders WHERE date(created_at) = '2024-06-01';

对列套了函数，普通 B-tree 索引就用不上了。改成范围查询：

SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

如果确实需要按函数结果查，可以建表达式索引 CREATE INDEX ON orders (date(created_at))。

2. 类型不一致

user_id 是 bigint，但代码里传进来的是字符串，ORM 生成了 user_id = '123'::text 之类的比较，结果发生隐式转换。这个在日志里很难看出来，最好在 EXPLAIN 里检查 Filter 那一行的类型。

3. LIKE 前面带百分号

LIKE '%abc' 和 LIKE '%abc%' 用不了普通 B-tree 索引。需要模糊匹配的话，可以用 pg_trgm 扩展建 GIN 索引。另外，即使是 LIKE 'abc%'，如果数据库的排序规则不是 C，也需要建索引时指定 text_pattern_ops。

4. 统计信息过时

大批量导入数据之后，如果还没来得及自动 ANALYZE，优化器会按旧的统计信息估算行数，可能觉得全表扫描更划算。导入完手动跑一次 ANALYZE 表名 通常就好了。

5. 优化器认为全表扫描更快

如果查询条件会命中表里大部分的行，比如 status = 'done' 占了 90%，那走索引还要回表，确实不如直接顺序扫描。这种情况不是 bug。可以考虑部分索引，只给少数的状态建索引：

CREATE INDEX ON orders (created_at) WHERE status = 'pending';

6. 复合索引的列顺序

索引 (a, b) 对 WHERE b = ? 基本没有帮助。列的顺序要按查询条件来设计，等值条件放前面，范围条件放后面。

最后一条建议：不要猜，跑 EXPLAIN (ANALYZE, BUFFERS)，看实际的行数和预估的行数差了多少，大部分问题都藏在这里。