From: | Matt Dew <mattd(at)consistentstate(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: indexes no longer used after shutdown during reindexing |
Date: | 2012-01-11 18:22:44 |
Message-ID: | 4F0DD374.9070103@consistentstate.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 01/11/2012 11:07 AM, Scott Marlowe wrote:
> On Wed, Jan 11, 2012 at 10:42 AM, Matt Dew<mattd(at)consistentstate(dot)com> wrote:
>> 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
> You likely had an invalid index, I've seen that crop up when doing a
> create index concurrently. Just a guess. What did or does \d of the
> table and its indexes show? Look for invalid in the output.
Hi Scott,
The output of \d looked normal. Nothing weird or different than before.
From | Date | Subject | |
---|---|---|---|
Next Message | Kirill Müller | 2012-01-11 19:45:19 | Re: Enumeration of tables is very slow in largish database |
Previous Message | Andreas Kretschmer | 2012-01-11 18:09:21 | Re: add constraint deferrable, syntax error |