From: | Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: : Performance Improvement Strategy |
Date: | 2011-09-20 22:05:07 |
Message-ID: | 4E790E13.8060308@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2011-09-20 22:10:49 | Re: : Performance Improvement Strategy |
Previous Message | Josh Berkus | 2011-09-20 18:09:48 | Re: : Performance Improvement Strategy |