On 01/12/2012 01:21 PM, Tom Lane wrote:
Matt Dew<mattd@xxxxxxxxxxxxxxxxxxx> 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.)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
