Re: indexes no longer used after shutdown during reindexing

On 01/12/2012 01:21 PM, Tom Lane wrote:
> 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

Thanks Tom.

An interesting sidenote we realized. the nice system shutdown script
/etc/init.d/postgres doesn't actually wait for the db to be down, it
just waits for pg_ctl to return.

I'm guessing it's not good when the box shuts down before postgres is.

Matt