Re: : Performance Improvement Strategy

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(dot)balaji(at)verse(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> Venkat Balaji <venkat(dot)balaji(at)verse(dot)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
>>
>
>