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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
