PostgreSQL中为IS NULL/IS NOT NULL查询创建高效索引的方法

单列场景优化 #

如果是单列的IS NULL/IS NOT NULL查询，你可以保留基于(col IS NULL)的函数索引，按以下步骤操作确保IS NOT NULL查询能利用该索引：

• 更新统计信息：执行ANALYZE <table_name>;，让PostgreSQL获取最新的列分布数据，优化器才会考虑使用索引。
• 验证等价性匹配：PostgreSQL优化器在统计信息充足时，应当能识别col IS NOT NULL等价于(col IS NULL) = false，从而扫描索引中值为false的条目。如果仍未触发索引，可手动改写查询为WHERE (col IS NULL) = false后再查看执行计划。

若上述方法无效，可单独创建针对IS NOT NULL的表达式索引：

CREATE INDEX idx_col_not_null ON <table_name> ((col IS NOT NULL));

两种查询将分别对应各自的索引，优化器会自动选择合适的索引执行。

复合索引场景（你的示例） #

针对obj_id+deleted_at+mute_until的复合查询，提供三种可行方案：

方案1：更新统计信息并验证索引可用性 #

首先更新表的统计信息：

ANALYZE table1;

查看执行计划，如果优化器仍选择全表扫描，可能是因为过滤后的数据量占比过高（通常超过表的30%时，全表扫描效率更高）。若数据量占比低但未使用索引，可临时禁用全表扫描验证索引是否可用：

SET enable_seqscan = OFF;
-- 执行你的目标查询，查看执行计划是否使用索引
SET enable_seqscan = ON; -- 恢复默认设置

若验证有效但优化器不自动选择，可调整random_page_cost参数（降低该值会让优化器更倾向于使用索引）。

方案2：用COALESCE转换NULL为固定值 #

将NULL的timestamp转换为极小的固定值（如'0001-01-01'::timestamp），创建基于转换后值的复合索引：

CREATE INDEX table1_obj_id_deleted_mute_idx ON table1 (
  obj_id,
  COALESCE(deleted_at, '0001-01-01'::timestamp),
  COALESCE(mute_until, '0001-01-01'::timestamp)
);

此时：

• deleted_at IS NULL等价于COALESCE(deleted_at, '0001-01-01'::timestamp) = '0001-01-01'::timestamp
• deleted_at IS NOT NULL等价于COALESCE(deleted_at, '0001-01-01'::timestamp) != '0001-01-01'::timestamp

PostgreSQL优化器会自动将你的原始查询转换为匹配索引的表达式，从而利用该索引加速两种判断场景的查询。

方案3：创建针对性部分索引 #

根据实际查询的组合场景，创建多个部分索引，每个索引对应一种布尔判断组合：

-- 覆盖deleted_at IS NULL + mute_until IS NULL的场景
CREATE INDEX table1_obj_id_del_null_mute_null_idx ON table1 (obj_id) WHERE deleted_at IS NULL AND mute_until IS NULL;
-- 覆盖deleted_at IS NOT NULL + mute_until IS NOT NULL的场景
CREATE INDEX table1_obj_id_del_notnull_mute_notnull_idx ON table1 (obj_id) WHERE deleted_at IS NOT NULL AND mute_until IS NOT NULL;
-- 按需补充其他组合，比如deleted_at IS NULL + mute_until IS NOT NULL
CREATE INDEX table1_obj_id_del_null_mute_notnull_idx ON table1 (obj_id) WHERE deleted_at IS NULL AND mute_until IS NOT NULL;

部分索引的体积更小，优化器能精准匹配对应的查询场景，提升查询效率。

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