Re: Answering my own question - Mailing list pgsql-novice

As in another post, VACUUM ANALYZE has fixed my index, optimer, query plan
problem.

A follow-up question I have is about VACUUM FULL.

There's no mention of "FULL" as an arguement to VACUUM in the 7.1.3
doc/html  on-line reference manual.  Was FULL added to a later release?


Gerry




        
                      "Francisco
        
                      Reyes"                   To:      gerry.smit@lombard.ca
        
                      <lists@natserv.c         cc:      ghaverla@freenet.edmonton.ab.ca, pgsql-novice@postgresql.org
        
                      om>                      Fax to:
        
                                               Subject: Re: [NOVICE] Answering my own question
        
                      15/05/2002 03:29
        
                      PM
        

        

        




On Wed, 15 May 2002 gerry.smit@lombard.ca wrote:

> Now THAT's REALLY fascinating.  We upgraded from 6.5 to 7.1 about 4-5
> months ago , and only got this problem after that. I leave "ANALYZE" out
of
> VACUUM due to the large volume of (I though needless) output that gets
> piped to my cron_log.

Display of activities is controllable through settings.
It is also somewhat silly to not run analyze just because it displays too
much data in a log.

> Does anyone have a feal for how "necessary" VACUUM ANALYZE is over
"VACUUM"
> Gerry

Like many other things in live the answer is: it depends.
Vacuum is about been able to reuse space which was used by rows that have
been deleted.

Vacuum Full is about recovering the disk space used by deleted rows.

Analyze is about re-computing statistics that the analyzer uses to
determine best strategies.

The answer to your question will be directly related to how much and how
often new data is inserted in your database.

If you NEVER change your data or make inserts then you NEVER have to run
either vacuum or analyze.

If you have a very heavily loaded environment with hundreds/thousands
transactions per minute, then an hourly vacuum analyze would probably
help.

Why don you tell us more about your particular scenario?

For instance I do ONE big load every morning from a production Foxpro
system to my reporting PostgreSQL system. I do one vacuum analyze just
after the load, then I compute some stats and populate some files, then
another vacuum analyze. At 10pm I do a vacuum full. This works great for
me.

Before I was doing one vacuum analyze after the stats computations and it
was horrible. Something which takes to compute less than an hour if I have
done a vacuum analyze took about 4 hours without vacuum analyze and that
was just ONE of the things that needed to be done. The first vacuum
analyze takes about 50 minutes and the second takes about 8 minutes. A lot
less than the 4+ hours of savings from having run vacuum analyze vs doing
the stats computations without them.