PostgreSQL / 系统课时
使用部分索引
使用部分索引
部分索引(Partial Index)只为满足指定谓词的行建立索引,适合把查询最常访问的一小部分数据单独组织起来。本课重点解决两个问题:什么时候值得把索引限定在一部分行,以及为什么 SQL 明明写了同样的过滤条件,优化器仍可能不使用这个索引。
学习目标
- 能根据数据分布判断部分索引是否值得建立。
- 能理解查询谓词与部分索引谓词之间的匹配关系。
- 能用
EXPLAIN验证部分索引是否命中,并识别常见失配原因。
先看数据分布,再决定是否缩小索引
假设订单表有大量历史数据,但业务后台最常查询的是仍处于待处理状态的订单。此时为全表建立 status 索引,意味着历史已完成订单也要占用索引空间;若目标查询长期只关心少数状态,部分索引更合适。
CREATE TABLE orders (
id bigint PRIMARY KEY,
tenant_id bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL,
total_amount numeric(12, 2) NOT NULL
);
CREATE INDEX idx_orders_pending
ON orders (tenant_id, created_at DESC)
WHERE status IN ('PENDING', 'PROCESSING');
EXPLAIN (COSTS OFF)
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42
AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;预期结果
Limit
-> Index Scan using idx_orders_pending on orders
Index Cond: (tenant_id = 42)实际计划会受统计信息、数据规模和其他索引影响,Index Cond 的展示细节也可能不同。关键验证点是计划是否选择 idx_orders_pending,以及是否仍需要对大量不满足部分索引谓词的行做额外扫描。
部分索引真正省下的是什么
部分索引的收益来自两处:索引条目更少、查询时要维护和访问的数据结构更小。若 PENDING 和 PROCESSING 只占总表的一小部分,而且这类查询频率很高,收益通常更明显。
但部分索引不是“给某个值单独建索引”的语法糖。它表达的是一个稳定的集合条件,例如 WHERE status IN (...)、WHERE deleted_at IS NULL。如果被索引集合随着业务演进迅速扩大,索引体积和维护成本也会同步增加,此时就需要重新评估。

谓词匹配决定能不能用
优化器必须能够判断查询条件保证目标行满足部分索引谓词。最稳妥的做法是让业务 SQL 直接包含与索引定义语义一致的条件,例如索引谓词是 status IN ('PENDING', 'PROCESSING'),查询明确限定其中一个状态。
常见失配来自应用层把条件写得过于动态,或者查询只保留了一个无法证明集合包含关系的表达式。比如应用只写 WHERE tenant_id = 42,虽然返回的数据中可能碰巧有待处理订单,但它没有保证结果行一定满足部分索引谓词,因此不能简单假设这个查询可以安全使用该索引。
用数据分布决定是否长期保留
部分索引特别依赖业务分布。可以用聚合先估计目标集合占比,再决定它是否值得单独维护。
SELECT
status,
count(*) AS rows,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status
ORDER BY rows DESC;预期结果
status | rows | pct
-------------+------+------
DONE | 9500 | 95.00
PENDING | 300 | 3.00
PROCESSING | 200 | 2.00这里的数字只是示例,实际结果取决于你的数据。重点是判断部分索引覆盖集合是否足够小,以及该集合上的查询是否足够频繁。
还要考虑更新成本。状态从 PENDING 变成 DONE 时,相关行会从部分索引覆盖范围中退出;反向变化则会重新进入索引。因此如果状态迁移非常频繁,部分索引虽然较小,却可能增加索引维护活动。设计时要同时看读收益和写入成本。
建立后如何确认它仍然值得用
上线后不要只检查“有没有这个索引”,而要持续观察目标查询是否命中、扫描量是否下降,以及索引覆盖集合是否发生结构性变化。若某个状态从 5% 扩大到 60%,原本“只索引热点集合”的假设可能已经不成立。
部分索引最适合满足一个稳定、明确且规模明显小于全表的业务子集。先验证谓词可匹配,再结合数据占比、查询频率和状态更新成本评估;只有这三点同时成立,Partial Index 才真正有治理价值。