PostgreSQL中针对deleted_at IS NULL查询的索引优化方案咨询

先明确核心背景：

• 表my_table已有user_id、group_id等单列/复合索引
• 新增查询需过滤deleted_at IS NULL（软删除未标记），且绝大多数记录的deleted_at为NULL
• 示例慢查询：

select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null

以下是对四种候选方案的逐一分析：

方案1：为deleted_at is null创建表达式索引 #

CREATE INDEX deleted_at_is_null ON my_table ((deleted_at is null));

• 性能预期：完全无效，甚至拖慢查询。因为绝大多数记录满足deleted_at IS NULL，这个索引里几乎全是true的条目，PostgreSQL会判定扫描此索引和扫全表效率差不多，根本不会选用它。
• 分析思路：表达式索引仅当目标条件的结果集占比极低时才有价值（比如仅10%以下记录满足），当前数据分布完全不匹配。

方案2：替换现有索引为包含deleted_at is null的复合索引 #

• 性能预期：收益极低，且风险高。如果把group_id的单列索引换成(group_id, (deleted_at is null))，由于deleted_at is null的区分度几乎为0，索引体积会比原索引大，但查询时依然要扫描该分组下的所有行（因为几乎全是NULL），和原索引加过滤的效率没区别。此外，替换索引会中断依赖原索引的其他查询，存在业务风险。
• 分析思路：复合索引的核心是高区分度字段在前，低区分度字段的添加只会增加索引维护成本，对查询提速无帮助。

方案3：额外添加包含目标条件的复合索引 #

• 性能预期：如果建对索引，这是最优解。推荐创建覆盖型部分复合索引：

CREATE INDEX idx_my_table_group_user_active ON my_table (group_id, user_id) WHERE deleted_at IS NULL;

这个索引完美匹配示例查询：

1. 用group_id快速定位目标分组，过滤无关行；
2. 包含user_id，无需回表查询原数据（覆盖索引）；
3. 仅保留deleted_at IS NULL的行，索引体积略小于全表索引；
4. 可直接在索引内完成count(distinct user_id)的计算，效率拉满。

• 分析思路：不替换现有索引，避免影响其他查询；针对性创建匹配查询的覆盖索引，是PostgreSQL优化特定查询的标准实践。

方案4：为现有索引添加where deleted_at is not null的部分索引 #

• 性能预期：完全无效，且增加冗余。你的目标是查询未删除（deleted_at IS NULL）的记录，这类索引是针对已删除数据的，对当前查询毫无帮助。同时，现有索引已经包含所有记录，新增的部分索引只会增加写入时的索引维护开销，纯粹浪费资源。
• 分析思路：部分索引的过滤条件必须匹配目标查询的需求，方向完全相反的索引没有任何价值。

关于deleted_at单列索引的误区纠正 #

你认为单独为deleted_at建索引冗余的观点是对的——在绝大多数记录为NULL的情况下，扫描deleted_at单列索引找NULL和扫全表效率一致，PostgreSQL不会选用该索引。只有当deleted_at IS NULL的记录占比极低时，单列索引才有意义。

最佳实践总结 #

1. 直接排除方案1、4，无任何实用价值；
2. 放弃方案2，风险高收益低；
3. 采用方案3的优化版本：创建覆盖型部分复合索引，精准匹配目标查询，同时不影响现有业务的索引依赖。

内容的提问来源于stack exchange，提问作者just-some-questions