Thread: Vacuum and Vacuum analyze

I'm starting a new thread, apart from the one I started
earlier, just on this topic.

Should you run either (or both) of these every time you've
added data to the database?  After you've added indexes
or keys?  As I mention in my other email, I ran a query
immediately after I added the data and the response time
was dismal to say the least.  After seeing that, I tried add-
ing a few new indexes in hopes of speeding up the  query
but that didn't help matters, either.  The response time was
just as bad (although, the new indexes did shave off a
millisecond or two).

But then, on the suggestion of two fellow list members, I
ran 'vacuum analyze' from the command line (even though
I had done that already, with no apparent effect, through the
pgAdminII interface but that's another topic) then executed
the query again.  I was very happy to see that the rows were
returning in milliseconds as opposed to the (many) seconds
they were being returned previously.

So that begs the question (from a newbie), when should these
commands, 'vacuum' and 'vacuum analyze' be run and how
often?

thnx,
Christoph

On Mon, Jun 30, 2003 at 08:49:28 -0500,
  Chris Boget <chris@wild.net> wrote:
>
> So that begs the question (from a newbie), when should these
> commands, 'vacuum' and 'vacuum analyze' be run and how
> often?

The purpose of vacuum is to allow the space used by deleted (included
old versions of updated tuples) to be reused. You want to do this once
a significant fraction of your database is taken up by deleted tuples.

The purpose of analyze is to provide statistics to the planner so that
a good plan for handling queries. You generally need to run analyze
after the number of rows or distribution of your data changes signicantly.
Doing an initial load is a significant change.

You can run these commands on just some of your tables. This is helpful
when you have a large database where some tables change differently than
others.

Have you read the admin part of the documentation yet?