跳到正文

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

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

Zhan Lu阅读约 10 分钟
本页目录

你执行了 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 万用户的表。数据都是生成出来的，无需下载任何东西：

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;

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

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';
 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)

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

1. 你在列上套了函数

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

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)

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

CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';
 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：

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';
 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 结尾的值，因为这些值并不是按结尾排序的：

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';
 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)

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

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';
 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 次更便宜，规划器对此心知肚明：

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';
 Seq Scan on users
   Filter: (status = 'active'::text)

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

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

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

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';
 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 行：

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';
 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 之后，同一条查询的计划变了：

ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';
 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 索引先按第一列排序，在第一列的每个值内再按第二列排序，依此类推。过滤条件包含最左列的查询可以很好地利用它；跳过最左列的查询则不行：

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';
 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 过滤，就需要一个以它打头的索引。对于要同时服务两类查询的索引，通常的规则是：把等值条件的列放在前面，范围条件的列放在最后：

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';
 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 行的表放得进一页。读这一页比先读一个索引页再读一个表页更便宜，所以规划器选择扫描整张表：

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;
 Seq Scan on plans
   Filter: (id = 7)

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

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

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;
 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)

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

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

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

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 执行两次精确匹配查询，并稍等片刻让统计信息上报之后，输出如下：

     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)，如果计划没有改善就丢弃这个分支。主分支从不构建这个索引，也不会感知到这次实验。请对比分支与生产环境的执行计划和行数估算，而不是耗时：分支的计算实例规格可能不同，并且缓存是冷的。

免费开始使用

标签Postgresindexesperformanceexplain

作者

Zhan Lu

相关文章

下一个数据库，只差一个分支。

一分钟内创建免费项目。无需信用卡，无需配置。

免费开始阅读文档

返回博客