On 28/08/06, Michal Taborsky - Internet Mall <michal.taborsky@xxxxxxx> 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.
