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

On 11/25/09 4:18 AM, "Matthew Wakeling" <matthew(at)flymine(dot)org> wrote:
>
> 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.
>

For tables without an index, you still need something. Vacuum full isn't
that bad here, but cluster has other advantages.
Ideally, you could CLUSTER without using an index, maybe something like
CLUSTER table using (column a, ...)
To order it by specific columns Or even simply
CLUSTER using ()
For when you don't care about the order at all, and just want to compact the
whole thing to its proper size (including fillfactor) and most likely
defragmented too.

Additionally, I've found it very important to set fillfactor to something
other than the default for tables that have lots of updates, especially if
there are bulk updates on non-indexed columns.