Search code examples
postgresqlindexingpostgresql-performancepartial-index

In Postgres partial index is taking more time and cost to execute than normal index

I was comparing the performance of a normal index and partial index in Postgres and to my surprise, the partial index performance seems sub-optimal compared to the normal index.

The explain statement after the index creation:

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)

The query became performant but want to try a partial index since the fields is_deleted and knowledge_end_date will be of the same value and as a partial index can reduce the overall index size. To my surprise, the query became less performant with time and cost.

Explain statement after partial index creation:

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)

I am not sure why this is the case. Shouldn't the query using the partial index be faster?

Solution

  • To make this a fair fight, drop the now redundant columns is_deleted and knowledge_end_date from the partial index:

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

    Then test again.

    To note, a partial index typically only makes sense once you exclude a substantial share of all rows. If WHERE NOT is_deleted AND knowledge_end_date IS NULL only excludes a few percent or less, don't go there.