使用 PostgreSQL 部分索引优化高频查询的实践与效果对比

起因

前段时间盯 Grafana 的时候发现，有个轮询接口的响应时间从十几毫秒慢慢爬到了八百多毫秒，而且还在涨。这个接口挺重要的，每五秒被调用一次，专门用来捞待处理的消息，慢成这样，下游已经开始超时重试了。

表结构大概长这样（简化过的）：

CREATE TABLE notifications (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    status      text NOT NULL DEFAULT 'pending',  -- pending / sent / failed
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now(),
    sent_at     timestamptz
);

查了一下行数，四千多万行。但真正处于 pending 状态的，峰值也就几千条，剩下的全是已经发出去的历史数据。换句话说，这个表里 99% 的数据对这个查询来说都是累赘。

先搞清楚为什么慢

老规矩，EXPLAIN ANALYZE 走起：

EXPLAIN ANALYZE
SELECT id, user_id, payload
FROM notifications
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

结果很直接，Seq Scan，全表扫：

Limit  (actual time=0.08..811.2 rows=50 loops=1)
  ->  Sort ...  Sort Method: top-N heapsort
        ->  Seq Scan on notifications  (actual rows=3214 ...)
              Filter: (status = 'pending'::text)
              Rows Removed by Filter: 41872311
Execution Time: 823.411 ms

Rows Removed by Filter: 41872311，这一行看得我血压都上来了。扫四千多万行，扔掉四千一百多万行，就为了找那几千条。

那给 status 建个普通索引行不行？行是行，但意义不大。这个表是典型的写多读少，每秒都在插入新消息，status 还会从 pending 更新成 sent，普通索引里那四千多万条死数据每次写入都得跟着维护，纯纯的浪费。

部分索引是干什么的

PostgreSQL 允许在建索引的时候加个 WHERE 条件，只把满足条件的行收进索引里，这就是部分索引（Partial Index）。对我们这个场景来说简直量身定做：查询只关心 pending 的数据，那索引里就只放 pending 的数据，其他九成多的行连碰都不用碰。

动手改

执行下面这条：

CREATE INDEX CONCURRENTLY idx_notifications_pending
ON notifications (created_at)
WHERE status = 'pending';

两个细节说一下：一是 CONCURRENTLY，加了这个参数建索引不会锁写，生产环境裸建索引会把表锁住，别问我怎么知道的... 二是索引里故意没放 status 这一列，因为 WHERE status = 'pending' 已经把范围限死了，索引里每一行的 status 都一样，放进去纯属占地方。

建完顺手查了下大小：

SELECT pg_size_pretty(pg_relation_size('idx_notifications_pending'));

出来是 216 kB。作为对比，我在测试环境建了个覆盖全表的普通索引 (status, created_at)，1.9 GB。216 kB 对 1.9 GB，这差距没什么好说的了。

效果对比

同一个查询再跑一遍 EXPLAIN ANALYZE：

Limit  (actual time=0.041..0.186 rows=50 loops=1)
  ->  Index Scan using idx_notifications_pending on notifications
Execution Time: 0.214 ms

823 ms 到 0.214 ms，三千多倍。当然这个倍数看看就好，之前那个全表扫描实在太拉了，没什么参考价值。但接口响应回到几毫秒以内之后，下游的超时重试立刻就消停了，这是实打实的。

写入这边我也用 pgbench 简单压了一下，模拟插入和状态更新的混合负载。部分索引方案写入吞吐基本没变化，普通全量索引方案掉了 7% 左右。索引越小，写入时维护的成本就越低，符合直觉。

踩过的坑

这玩意儿也不是建完就完事了，有几个坑我替大家踩了：

查询条件必须能推出索引条件。 索引是 WHERE status = 'pending'，查询就得老老实实写 status = 'pending'。如果你写成 WHERE status IN ('pending', 'failed')，优化器是不会用这个索引的，因为它没法保证索引里的行都满足你的条件。所以业务逻辑变了、查询条件跟着变的时候，记得回头看看索引还匹配不匹配。

索引条件里不能用 now() 这类函数。 WHERE created_at > now() - interval '7 days' 这种想都别想，建索引的时候直接报错，因为 now() 不是 immutable 的。想按时间切的话，只能退而求其次写死一个时间比如 created_at > '2025-01-01'，过段时间手动重建一次。

参数类型要对上。 我们有个服务走 prepared statement，参数推断出来的类型和索引谓词对不上，优化器死活不认这个索引。排查了半天，最后在查询里加了个显式转型才好。

另外建议定期看看哪些索引在吃灰：

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND relname = 'notifications';

写在最后

整个优化从发现到搞定花了一个下午，其中一大半时间耗在那个参数类型的坑上。部分索引说起来没什么技术含量，无非是"查询只关心一小撮数据，那索引就只放这一小撮"，但在这种查询模式固定、数据倾斜严重的场景下，效果是立竿见影的。

如果你系统里也有那种"一张大表，高频查询永远只摸其中一小部分"的场景，可以试试这个思路。动手之前记得先 EXPLAIN ANALYZE 看看执行计划，别瞎猜。