Re: Need to determine how badly tables need vacuuming

http://www.varlena.com/GeneralBits/107.php has an explanation
of the statistics views pg_stat*.  Perhaps those would help.

--elein
elein@varlena.com

On Wed, May 11, 2005 at 04:12:11PM -0700, Marc Munro wrote:
> On a 7.3 production system with limited downtime available, we can
> rarely take the time to run vaccuum full.  From time to time though,
> performance of some of the tables becomes an issue and we have to
> perform a full vaccum on those tables.
>
> We'd like to be able to better plan these operations, so:
>
> Is there a query that will return an estimated row count as well as an
> estimated unused tuple count for each table?  Right now we're figuring
> this stuff out by manually by reading the vacuum report.  It'd be nice
> to have a query on hand that returns a list of tables with more than 30%
> unused and more than 100k rows unused.
>
> I envision these columns being returned:
>
> table_name, %unused, tuples, unused_tuples, MB_of_disk
>
> It looks like the estimated row count can be found in pg_class.  I can
> figure out the size on disk by looking at the physical files.  But how
> can I figure out the number of dead tuples without actually doing a
> vacuum?
>
> Thanks.
>
> __
> Marc Munro
>