Re: Dump / restore for optimization?

lec <limec(at)streamyx(dot)com> wrote:

> I always have to dump & restore to make a database (which is in use for
> a few months) efficient again. I have scheduled nightly vacuum analyze,
> but only dump & restore can make the database efficient again. Has
> anyone experience this?

You might benefit from identifying the tables that are having the biggest
performance problems, identifying the keys that are most commonly used, and
using CLUSTER to physically reorder those tables on a set schedule. This
is actually a lot like dump/restore (since it physically recreates the
tables on disk) but should take less time (especially if you can narrow
the problem down to only a few tables out of your entire db)

This may or may not help, but it's something to try.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com