Re: Disk Utilization Increases And Time for Vacuum Increases.

scott.marlowe wrote:
> On Mon, 25 Aug 2003, Jeff Boes wrote:
>
> > >One action we have consiously not done is "REINDEX" on the
> > >table. We want to avoid that as far as possible.
> >
> > Why? It's usually a very painless step, unless the table has millions and
> > millions of rows. We reindex tables with multiple indexes and several million
> > rows on a weekly basis. The only downside is that the table seems to be quite
> > "busy" during the process, which only takes a few minutes.
> >
> > AFAIK, VACUUM doesn't reclaim space taken up by indexes. In fact, the more
> > deletes you do, the larger the index space gets, and the *slower* the index
> > performs. A periodic REINDEX cleans up a lot of problems.
>
> Note that in 7.4 the fix for this is in, so if you have a chance to test
> it out with your indexes and their growth problem please test it to see if
> it works right.
>
> I haven't tested 7.4 beta1 yet very hard, just on my workstation, with
> relatively low level stuff.

I am not sure we have completely dealt with index growth in 7.4. What
we have new in 7.4 is the ability for non-FULL VACUUM to collect info on
free index pages and reuse them.

However, VACUUM FULL does not shrink the index table unless those pages
are the last pages of the file. (Could it shift free pages to the end
and then truncate index?) Also, does VACUUM FULL on an index put the
empty index pages in the FSM map? It doesn't do that for heap pages
because there are none after vacuum, but there might be free index pages
that we should record.

Interesting I found a reference of doing an auto-reindex as part of
VACUUM FULL:

#ifdef NOT_USED

/*
* reindex in VACUUM is dangerous under WAL. ifdef out until it
* becomes safe.
*/
if (reindex)
{
vac_close_indexes(nindexes, Irel);
Irel = (Relation *) NULL;
activate_indexes_of_a_table(onerel, false);
}
#endif /* NOT_USED */

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073