On Thu, 19 Feb 2004, Alex wrote:
> Hi,
> just a few questions on the Vaccum
>
> I run a vacuum analyze on the database every night as part of a
> maintenance job.
>
> During the day I have a job that loads 30-70,000 records into two tables
> (each 30-70k).
> This job runs 2-3 times a day; the first time mainly inserts, the 2nd,
> 3rd time mostly updates.
> Both tables have in the area of 1-3Mio records
>
> How reasonable is it to run a Vacuum Analyze before and after the
> insert/update of the data.
Running it before probably gains you little. In some circumstances
(running analyze on an empty table is one) analyzing before loading data
is counterproductive, because postgresql's query planner will be making
decisions on the 30,000th of 70,000 inserts based on a table size of very
few rows, and favoring seq scans when it should be using index scans.
vacuuming (and analyzing) after the import is a good thing.
> 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?
Likely it is loading the whole table into kernel cache.
> One more question; on one server the Vacuum Analyze before the insert
> takes approx. 2min after that the same command takes 15min.
Normal. Before hand, there are no dead tuples to harvest / put in the
fsm, but afterward there are plenty to harvest.
Make sure your fsm settings are high enough to retain all the freed pages,
or you'll wind up with table bloat.
Vacuum full every so often (off hours are best) to make sure. Do a df on
the database mount point before and after and see how much spave it
recovers.