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? Dec 10, 2014 at 21:56
  • Yes, I used both analyze and vacuum analyze 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? 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 Dec 10, 2014 at 23:05
  • ok. Glad to hear it. :) 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, privacy policy and cookie policy

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