Re: Identifying bloated tables

On 28/08/06, Michal Taborsky - Internet Mall <michal(dot)taborsky(at)mall(dot)cz> wrote:
> Markus Schaber napsal(a):
> > Hi, Michal,
> >
> > Michal Taborsky - Internet Mall wrote:
> >
> >> When using this view, you are interested in tables, which have the
> >> "bloat" column higher that say 2.0 (in freshly dump/restored/analyzed
> >> database they should all be around 1.0).
> >
> > I just noticed some columns in pg_catalog with a bloat value <1 and a
> > negative "wasted space" - is this due to the pseudo nature of them?
>
> It is more likely due to the fact, that these numbers are just
> estimates, based on collected table statistics, so for small or
> non-standard tables the statistical error is greater that the actual
> value. You are usually not interested in tables, which have wasted space
> of 1000kB or -1000kB. Also the database must be ANALYZEd properly for
> these numbers to carry any significance.
>

I was just playing around with this table and noticed it preforms the
badly in tables with very small record sizes. This seams to be because
it ignores the system overhead (oid, xmin ctid etc) which seams to be
about 28 bytes per a record this can be quite significate in small
record tables and can cause trouble even with a smal numbers of
record. Hence I've got a table thats static and fresly "vacuum full"
which reads with a bloat of 4.

Easy to recreate problem to

Create table regionpostcode (area varchar(4), regionid int);

then insert 120000 records.

Peter.