Re: : Performance Improvement Strategy - Mailing list pgsql-performance

From Mark Kirkwood
Subject Re: : Performance Improvement Strategy
Date
Msg-id 4E790E13.8060308@catalyst.net.nz
Whole thread Raw
In response to Re: : Performance Improvement Strategy  (Josh Berkus <josh@agliodbs.com>)
Responses Re: : Performance Improvement Strategy  (Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
List pgsql-performance
On 21/09/11 06:09, Josh Berkus wrote:
> Venkat,
>
>> I see lot of free spaces or free pages in Tables and Indexes. But, I need to
>> give an exact calculation on how much space will be reclaimed after VACUUM
>> FULL and RE-INDEXING.
> At present, there is no way to calculate this precisely.  You can only
> estimate, and estimates have significant error factors.  The query which
> Marcin linked for you, for example, can be as much as 500% off (although
> usually only 50% off).
>

If you have autovacuum on (which should be typical thee days), then
using the freespacemap contrib module should give very accurate results:

SELECT oid::regclass,
                pg_relation_size(oid)/(1024*1024) AS mb,
                sum(free)/(1024*1024) AS free_mb
         FROM
             (SELECT oid, (pg_freespace(oid)).avail AS free
              FROM pg_class) AS a
         GROUP BY a.oid ORDER BY free_mb DESC;


regards

Mark

pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: : Performance Improvement Strategy
Next
From: Mark Kirkwood
Date:
Subject: Re: : Performance Improvement Strategy