Re: Query times change by orders of magnitude as DB ages

On Wed, 25 Nov 2009, Richard Neill wrote:
>> On Sun, 22 Nov 2009, Richard Neill wrote:
>>> Worse still, doing a cluster of most of the tables and vacuum full analyze
>>
>> Why are you doing a vacuum full? That command is not meant to be used
>> except in the most unusual of circumstances, as it causes bloat to indexes.
>
> We'd left it too long, and the DB was reaching 90% of disk space. I
> didn't realise that vacuum full was ever actively bad, only sometimes
> unneeded. I do now - thanks for the tip.

The problem is that vacuum full does a full compact of the table, but it
has to update all the indexes as it goes. This makes it slow, and causes
bloat to the indexes. There has been some discussion of removing the
command or at least putting a big warning next to it.

> So, having managed to bloat the indexes in this way, what can I do to
> fix it? Will a regular vacuum do the job?

In fact, cluster is exactly the command you are looking for. It will drop
the indexes, do a complete table rewrite (in the correct order), and then
recreate all the indexes again.

In normal operation, a regular vacuum will keep the table under control,
but if you actually want to shrink the database files in exceptional
circumstances, then cluster is the tool for the job.

Matthew

--
Matthew: That's one of things about Cambridge - all the roads keep changing
names as you walk along them, like Hills Road in particular.
Sagar: Yes, Sidney Street is a bit like that too.
Matthew: Sidney Street *is* Hills Road.