Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> No. You'd be better off using REINDEX for that, I think.
>
> > I guess my point is that if you forget to run regular vacuum for a
> > month, then realize the problem, you can just do a VACUUM FULL and the
> > heap is back to a perfect state as if you had been running regular
> > vacuum all along. That is not true of indexes. It would be nice if it
> > would.
>
> A VACUUM FULL that invoked REINDEX would accomplish that *better* than
> one that didn't, because of the problem of duplicate entries for moved
> tuples. See my response just now to Alvaro.
Right, REINDEX is closer to what you expect VACUUM FULL to be doing ---
it mimicks the heap result of full compaction.
I think Alvero's point is that if you are doing VACUUM FULL on a large
table with only a few expired tuples, the REINDEX could take a while,
which would seem strange considering you only have a few expired tuples
--- maybe we should reindex only if +10% of the heap rows are expired,
or the index contains +10% empty space, or something like that.
Of course, that is very abitrary, but only VACUUM knows how many rows it
is moving --- the user typically will not know that.
In an extreme case with always REINDEX, I can imagine a site that is
doing only VACUUM FULL at night, but no regular vacuums, and they find
they can't do VACUUM FULL at night anymore because it is taking too
long. By doing REINDEX always, we eliminate some folks are are happy
doing VACUUM FULL at night, because very few tuples are expired.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073