Thread: Need to determine how badly tables need vacuuming

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

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
>

Marc Munro <marc@bloodnok.com> writes:
> Is there a query that will return an estimated row count as well as an
> estimated unused tuple count for each table?

See the contrib/pgstattuple module.

            regards, tom lane

Thanks to both Elein and Tom.  pgstattuple (and dbsize) from contrib
gave me exactly what I wanted.  The statistics views give me something
extra.

__
Marc