Postgres partial index not taken into acount

user_598442 0
Viewed 48

I have a table with ~200 million rows. On that table, I have a flag deprecated (boolean) and a date_time (timestamp).

My table is filled with deprecated values. At the moment I have 3 values on the 200 million that are not deprecated.

So I tried to add a partial index on the deprecated flag, but postgres does not take it into consideration and keep doing a Seq scan.

Here is how the index was created:

CREATE INDEX api_kpi_date_time_idx ON public.api_kpi USING btree (date_time) WHERE (deprecated = false)

And I try to run an explain:

explain select ak.id from api_kpi ak
where ak.deprecated is false and date_time < now()

I get the following:

Seq Scan on api_kpi ak  (cost=0.00..10208288.72 rows=80594624 width=37)
  Filter: ((deprecated IS FALSE) AND (date_time < now()))

And the query takes almost 5 minutes to run.

What do I need to do to make sure that Postgres takes the index into consideration and only do the date comparison on the subset from the index?

indexing postgresql
1 Answers

Found the error after playing with the query.

deprecated IS FALSE != deprecated = FALSE. When creating a partial index, the query has to be exactly the same for Postgres to pick it up.

user_5984420
Related