2

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? – Joishi Bodio Dec 10 '14 at 21:56
  • Yes, I used both analyze and vacuum analyze – Dan Mazzini Dec 10 '14 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? – Joishi Bodio Dec 10 '14 at 22:13
  • Thanks Joishi, I got the solution from IRC - it was a long running transaction, the indexes themselves were OK – Dan Mazzini Dec 10 '14 at 23:05
  • ok. Glad to hear it. :) – Joishi Bodio Dec 10 '14 at 23:27
3

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.