Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> wrote:
> We CLUSTERED a table using mostly used Index. Application is
> performing better now.
CLUSTER can help in at least four ways:
(1) It eliminates bloat in the table heap.
(2) It eliminates bloat in the indexes.
(3) It can correct fragmentation in the underlying disk files.
(4) It can put tuples which are accessed by the same query into
adjacent locations on disk, reducing physical disk access.
An aggressive autovacuum configuration can generally prevent the
first two from coming back to haunt you, and the third may not be a
big problem (depending on your OS and file system), but that last
one is a benefit which will degrade over time in most use cases --
the order in the heap is set by the cluster, but not maintained
after that. If this ordering is a significant part of the
performance improvement you're seeing, you may want to schedule some
regular CLUSTER run. It's hard to say what frequency would make
sense, but if performance gradually deteriorates and a CLUSTER fixes
it, you'll get a sense of how often it pays to do it.
-Kevin