postgresql ignore index after vacuum

Asked Jun 23 '16 at 20:35

Active Jun 23 '16 at 22:11

Viewed 129 times

I have the following index:

CREATE INDEX in_table_1
  ON table_1
  USING btree
  ((COALESCE(col_1, 30001231::bigint)), col_2);

and the following select

select * from tabela_1
where coalesce(col_1, 30001231) >= 20150630 and col_1 <= 20150630

Im having a performance problem, I checked and my select is not using the index I created.

If I drop and recreate the index, my select uses it.

I noticed that after running a vacuum on the table, the problem starts again.

The vacuum is doing something that makes the database stop using the index?

edited Jun 23 '16 at 22:11

asked Jun 23 '16 at 20:35

user3362352

2

https://wiki.postgresql.org/wiki/Slow_Query_Questions – Jun 23 '16 at 22:11
A functional index on COALESCE() makes little sense to mee. Why not a conditional index on `col_1 IS NOT NULL) (makes little sense either) and/or one on `col_1 IS NULL` ? – wildplasser Jun 23 '16 at 22:51

0 Answers0