Venkat Balaji <venkat.balaji@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
