From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: surprised to find bloat in insert-only table
Date: Fri, 03 Jun 2011 13:39:12 -0400
Message-ID: <1739.1307122752@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mark@summersault.com
Subject: Re: surprised to find bloat in insert-only table
In-Reply-To: <1739.1307122752@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox