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 >
