Hello, Thanks for your suggestions !
We CLUSTERED a table using mostly used Index. Application is performing better now. Thanks VB On Tue, Sep 27, 2011 at 6:01 PM, Venkat Balaji <venkat.bal...@verse.in>wrote: > 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 >>> >> >> >
