Re: indexes no longer used after shutdown during reindexing

Matt Dew <mattd(at)consistentstate(dot)com> writes:
> On 01/11/2012 04:29 PM, Tom Lane wrote:
>> What exactly is your definition of a "clean shutdown"?

> 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

Well, a fast-mode stop would abort the reindex operation, but that
should certainly have left the catalog entries in the same state as
before, so there's no obvious reason here why the indexes would've
stopped being used.

> We're using v8.3.9

That's a tad old. Please consult
http://www.postgresql.org/docs/8.3/static/release.html
for reasons why an update might be a good idea. I don't recall any
8.3.x bugs that might be related to this, but I haven't trawled the
commit logs to see what I've forgotten, either.

> 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.

Hmm. If that's the case then we don't have to explain how an aborted
reindex operation could have affected the usability of the old indexes,
so I'm inclined to believe that it didn't. Which seems to mean that you
have a garden variety "why won't the planner use my index" issue, not
something unusual. If you no longer have the original table then it may
be impossible to investigate further; but if you can recreate the state
where it's not using the index(es), please see
http://wiki.postgresql.org/wiki/Slow_Query_Questions
and pursue the issue on pgsql-performance.

regards, tom lane