PostgreSQL中部分索引执行性能低于普通索引的原因排查

问题场景 #

在PostgreSQL中测试普通索引与部分索引的性能差异时，结果与预期相悖：

• 普通索引：查询执行成本0.56..7169.58，实际耗时1.485..2.284毫秒，返回42行
• 部分索引：添加过滤条件where not is_deleted and knowledge_end_date is null后，查询执行成本升至0.56..331556.57，实际耗时602.011..1078.219毫秒，返回行数同样为42行

相关SQL与执行计划 #

普通索引创建语句

CREATE INDEX sett_ked_ped_idx ON public.settlement USING btree (client_id, is_deleted, knowledge_end_date DESC, period_end_date)

查询语句

SELECT "settlement"."payee_email_id"
FROM "settlement"
WHERE ("settlement"."client_id" = 3
       AND NOT "settlement"."is_deleted"
       AND "settlement"."knowledge_end_date" IS NULL
       AND "settlement"."period_end_date" = '2023-06-30T23:59:59.999999+00:00'::timestamptz);

普通索引执行计划

INDEX Scan USING sett_ked_ped_idx ON settlement (cost=0.56..7169.58 ROWS=3014 width=28) (actual TIME=1.485..2.284 ROWS=42 loops=1)

部分索引创建语句

CREATE INDEX sett_ked_ped_idx ON public.settlement USING btree (client_id, is_deleted, knowledge_end_date DESC, period_end_date) where not is_deleted and knowledge_end_date is null

部分索引执行计划

INDEX Scan USING sett_ked_ped_idx ON settlement (cost=0.56..331556.57 ROWS=736 width=28) (actual TIME=602.011..1078.219 ROWS=42 loops=1)

可能的原因 #

1. 统计信息不准确
   PostgreSQL优化器依赖表和索引的统计信息估算执行成本。部分索引创建后若未及时更新统计信息，优化器可能错误估算符合条件的行数（执行计划中估算行数从3014降至736，但实际均为42），进而选择低效的执行策略。

2. 部分索引列设计冗余
   部分索引已通过where子句过滤掉is_deleted = true和knowledge_end_date IS NOT NULL的行，索引中仍保留这两个列会浪费存储空间，降低索引紧凑性，导致索引扫描的IO成本升高。

3. 索引名称重复的潜在问题
   两次创建的索引使用同一名称sett_ked_ped_idx，若未先删除旧索引会直接报错；若删除后重建，可能存在缓存未清理的情况，导致优化器未正确识别新索引的结构。

解决建议 #

• 更新统计信息：执行ANALYZE settlement;，让优化器获取最新的表和索引数据分布，修正成本估算。
• 优化部分索引结构：移除索引中冗余的过滤列，重新创建部分索引：

  CREATE INDEX sett_ked_ped_partial_idx ON public.settlement USING btree (client_id, period_end_date) 
  WHERE NOT is_deleted AND knowledge_end_date IS NULL;
  

  精简后的索引体积更小，扫描时能更快定位目标数据。
• 验证索引有效性：使用\di sett_ked_ped_idx（psql命令）查看索引的定义、大小和行数，确认索引符合预期；重新执行EXPLAIN ANALYZE验证执行计划的变化。

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