Postgresql – Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

indexpostgresql

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");

Best Answer

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish.

Killed the transaction, and now the indexes are back to normal.

Related Question