Re: [HACKERS] Index recreation in vacuum

> > Let's look at index using during vacuum.  Right now, how does vacuum
> > handle indexes when it moves a tuple?  Does it do each index update as
> > it moves a tuple?  Is that why it is so slow?
> >
> 
> Yes,I believe so.  It's necessary to keep consistency between heap
> table and indexes even in case of abort/crash.
> As far as I see,it has been a big charge for vacuum.

In fact, maybe we just need to look at the ability to recreate the
entire table/index in one big function.  We could do a sequential scan
of the table and if we find > X number of rows that are expired, we
decide to do a full recreate of the table with all new indexes vs.
doing a vacuum.  This seems to be the core of what the REINDEX function
is doing anyway.  

In fact, I wonder if we should enable a % parameter to VACUUM, so vacuum
does something only of X% of the disk space will be saved by the vacuum.
Currently if someone deletes the first row of a able, every row is moved
to save a few bytes of disk space.  That is certainly a waste, and
telling people they have to vacuum every night is probably a waste in
most cases too, but we don't give administrators the ability to control
when a vacuum is a good idea.

We could get ALTER TABLE DROP COLUMN working too by recreating the table
without the dropped column.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026