From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: : Performance Improvement Strategy
Date: Wed, 21 Sep 2011 10:05:07 +1200
Message-ID: <4E790E13.8060308@catalyst.net.nz> (raw)
In-Reply-To: <4E78D6EC.30205@agliodbs.com>
References: <CAFrxt0j5UysgEU8hQxZ_mGbdex8Ev-bGdGsdpOGYYSO-jO24Fw@mail.gmail.com>
<4E78D6EC.30205@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, josh@agliodbs.com
Subject: Re: : Performance Improvement Strategy
In-Reply-To: <4E790E13.8060308@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox