Search Postgresql Archives

Re: indexes no longer used after shutdown during reindexing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

			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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux