我在Postgres中比较正常索引和部分索引的性能,令我惊讶的是,与正常索引相比,部分索引的性能似乎不是最优的.
创建索引后的EXPLAIN语句:
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)
查询已执行,但希望try 部分索引,因为字段is_deleted
和knowledge_end_date
将具有相同的值,并且作为部分索引可以减小整体索引大小.令我惊讶的是,随着时间和成本的增加,查询的性能变差了.
创建部分索引后的EXPLAIN语句:
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)
我不知道为什么会是这样.使用部分索引的查询不应该更快吗?