Shane Wright wrote:
>
> Incidentally, how many passes of a table can vacuum make! Its currently
> on its third trip through the 20Gb of indices, meaning another 7 hours
> till completion [of this table]!.
>
> Assume it only does three passes? (it chooses based on the table
> continuing to be updated while vacuum is running)
As many passes at it needs to. It is limited by maintenance_work_mem
(in 7.4 I think it was sort_mem).
It needs to collect an array of tuple pointers, and it keeps them in
memory. When the array grows to maintenance_work_mem, it stops scanning
the table and scans the indexes, removing everything that points to
those tuple pointers. Then it goes back to scanning the table.
So the problem is that it scans the whole indexes many times. If you
increase maintenance_work_mem way up for this vacuum task, it will need
to wholly scan the indexes less times (hopefully only one), making the
process a lot faster.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.