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");
  • Have you ANALYZED the table? Commented Dec 10, 2014 at 21:56
  • Yes, I used both analyze and vacuum analyze Commented Dec 10, 2014 at 21:57
  • 1
    Please paste both indexes you have (or had) on the table as well as the sql you ran to generate the explain you have shown? Commented Dec 10, 2014 at 22:13
  • Thanks Joishi, I got the solution from IRC - it was a long running transaction, the indexes themselves were OK Commented Dec 10, 2014 at 23:05
  • ok. Glad to hear it. :) Commented Dec 10, 2014 at 23:27

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.