Re: : Performance Improvement Strategy

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(dot)balaji(at)verse(dot)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(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
>>>
>>
>>
>