Skip site navigation (1) Skip section navigation (2)

Re: Bloated Table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>, Alexander Schöcke <asc(at)turtle-entertainment(dot)de>, pgsql-general(at)postgresql(dot)org
Subject: Re: Bloated Table
Date: 2009-05-27 19:28:11
Message-ID: 15351.1243452491@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Alan McKayDate: 2009-05-27 19:33:55
Subject: Re: [PERFORM] Postgres Clustering
Previous:From: miller_2555Date: 2009-05-27 19:19:41
Subject: Multidimensional array definition in composite type appears parsed as string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group