indexes no longer used after shutdown during reindexing

From: Matt Dew <mattd(at)consistentstate(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: indexes no longer used after shutdown during reindexing
Date: 2012-01-11 17:42:54
Message-ID: 4F0DCA1E.5000101@consistentstate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,
I have a database that was shut down, cleanly, during an 'reindex
table' command. When the database came back up, queries against that
table started doing sequential scans instead of using the indexes as
they had been up until that point.

We tried:
1) vacuuming the table (vacuum tblName)
2) reindexing the table (reindex table tblName)
3) dropping and recreating the indexes

but none of those actions helped. We ended up recreating the table by
renaming the table and doing a create table as select * from oldTable
and readding the indexes. This worked.

This problem presented itself as an application timing out. It took
several people, several hours to track this down and solve it.

Several months ago I had two other tables also stopped using their
indexes. Those times however I don't know if a database shutdown caused
the problem.

Has anyone had this problem? If so, what specifically is the cause? Is
shutting down a database during a table rebuild or vacuum an absolute no-no?

Any and all help or insight would be appreciated,
Matt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2012-01-11 18:00:32 Re: Enumeration of tables is very slow in largish database
Previous Message vyang 2012-01-11 17:26:38 Re: Keywords