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

Cool.... Thanks. Glad I'm on the right track.

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?

Due to the amount of time it takes. The disks are slow enough to make a
database-wide analyze painful since I would have to repeat it every 1-2
hours, IE every reload time.

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?

Last I tried it, it took 15 minutes on a 30 gig database while it was
being used.

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?

Because I'm constrained by our application developers who don't have the
time to vet our app against 8.4 yet. I've been pushing for it for the
last 2 months.

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