Re: autovacuum question

On Mon, Mar 8, 2010 at 5:13 PM, Scot Kreienkamp <SKreien(at)la-z-boy(dot)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
> skreien(at)la-z-boy(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>