From: Tom Lane <tgl@sss.pgh.pa.us>
To: Samuel Stearns <SStearns@internode.com.au>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Resolving Index Bloat
Date: Mon, 19 Nov 2012 19:20:11 -0500
Message-ID: <16671.1353370811@sss.pgh.pa.us> (raw)
In-Reply-To: <CBAC86BE623FDB4E8B6225471691724291E1BC19@EXCHMBX-ADL6-01.staff.internode.com.au>
References: <CBAC86BE623FDB4E8B6225471691724291E1BC19@EXCHMBX-ADL6-01.staff.internode.com.au>
Samuel Stearns <SStearns@internode.com.au> writes:
> We have a problem with index bloat on a couple of our tables even though we have applied more aggressive autovac/analyze settings in the schema:
Hard to tell much about this without knowing the baseline condition or
what's happened since the baseline. It looks like your tables have
grown circa 2X (eg billingitemrating), but is that due to new data or
heavy update activity?
If the baseline condition is freshly-built-or-REINDEXed indexes, a fair
amount of "bloat" is to be expected. The traditional rule of thumb
about btree indexes is that the steady-state load factor is about
two-thirds full. By default, PG builds indexes tightly packed --- so
just allowing the index to reach steady state will incur 50% "bloat"
on average. It's usually counterproductive to try to maintain a fill
factor better than that, unless the table receives only minimal
insert/update traffic. (Indeed, usually the better policy for a
heavy-update table is to create the indexes with 66% fillfactor to begin
with.)
Your indexes on billingitemrating seem to have expanded a bit more than
what would be expected from the combination of these factors, but I'm
not sure they're enormously out of line. You could delve a bit deeper
by using contrib/pgstattuple to measure the actual dead space in both
the tables and the indexes. Also, it'd be useful to know the data types
of the columns being indexed.
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, SStearns@internode.com.au
Subject: Re: Resolving Index Bloat
In-Reply-To: <16671.1353370811@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