Re: : Performance Improvement Strategy

[Venkat Balaji <venkat.balaji@xxxxxxxx>]

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.Grittner@xxxxxxxxxxxx> wrote:

Venkat Balaji <venkat.balaji@xxxxxxxx> 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