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.
