On Sat, 24 Apr 2004 10:45:40 -0400, "Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca>
wrote:
>[...] 87 GB table with a 39 GB index?
>The vacuum keeps redoing the index, but there is no indication as to why it
>is doing this.
If VACUUM finds a dead tuple, if does not immediately remove index
entries pointing to that tuple. It instead collects such tuple ids and
later does a bulk delete, i.e. scans the whole index and removes all
index items pointing to one of those tuples. The number of tuple ids
that can be remembered is controlled by vacuum_mem: it is
VacuumMem * 1024 / 6
Whenever this number of dead tuples has been found, VACUUM scans the
index (which takes ca. 60000 seconds, more than 16 hours), empties the
list and continues to scan the heap ...
From the number of dead tuples you can estimate how often your index
will be scanned. If dead tuples are evenly distributed, expect there to
be 15 index scans with your current vacuum_mem setting of 196608. So
your VACUUM will run for 11 days :-(
OTOH this would mean that there are 500 million dead tuples. Do you
think this is possible?
Servus
Manfred