Re: : Performance Improvement Strategy

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
>