PostgreSQL中如何创建排除NULL的稀疏可空列值索引

直接创建部分B-tree索引，指定过滤条件为目标列不为NULL，同时索引该列本身。这样索引只会存储非NULL值，既减小体积、提升插入效率，又能高效支持针对该列特定非NULL值的查询。

示例代码 #

假设你的表是your_table，目标列是target_column，自定义索引名称即可：

CREATE INDEX idx_your_table_target_column_non_null
ON your_table (target_column)
WHERE target_column IS NOT NULL;

为什么这能满足需求 #

• 排除NULL值：WHERE target_column IS NOT NULL的过滤条件，让索引只包含该列非NULL的行，不仅大幅压缩索引体积，插入NULL值时也无需维护索引，直接提升插入速度。
• 支持特定值查询：因为索引是基于target_column本身构建的，当你执行SELECT * FROM your_table WHERE target_column = '特定非NULL值';这类查询时，PostgreSQL会自动调用这个部分索引，效率和普通B-tree索引完全一致。
• 兼容IS NOT NULL查询：同时也能高效处理SELECT * FROM your_table WHERE target_column IS NOT NULL;这类检索所有非NULL值的需求，一举两得。

验证索引生效 #

用EXPLAIN ANALYZE查看查询计划，确认索引是否被正常使用：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE target_column = '某个非NULL值';

如果输出里出现Index Scan using idx_your_table_target_column_non_null on your_table，就说明索引已经在工作了。

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