Re: Bloated Table

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Brad Nicholson wrote:
>> On Wed, 2009-05-27 at 11:15 -0400, Tom Lane wrote:
>>> I wouldn't trust the calculations that view does in the least.

> 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.

I didn't bother to go through every detail of the calculations, but
there are multiple small errors there. (Handling alignment for the
tuple header and not anyplace else is pretty pointless, for instance,
even if you had the correct alignment number for the machine.)
However ...

> 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).

That's big problem number one, and big problem number two is that it has
no good idea of the width of variable-width fields. (Should I even
mention TOAST?)

It's an interesting exercise in trying to estimate bloat without
groveling through the whole relation, but I seriously doubt you could
ever get numbers this way that are trustworthy enough to drive
maintenance decisions.

regards, tom lane