# PostgreSQL 索引不生效？Postgres 不走索引的 7 个原因，附可复现的 EXPLAIN 输出

明明建了索引，EXPLAIN 里还是 Seq Scan？本文梳理 PostgreSQL 忽略索引（索引不生效）的 7 个原因：函数、类型转换、通配符、选择性、统计信息过期、列顺序、小表，每个都附复现步骤和修复方法。

**你执行了 `CREATE INDEX`，也跑了查询，`EXPLAIN` 却依然显示 `Seq Scan`。先别急着怪规划器：多数情况下，它对你写出来的查询判断是对的，错的只是你想表达的意思。本文整理了我们最常见的 7 个 PostgreSQL 忽略索引的原因：列上套了函数、类型不匹配、前导通配符、过滤条件命中表的大部分行、统计信息过期、多列索引顺序不对，以及表本身太小。每个案例都附有在 PostgreSQL 17.11 上的复现步骤、实际产生的执行计划和修复方法。把建表脚本粘贴到 `psql` 里，在默认设置下你会得到同样的执行计划。**

## 如何判断 Postgres 有没有用上我的索引？

在查询前加上 `EXPLAIN`。执行计划节点名为 `Index Scan`、`Index Only Scan` 或 `Bitmap Index Scan`，说明用到了索引。出现 `Seq Scan`（或 `Parallel Seq Scan`）则表示规划器会读取整张表。`EXPLAIN` 只生成计划；`EXPLAIN ANALYZE` 还会实际执行查询并显示真实行数，下面讲统计信息过期的案例时会用到。

有两个习惯能让执行计划更易读、更易比较：

- `EXPLAIN (COSTS OFF)` 会去掉代价数字，这些数字会随表大小和设置而变化。
- `EXPLAIN (ANALYZE, BUFFERS)` 会显示查询实际访问了多少页，而不仅仅是规划器预期的页数。

## 测试环境

7 个案例共用一张 20 万用户的表。数据都是生成出来的，无需下载任何东西：

```sql
SET TimeZone = 'UTC';
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  status     text NOT NULL,
  org_id     int  NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g,
       'User' || g || '@Example.com',
       CASE WHEN g % 100 = 0 THEN 'banned' ELSE 'active' END,
       (g % 500) + 1,
       timestamptz '2026-01-01' + (g || ' seconds')::interval
FROM generate_series(1, 200000) g;

CREATE INDEX users_email_idx ON users (email);
ANALYZE users;
```

作为基线，对索引列做精确匹配会用到索引：

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';
```

```text
 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)
```

下面所有案例，都是把这条查询稍作改动，然后丢掉索引。

## 1. 你在列上套了函数

`email` 上的索引存的是 `email` 的值。对 `lower(email)` 的条件问的是另一批值，所以索引回答不了：

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
```

```text
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)
```

这个计划是并行的，但本质上仍是顺序扫描。修复方法是建一个**表达式索引**，即在查询所用的同一个表达式上建索引：

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: (lower(email) = 'user42@example.com'::text)
   ->  Bitmap Index Scan on users_lower_email_idx
         Index Cond: (lower(email) = 'user42@example.com'::text)
```

查询里的表达式必须与索引中的表达式一致。`lower(email)` 和 `lower(trim(email))` 是不同的表达式。同样的规则也适用于 `coalesce(col, 0)`、`price * 100` 这类算术运算，以及日期函数。日期函数还有一个坑：在 `timestamptz` 列上，`date_trunc('day', created_at)` 和 `created_at::date` 取决于会话时区，所以 Postgres 不允许直接对它们建索引（`functions in index expression must be marked IMMUTABLE`）。请改为索引一个固定了时区的表达式，例如 `((created_at AT TIME ZONE 'UTC')::date)`，并在查询里使用同样的表达式。

如果可以，优先改写条件。`created_at >= '2026-01-02' AND created_at < '2026-01-03'` 可以使用 `created_at` 上的普通索引，而 `created_at::date = '2026-01-02'` 不行。

## 2. 类型不匹配

比较两侧的类型不同时，Postgres 必须转换其中一侧。如果被转换的是列，列上的普通索引就不再适用。这里 `id` 是 `bigint`，而字面量 `42.0` 是 `numeric`：

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42.0;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id::text = '42';
```

```text
 Index Scan using users_pkey on users
   Index Cond: (id = 42)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::numeric = 42.0)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::text = '42'::text)
```

看 `Filter` 这一行：`(id)::numeric` 就是对列做的类型转换。这个转换是最明显的线索：只要执行计划里索引列出现 `Seq Scan`，就该先找它。

修复方法是让字面量或参数的类型与列一致。在应用代码里，常见的来源是驱动把数字当作 `numeric` 发送，或者值以字符串形式传入。可以在查询里对参数做转换（`WHERE id = $1::bigint`），或者从源头修正类型。如果列本身的类型就不对，比如标识符存成了 `text` 却和数字比较，那么修改列类型才是真正的修复。

## 3. 模式以通配符开头

B-tree 索引是有序的，所以能快速找到所有以 `User4` 开头的值，却找不到所有以 `1234@Example.com` 结尾的值，因为这些值并不是按结尾排序的：

```sql
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';
```

```text
 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)
```

`pg_trgm` 扩展会为三字符片段建立索引，这让 Postgres 能对带前导通配符的模式、`ILIKE` 以及 `%` 相似度搜索使用索引。`pg_trgm` 是 Relyt One 上可用的扩展之一。索引类型是使用 `gin_trgm_ops` 操作符类的 GIN 索引：

```sql
CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: (email ~~ '%1234@Example.com'::text)
   ->  Bitmap Index Scan on users_email_trgm_idx
         Index Cond: (email ~~ '%1234@Example.com'::text)
```

三元组索引比 B-tree 索引更大，更新也更慢，并且在搜索字符串足够长、至少包含一个完整三元组时效果最好。少于三个字符的模式，它几乎帮不上忙。如果你真正的需求是在文本中搜索词语，请使用全文搜索（`tsvector`），而不是 `LIKE`。

## 4. 过滤条件命中了表的大部分行

当索引能让数据库跳过表的大部分内容时，它才划算。如果某个条件匹配 99% 的行，按顺序读完整张表，比通过索引跳转 198,000 次更便宜，规划器对此心知肚明：

```sql
CREATE INDEX users_status_idx ON users (status);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'active';
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'banned';
```

```text
 Seq Scan on users
   Filter: (status = 'active'::text)

 Index Scan using users_status_idx on users
   Index Cond: (status = 'banned'::text)
```

这是索引在按设计工作：同一个索引，对 1% 的行会用，对 99% 的行则跳过。规划器这边没有任何需要修复的地方。如果常见值的那条查询很重要，就该问问它是否真的应该返回这么多行（可以加 `LIMIT`、额外的过滤条件或聚合）。

真正能改进的是罕见值。**部分索引**只覆盖你要查找的那些行，所以体积小，维护成本也低：

```sql
DROP INDEX users_status_idx;
CREATE INDEX users_banned_idx ON users (id) WHERE status = 'banned';
EXPLAIN (COSTS OFF) SELECT id FROM users WHERE status = 'banned';
```

```text
 Index Only Scan using users_banned_idx on users
```

查询的 `WHERE` 子句必须蕴含索引的 `WHERE` 子句。查询 `status = 'banned'` 可以使用这个索引，查询其他状态则不行。

## 5. 统计信息过期

规划器在生成计划时并不会查看你的数据，而是查看 `ANALYZE` 收集的统计信息，并据此估算。如果上一次 `ANALYZE` 之后表发生了大量变化，估算就会与实际脱节，计划可能向任一方向出错，包括在索引并不合适的时候选择了索引。

autovacuum 通常在表约 10% 的数据发生变化时刷新统计信息（`autovacuum_analyze_scale_factor = 0.1`，再加 50 行的基数）。为了确定地展示这种效果，我们对一张表关闭 autovacuum，在它只有 5 行时执行 analyze，然后批量导入 300,000 行：

```sql
CREATE TABLE events (id bigint, kind text, created_at timestamptz);
ALTER TABLE events SET (autovacuum_enabled = false);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events SELECT g, 'click', now() FROM generate_series(1, 5) g;
ANALYZE events;
INSERT INTO events SELECT g, 'view', now() FROM generate_series(1, 300000) g;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';
```

```text
 Aggregate  (cost=4.31..4.32 rows=1 width=8) (actual rows=1 loops=1)
   ->  Index Only Scan using events_kind_idx on events  (cost=0.29..4.30 rows=1 width=0) (actual rows=300000 loops=1)
         Index Cond: (kind = 'view'::text)
         Heap Fetches: 300000
```

规划器预期是 1 行（`rows=1`），实际得到 300,000 行（`actual rows=300000`）。它为一次要读完整张表的查找选择了索引，而 `Heap Fetches: 300000` 意味着每一行还都要再访问一次表。执行 `ANALYZE` 之后，同一条查询的计划变了：

```sql
ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';
```

```text
 Finalize Aggregate  (cost=5558.22..5558.23 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather  (cost=5558.11..5558.22 rows=1 width=8) (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=4558.10..4558.11 rows=1 width=8) (actual rows=1 loops=2)
               ->  Parallel Seq Scan on events  (cost=0.00..4116.92 rows=176474 width=0) (actual rows=150000 loops=2)
                     Filter: (kind = 'view'::text)
                     Rows Removed by Filter: 2
```

具体的代价数字每次运行会略有不同，所以不要逐位对比。这里的重点是如何读执行计划。**在每个节点上对比 `rows=` 和 `actual rows=`。** 当两者相差几个数量级时，问题出在估算，而不是索引。批量导入或大规模回填之后，请执行 `ANALYZE`；如果怀疑 autovacuum 跟不上，可以查看 `pg_stat_user_tables` 中的 `last_autoanalyze`。对于分布倾斜的列，提高统计目标（`ALTER TABLE … ALTER COLUMN … SET STATISTICS 500`）能让规划器获得比默认值 100 更细致的分布信息。

## 6. 多列索引的列顺序不对

多列 B-tree 索引先按第一列排序，在第一列的每个值内再按第二列排序，依此类推。过滤条件包含最左列的查询可以很好地利用它；跳过最左列的查询则不行：

```sql
CREATE INDEX users_org_created_idx ON users (org_id, created_at);
ANALYZE users;

-- 约束了最左列：用到索引
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE org_id = 7 AND created_at >= '2026-01-02';

-- 缺少最左列：用不到索引
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';
```

```text
 Bitmap Heap Scan on users
   Recheck Cond: ((org_id = 7) AND (created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on users_org_created_idx

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...
```

如果你需要单独按 `created_at` 过滤，就需要一个以它打头的索引。对于要同时服务两类查询的索引，通常的规则是：把等值条件的列放在前面，范围条件的列放在最后：

```sql
CREATE INDEX users_created_org_idx ON users (created_at, org_id);
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';
```

```text
 Index Scan using users_created_org_idx on users
   Index Cond: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...
```

**这一点取决于你的版本。** PostgreSQL 18 为 B-tree 索引加入了 skip scan，使省略最左列的查询在某些情况下也能使用索引。我们在 PostgreSQL 18.6 上用同样的表和同样的 `(org_id, created_at)` 索引做了测试，只按 `created_at` 做范围查询时，用到了 `(org_id, created_at)` 索引。而在 Relyt One 所运行的 PostgreSQL 17 上则不会。只有当规划器预计要跳过的最左列不同取值很少，或者后面的条件选择性很高（如本例）时，skip scan 才划算，所以专门为查询设计的索引依然更可靠。

每个索引在每次写入时也都有代价。添加之前，先确认已有的索引是否已经覆盖了这条查询。

## 7. 表很小，或者索引不值得用

一张只有 20 行的表放得进一页。读这一页比先读一个索引页再读一个表页更便宜，所以规划器选择扫描整张表：

```sql
CREATE TABLE plans (id int PRIMARY KEY, name text);
INSERT INTO plans SELECT g, 'plan' || g FROM generate_series(1, 20) g;
ANALYZE plans;

EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
```

```text
 Seq Scan on plans
   Filter: (id = 7)
```

这是正确的行为，但如果你在小型开发数据库上测试，却期望得到生产环境里的计划（或者反过来），它就是一个陷阱。请在与生产环境相似的数据上测试索引决策，无论是规模还是分布。

当你分辨不出索引是*不可用*还是*没被选中*时，还有一个诊断开关：在一个事务内关闭顺序扫描，然后查看计划：

```sql
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;
```

```text
 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)
```

如果索引出现了，说明它是可用的，只是规划器认为扫描整张表更便宜，此时应检查统计信息、选择性和表大小。如果它仍然没有出现，说明这个索引无法服务这条写法的查询，请回头查看案例 1、2、3 和 6。这个开关只用于诊断，不要一直开着，也不要写进应用代码。

## 我的数据库里有哪些索引从来没被用过？

反过来的问题是没有人使用的索引。它们会拖慢每一次插入和更新，还占用空间。`pg_stat_user_indexes` 统计自上次重置统计信息以来的索引扫描次数：

```sql
SELECT count(*) FROM users WHERE email = 'User42@Example.com';
SELECT count(*) FROM users WHERE email = 'User43@Example.com';

SELECT pg_sleep(1.5);  -- 统计信息的上报会有短暂延迟

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY idx_scan DESC, indexrelname;
```

在测试表上对 `email` 执行两次精确匹配查询，并稍等片刻让统计信息上报之后，输出如下：

```text
     indexrelname      | idx_scan
-----------------------+----------
 users_email_idx       |        2
 users_banned_idx      |        0
 users_created_org_idx |        0
 users_email_trgm_idx  |        0
 users_lower_email_idx |        0
 users_org_created_idx |        0
 users_pkey            |        0
```

有两点需要注意。单独执行 `EXPLAIN` 不算一次扫描，所以只有真正执行过的查询才会体现出来。此外，扫描次数为零的索引只是候选的删除对象，并不能证明它没用：它可能是为了保证唯一约束而存在，也可能服务于一份每月运行一次、自统计信息重置以来还没跑过的报表。

## 下一次遇到慢查询时的检查清单

1. 运行 `EXPLAIN (ANALYZE, BUFFERS)`，找出耗时占主导的节点。
2. 在它的 `Filter` 行里，查看列上是否套了函数或类型转换（案例 1 和 2）。
3. 如果模式以 `%` 开头，考虑使用三元组索引（案例 3）。
4. 对比 `rows=` 和 `actual rows=`。差距很大就该 `ANALYZE`（案例 5）。
5. 检查条件命中了多少行。如果是表的大部分，那么走扫描是正确的（案例 4）。
6. 对于多列索引，检查查询是否约束了最左列（案例 6）。
7. 在贴近真实的数据上测试，并用 `SET LOCAL enable_seqscan = off` 区分“不可用”和“没被选中”（案例 7）。

## 不动生产环境，测试索引变更

在大表上，建索引是最昂贵的部分：普通的 `CREATE INDEX` 在执行期间会阻塞写入，`CREATE INDEX CONCURRENTLY` 可以避免这一点，代价是构建更慢。在 Relyt One 上，每个分支都有自己的计算实例和写时复制存储，所以你可以为数据库创建一个分支，在分支里建索引，用你的真实数据运行 `EXPLAIN (ANALYZE)`，如果计划没有改善就丢弃这个分支。主分支从不构建这个索引，也不会感知到这次实验。请对比分支与生产环境的执行计划和行数估算，而不是耗时：分支的计算实例规格可能不同，并且缓存是冷的。

[免费开始使用](/auth/signin?ref=blog-postgres-not-using-index)