Re: indexes no longer used after shutdown during reindexing

On 01/11/2012 04:29 PM, Tom Lane wrote:
> Matt Dew<mattd@consistentstate.com>  writes:
>>      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.
> What exactly is your definition of a "clean shutdown"?  At the very
> least you'd have had to abort the session running the reindex.  Also,
> what PG version is this, and what are the index definitions?
Is a reboot command considered a clean shutdown?  It's a redhat box
which called /etc/init.d/postgresql stop, which does:  pg_ctl stop -D
'$PGDATA' -s -m fast

We're using v8.3.9

     "idx1" UNIQUE, btree (id)
     "idx_2" btree (homeaddress)
     "idx_3" btree (f3)
     "idx_4" btree (lower(firstname::text) varchar_pattern_ops)
     "idx_5" btree (lower(lastname::text) varchar_pattern_ops)
     "idx_6" btree (lower(lastname::text) varchar_pattern_ops,
lower(firstname::text) varchar_pattern_ops, id, f5)
     "idx_7" btree (s2id)
     "idx_8" btree (sid, lower(memberusername::text)
varchar_pattern_ops, lower(email::text) varchar_pattern_ops, birthdate)
     "idx_9" btree (id, f5) WHERE f5 = false

I'm in a rabbit hole. I dug in more and learned that that problem may
have existed before the shutdown.  I believe the root problem is still
the same though; having to recreate the table to get it to use indexes.

thanks for any help,
Matt



>             regards, tom lane