On Mon, Mar 8, 2010 at 5:13 PM, Scot Kreienkamp <skre...@la-z-boy.com>wrote:
> Hi everyone, > > I have a database that is constantly getting reloaded several times per > day from production backups and is used for reporting purposes. The > problem I'm having with it is that the database seems to be much slower > than the others I have that are more static. I suspect that is due to > the lack of query planner statistics (analyze) having been done after > each restore, which is that way due to the amount of time it takes to > accomplish. > > First, I'm hoping someone here can validate my theory. It would seem likely that this could be the problem... > Second, if > that's true, is there any way to trigger an auto-analyze on a table > automatically the first time a query touches that table? (I ask because > there is no way to shrink the amount of time a database-wide analyze > would take into the window I have to do it in. The expectations may be > a bit unrealistic here, I know.) Why not just add an 'analyze' as the last step of the restore job? > Third, what parameters can I set to > make analyze go as fast as possible, knowing that the disks are slow on > it because of the hardware? (Can't do anything about that either, FYI) > Obviously more memory the better, and setting maintenance work memory > higher also. Doing a vacuum is kind of pointless because it gets > reloaded every 2 hours, so all I really need is an analyze --I think--. Sounds like you've done what you can. How long does an analyze take? > > > I'm on 8.3.7. Since 8.3.10 is due out I'll probably upgrade to it after > it's shaken out a bit. > Why not move up to 8.4? --Scott M > Thanks for your help. Any suggestions are welcome. > > Scot Kreienkamp > skre...@la-z-boy.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
