From: Glyn Astill <glynastill@yahoo.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
Date: Mon, 4 Apr 2011 20:35:17 +0100 (BST)
Message-ID: <33565.77565.qm@web26001.mail.ukl.yahoo.com> (raw)
In-Reply-To: <1424.1301934766@sss.pgh.pa.us>
--- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > --- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
> >>> So it appears now that if I restore the
> database using
> >>> pg_restore, I end up with bloated indexes,
> which are fixed
> >>> with a vacuum full.
> >>>
> >>> The dump is a data only dump with the -Fc
> flag,
>
> >> Data only dump? Then what is the state of the
> >> database you're restoring it into?
>
> > It's a newly created database from a schema only
> dump.
>
> So the difference is that you have initially-empty indexes
> that are
> filled incrementally, whereas an ordinary dump-and-restore
> would be
> creating fresh indexes. Incremental filling of a
> btree is usually said
> to result in about 66% fillfactor on average, 50%
> worst-case; whereas by
> default I think we build fresh indexes at 90%
> fillfactor. You didn't
> say how much "bloat" you were seeing, but if it's less than
> 2X I think
> this is just expected. Unless the data is pretty
> static, it's useless
> to hope that the fill factor will stay as high as 90%
> anyway.
>
Thanks Tom.
Yeah that's exactly what I'm seeing, my indexes would be about 66% fillfactor.
I realize now, the reason I don't see this on our production machines is because I drop and recreate the indexes & constraints either side of the data restore process. I'd not bothered with any of that for my tests, and assumed the difference was due to some sort of change in 9.0.
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: glynastill@yahoo.co.uk, tgl@sss.pgh.pa.us, ktm@rice.edu
Subject: Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
In-Reply-To: <33565.77565.qm@web26001.mail.ukl.yahoo.com>
* 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