Re: VACUUM Question

Alex <alex(at)meerkatsoft(dot)com> writes:

> How reasonable is it to run a Vacuum Analyze before and after the
> insert/update of the data.

On a busy system you should run vacuum more often than once per day.

You should probably run a VACUUM after the update. And running ANALYZE at the
same time isn't a bad idea, especially if the update changes the distribution
a lot.

There won't be anything to VACUUM after the insert, but perhaps you still want
to run ANALYZE. Note that a plain ANALYZE uses a statistical sample which is
much faster, whereas VACUUM ANALYZE has to look at every record anyways so
it's slower but produces more accurate statistics. If you don't have
performance problems then using VACUUM ANALYZE isn't a bad idea, but it's
probably overkill.

> Also, I noticed that i get quite some performance improvement if I run a
> count(*) on the two tables before the insert. Any reasons for that?

Uh, just preloading the kernel cache with blocks from the table and index?

> One more question; on one server the Vacuum Analyze before the insert takes
> approx. 2min after that the same command takes 15min.

You might try a VACUUM FULL sometime when you can deal with 15min of downtime
or so. Actually it would probably be longer. Perhaps the table that's taking
15min has a ton of extra dead tuples left over from the fsm settings being too
low and/or vacuum being too infrequent.

--
greg