Forgot to mention - Kevin,
CLUSTER seems to be an very interesting concept to me. I am thinking to test the CLUSTER TABLE on our production according to the Index usage on the table. Will let you know once i get the results. Regards, VB On Tue, Sep 27, 2011 at 5:59 PM, Venkat Balaji <venkat.bal...@verse.in>wrote: > We had performed VACUUM FULL on our production and performance has improved > a lot ! > > I started using pg_stattuple and pg_freespacemap for tracking freespace in > the tables and Indexes and is helping us a lot. > > Thanks for all your inputs and help ! > > Regards, > VB > > > On Thu, Sep 22, 2011 at 12:11 AM, Kevin Grittner < > kevin.gritt...@wicourts.gov> wrote: > >> Venkat Balaji <venkat.bal...@verse.in> wrote: >> >> > If i got it correct, CLUSTER would do the same what VACUUM FULL >> > does (except being fast) >> >> CLUSTER copies the table (in the sequence of the specified index) to >> a new set of files, builds fresh indexes, and then replaces the >> original set of files with the new ones. So you do need room on >> disk for a second copy of the table, but it tends to be much faster >> then VACUUM FULL in PostgreSQL versions before 9.0. (Starting in >> 9.0, VACUUM FULL does the same thing as CLUSTER except that it scans >> the table data rather than using an index.) REINDEX is not needed >> when using CLUSTER or 9.x VACUUM FULL. Older versions of VACUUM >> FULL would tend to bloat indexes, so a REINDEX after VACUUM FULL was >> generally a good idea. >> >> When choosing an index for CLUSTER, pick one on which you often >> search for a *range* of rows, if possible. Like a name column if >> you do a lot of name searches. >> >> -Kevin >> > >
