Re: indexes no longer used after shutdown during reindexing

On 01/11/2012 04:29 PM, Tom Lane wrote:
> Matt Dew<mattd(at)consistentstate(dot)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