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
