PostgreSQL：分布不均的状态字段索引优化及部分索引适用场景

我们公司有一张存储大量任务的表，核心字段如下：

id: bigint 自增主键
customer_id: bigint
created_time: Timestamp
updated_time: Timestamp
status: int (1: 初始化, 2: 处理中, 3: 成功, 4: 失败, 5: 终止)
type: int (有200+种可能取值)

受业务负载特性影响，各状态的数据分布大致如下：

status=1: <1000条
status=2: 数千条
status=3: 5亿条
status=4: 500万条
status=5: 1000万条

目前已单独为status、type、created_time、customer_id字段建立了单列索引。在部分业务场景中，我们需要监控超过一定时长未完成的任务，此时仅过滤status IN (1,2)的查询速度仍然较慢。

疑问 #

1. 建立包含(status,created_time,type)且仅包含status IN (1,2)数据的部分索引是否有效？
2. 部分索引的适用场景是什么？

1. 该部分索引的有效性 #

完全有效，且能显著提升目标查询性能，核心原因如下：

• 数据占比极低：status=1和status=2的总数据量仅万级，远小于占比90%+的status=3数据。这类部分索引的体积会非常小，远小于全表的联合索引，磁盘占用和查询时的IO开销都大幅降低。
• 完美匹配查询逻辑：监控超时任务的查询通常会包含created_time < [超时阈值]的过滤条件，可能还会按type筛选或分组。(status,created_time,type)的索引顺序正好匹配查询的过滤优先级：先通过status IN (1,2)锁定小范围数据，再用created_time过滤超时记录，最后type直接满足后续筛选/排序需求，甚至可以实现覆盖查询（如果查询的字段都包含在索引内，无需回表取数）。
• 避免回表开销：原有单列status索引只能定位到符合状态的行，还需要回表获取created_time和type；而该部分联合索引可以直接在索引内完成所有过滤和数据返回，彻底消除回表的性能损耗。

2. 部分索引的适用场景 #

部分索引（又称过滤索引）适合以下典型场景：

• 数据分布极度倾斜：当某类过滤条件对应的数据量占全表比例极低时，建立仅包含该类数据的索引，既能保证查询效率，又能大幅降低索引的存储和维护成本。
• 高频特定查询：系统中存在固定的、高频触发的查询（比如仅查询未完成任务、仅查询VIP用户数据），部分索引可以精准匹配这类查询的过滤逻辑，避免全量索引的冗余。
• 降低索引维护开销：全表索引会对所有数据的更新同步维护索引，而部分索引仅维护符合过滤条件的数据。比如任务状态从1变为3时，会自动从该部分索引中移除，减少索引的更新操作。
• 特定场景的覆盖索引：当需要为某类查询建立覆盖索引时，部分索引可以进一步缩小索引范围，让覆盖索引的体积更小、查询效率更高。

内容的提问来源于stack exchange，提问作者JackDaniels