autovacuum question

From: "Scot Kreienkamp" <SKreien(at)la-z-boy(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: autovacuum question
Date: 2010-03-08 22:13:04
Message-ID: 59E62072D4EFFF4BB912EC607F8FA60B741CE9@lzbs6304.na.lzb.hq
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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. 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.) 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--.

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.

Thanks for your help. Any suggestions are welcome.

Scot Kreienkamp
skreien(at)la-z-boy(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2010-03-08 22:46:15 Re: managing tablespaces like files?
Previous Message Sam Carleton 2010-03-08 21:58:24 Re: managing tablespaces like files?