PostgreSQL 部分索引是什么？适合解决哪些查询性能问题？

简化版

部分索引是在满足某个 WHERE 条件的行上建索引，比如只给 status = 'UNPAID' 的订单建索引。它适合少量热点子集查询，能减少索引体积和维护成本，但查询条件必须能匹配索引谓词，否则优化器不会使用。

详细版

普通索引覆盖整张表，部分索引只覆盖一部分行。例如订单表 1000 万行，未支付订单只有 5 万行，如果业务经常查未支付订单，用全量 (status, created_at) 索引会比较大；用部分索引可以只索引未支付数据。

典型写法：

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

部分索引的价值在于让索引更小、更容易进缓存、写入维护成本更低。风险在于条件写法、参数化 SQL、数据分布变化都会影响命中率。

完整版教学

一、部分索引解决的是什么问题

很多表的数据分布极不均匀。订单表里 99% 是已完成订单，只有 1% 是待支付订单；用户表里 95% 是正常用户，只有少量被冻结用户。

如果查询只关心这一小撮数据，给全表建索引就像为了找 5 万条未支付订单，把 1000 万条订单都放进索引。索引能用，但体积、缓存和写入维护都更贵。

部分索引把索引范围缩小到“真正要查的子集”，这就是它的核心动机。

二、基本语法和查询匹配

部分索引的语法是在 CREATE INDEX 末尾加谓词条件：

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

对应查询通常要包含能推出该谓词的条件：

SELECT *
FROM orders
WHERE status = 'UNPAID'
ORDER BY created_at DESC
LIMIT 20;

如果查询没有 status = 'UNPAID'，优化器不能假设只查未支付订单，自然不能用这个索引。

三、为什么它能减少成本

假设订单表 1000 万行，未支付 5 万行。全量索引要覆盖 1000 万个索引项，部分索引只覆盖 5 万个，数量只有 0.5%。

全量索引项：10,000,000
部分索引项：50,000
比例：0.5%

索引更小意味着更多索引页能留在内存里，查询扫描更少页；写入时，只有满足条件的行才需要进入这个索引。

四、适合和不适合的场景

场景	是否适合	原因
少量待处理状态	适合	热点子集小
软删除只查未删除	适合	deleted_at IS NULL 常见
每个状态都差不多多	不适合	子集不够小
查询条件变化很大	不适合	谓词难匹配

部分索引最适合“少量、稳定、频繁查询”的子集，不适合所有条件都随用户筛选自由组合的场景。

五、参数化 SQL 的坑

部分索引要求优化器能判断查询条件满足索引谓词。某些参数化 SQL 里，条件值在计划阶段不明确，可能影响部分索引使用。

例如应用总是写 WHERE status = $1，当 $1='UNPAID' 时语义上能用，但通用计划未必总能针对具体值选择部分索引。实际要用 EXPLAIN 验证。

这也是为什么部分索引不能只看建表语句，必须结合真实 SQL 和执行计划。

六、常见误区与追问

• 误区：部分索引比普通索引一定更好。 只有子集足够小且查询稳定时收益明显。
• 误区：建了部分索引查询就一定会用。 查询条件必须能匹配或推出索引谓词。
• 误区：部分索引不会增加写入成本。 满足谓词的插入和更新仍要维护索引。
• 追问：软删除适合怎么建？ 常见是 WHERE deleted_at IS NULL，只优化有效数据查询。
• 追问：如何验证是否命中？ 用 EXPLAIN 或 EXPLAIN ANALYZE 看是否走该索引。

七、加强记忆

记忆钩子：部分索引像给“待办事项”单独做索引卡片，而不是把整本历史档案都搬出来翻。

回答这题要讲清“索引只覆盖部分行、查询谓词必须匹配、收益来自更小的索引、风险来自数据分布和 SQL 写法”。这样不会把它讲成普通索引的换皮。