Re: Vacuum-full very slow

Martijn van Oosterhout wrote:
> On Wed, Apr 25, 2007 at 09:36:35AM -0700, Steve Crawford wrote:
>> Hmmm, why would cluster be faster?
>
> Basically, vacuum full moves tuples from the end to the beginning of a
> table so it can compact the table. In the process it needs to update
> all the indexes too. So you save heap space but it tends to fragment
> your index. Lots of disk writes also.
>
> OTOH, cluster simply scans the table, sorts it, writes it out then
> rebuilds the indexes. If you've removed a lot of tuples, empirically
> it's faster.
>
> VACUUM FULL is discouraged these days, simply becuase it isn't actually
> as efficient as you might expect. Better to make sure it doesn't grow
> big in the first place, and use CLUSTER to rebuild the table if you
> really need to.
>
> Hope this helps,

So my mental-model is utterly and completely wrong. My assumption was
that since a full vacuum requires an access exclusive lock, it would do
the intelligent and efficient thing which would be to first compact the
table and then recreate the indexes.

Am I reading that what it actually does is to thrash around keeping
indexes unnecessarily updated, bloating them in the process?

Will cluster reduce the on-disk size like vacuum does?

(

And am I the only one who thinks the cluster command is backwards -
after all it is the table that is being reordered based on an index so:

CLUSTER tablename ON indexname

seems way more intuitive than

CLUSTER indexname ON tablename

)

Cheers,
Steve