I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column.
While trying to optimize some queries, I created a new partial index on the same column. Using EXPLAIN I noticed that the index wasn't used, so I tried to drop the original full index.
The partial index was still not used (a sequential scan was used instead). After some other tests, I dropped the new partial index and restored the old one. So the situation should be exactly the same as at the start. But the re-created index is not used, not even with the simplest of queries:
SELECT * FROM table WHERE status = 'status'
It still insists to do a full scan. I tried analyze, then vacuum analyze, nothing. Before recreating it, the same identical index was used. Why did this happen? How can I make PostgreSQL use the index again?
This is the explain:
"Seq Scan on table (cost=0.00..350340.22 rows=246 width=579)" " Filter: ((status)::text = 'partial'::text)"
This is the index:
CREATE INDEX index_table_on_status ON table USING btree (status COLLATE pg_catalog."default");