From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Vacuum-full very slow |
Date: | 2007-04-25 18:51:28 |
Message-ID: | 462FA330.8080402@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-04-25 19:01:38 | Re: PosegreSQL support |
Previous Message | Martin Gainty | 2007-04-25 18:31:07 | Re: Where to find kind code for STATISTIC_KIND GEOMETRY? |
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wong | 2007-04-25 18:54:38 | Re: ECPG failure on BF member Vaquita (Windows Vista) |
Previous Message | Josh Berkus | 2007-04-25 18:27:13 | Re: [HACKERS] Full page writes improvement, code update |