On 01/12/2012 01:21 PM, Tom Lane wrote:
> Matt Dew<mattd@consistentstate.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.
>
Thanks Tom. I have the original database stored away for investigation.
This was a serious problem so we're investigatng how to prevent this in
the future. It's strange because even though it looks like this problem
did happen before the reboot, it was once in a while. After the reboot
it was everytime and the application completely stopped working.
Plus after the reboot even a simple query against the table:
select * from tbl where id=1; was/is doing sequential scans.
It's a smallish table, just under 5 million rows.
Thanks for the link. I'm using that.
It's on a netapp if that matters. (Not my choice.)