Postgres Pro
Downloads
Home   >   mailing lists

Thread: Vacuuming of indexes on tables.

Vacuuming of indexes on tables.

From
elein
Date:
Running version 8.1.2, 1-2G RAM. Configuration
set up to use available RAM.  Running autovacuum.

I have a table with 8500000 rows.  The table
gets only inserts (constantly).  Once a day
a range of the rows is deleted and an explicit
vacuum analyze of the table is done.

The table has 4 indexes.

Much later in the day, a vacuum analyze of the
db showed that all of the indexes for that table
required significant vacuuming, although the
table did not.

A bit later, further vacuuming showed more indexes
required vacuuming and again the table did not.

In what situation would these indexes require the
further vacuuming?  Due to the insert only activity
on the table (and the db, mostly), I cannot understand
why the indexes require further vacuuming.

Ideas?

--elein
elein@varlena.com

Re: Vacuuming of indexes on tables.

From
Tom Lane
Date:
elein <elein@varlena.com> writes:
> Much later in the day, a vacuum analyze of the
> db showed that all of the indexes for that table
> required significant vacuuming, although the
> table did not.

What do you mean by that exactly?  If it's just that the index pages
emptied by one VACUUM aren't actually recycled till the next one,
that's by design.

            regards, tom lane

Re: Vacuuming of indexes on tables.

From
elein
Date:
On Tue, Apr 18, 2006 at 09:50:04AM -0400, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > Much later in the day, a vacuum analyze of the
> > db showed that all of the indexes for that table
> > required significant vacuuming, although the
> > table did not.
>
> What do you mean by that exactly?  If it's just that the index pages
> emptied by one VACUUM aren't actually recycled till the next one,
> that's by design.
>
>             regards, tom lane

Mine is a case where a table with constant inserts has a range
of data deleted once daily.

So you are saying that to truly vacuum a table and its indexes that
the table should be vacuumed twice or more.

The order of events seems to be vacuum indexes and then vacuum the
table.  Wouldn't we get more bang if we vacuumed the table and then
the indexes?  No doubt there is a reason for the ordering as it stands
but I am not familiar with it.

Perhaps it is not optimal to fully vacuum a table and its indexes?

--elein
elein@varlena.com


>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Re: Vacuuming of indexes on tables.

From
Tom Lane
Date:
elein <elein@varlena.com> writes:
> The order of events seems to be vacuum indexes and then vacuum the
> table.  Wouldn't we get more bang if we vacuumed the table and then
> the indexes?

No, the problem is that we can't recycle removed index pages until we
are certain there are not any transactions referencing or about to
reference the pages.  Postponing the recycle to a later command seems
the only very practical way to deal with that --- you don't want VACUUM
hanging up waiting for transactions that may or may not finish any time
soon.  See the notes in access/nbtree/README.

            regards, tom lane