From: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Greg Smith <greg(at)2ndquadrant(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: : Performance Improvement Strategy |
Date: | 2011-09-27 12:29:06 |
Message-ID: | CAFrxt0hft-BcE9-gBOce9QEkX_9Mme+=oCSrcXDq4SAjMWbciQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Venkat Balaji | 2011-09-27 12:31:01 | Re: : Performance Improvement Strategy |
Previous Message | Marti Raudsepp | 2011-09-27 10:29:14 | Re: Ineffective autovacuum |