Re: Bloated Table

Brad Nicholson wrote:
> On Wed, 2009-05-27 at 11:15 -0400, Tom Lane wrote:
> > =?iso-8859-1?Q?Alexander_Sch=F6cke?= <asc(at)turtle-entertainment(dot)de> writes:
> > > I'm using a view
> > > (http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html) to
> > > display the bloat (unused disk space) of the tables in a PostgreSQL
> > > database.
> >
> > I wouldn't trust the calculations that view does in the least.
> > You might look at contrib/pgstattuple if you want numbers that
> > have some relationship to reality (and are correspondingly more
> > expensive to get :-()
>
> Is the referenced query reliable for even estimating, or is it flat our
> wrong?
>
> Co-workers that were PGCon are saying that this is becoming a
> popular/accepted way to check for bloated tables.

If "ma" is supposed to be "maxalign", then this code is broken because
it only reports mingw32 as 8, all others as 4, which is wrong.

However I think the big problem is that it relies on pg_class.relpages
and reltuples which are only accurate just after VACUUM, only a
sample-based estimate just after ANALYZE, and wrong at any other time
(assuming the table has any movement).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.