To: Mark Stosberg <mark@summersault.com>
cc: pgsql-admin@postgresql.org
Subject: Re: surprised to find bloat in insert-only table 
In-reply-to: <isb453$uhp$1@dough.gmane.org> 
References: <isathr$kig$1@dough.gmane.org> <28332.1307116922@sss.pgh.pa.us>
	<isb453$uhp$1@dough.gmane.org>
Comments: In-reply-to Mark Stosberg <mark@summersault.com>
	message dated "Fri, 03 Jun 2011 13:03:31 -0400"
Date: Fri, 03 Jun 2011 13:39:12 -0400
Message-ID: <1739.1307122752@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Stosberg <mark@summersault.com> writes:
> Compared to 1669 MB reported as table bloat in the 'bloat' view. So,
> the bloat is about 40% of the total size.
> For an index, it's 410 MB of bloat, vs 1669 MB for an index size.

Hm ... 40% unused space wouldn't be surprising at all for an index.
The traditional rule of thumb for a b-tree index is that the steady
state fill factor is about 2/3rds.  You can do better for an index that
is loaded in increasing order (eg, an index on a serial or timestamp
column typically has a higher fill factor) but indexes on columns that
are more random are not going to see that.

> One thing that looks suspicious is that the exact same number of bytes
> is being report for the table as well as each index.

Um.  In that case I'd say your view is flat out wrong...

			regards, tom lane