DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Z2UzEBsZm5jthP4ylR3kA59bKkBfdoJAqOUhlAt7lw28k5QRdf/xuyh0wnhLVDu1QgApuMZ7wu7y2Iio8wr+KsFbB+vVGwMvzyeumSSrZuAMg3lxuZw10uGiFtdONKyXJy7OhJk1ntgGAPqgrsbNyNMtU9eg0vKJtDjpXg/vROA=;
Message-ID: <33565.77565.qm@web26001.mail.ukl.yahoo.com>
Date: Mon, 4 Apr 2011 20:35:17 +0100 (BST)
From: Glyn Astill <glynastill@yahoo.co.uk>
Subject: Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed
	in pg9?)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kenneth Marshall <ktm@rice.edu>, pgsql-admin@postgresql.org
In-Reply-To: <1424.1301934766@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0A=0A--- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:=0A> > --- On =
Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.us>=0A> wrote:=0A> >>> So it appears =
now that if I restore the=0A> database using=0A> >>> pg_restore, I end up w=
ith bloated indexes,=0A> which are fixed=0A> >>> with a vacuum full.=0A> >>=
> =0A> >>> The dump is a data only dump with the -Fc=0A> flag,=0A> =0A> >> =
Data only dump?=A0 Then what is the state of the=0A> >> database you're res=
toring it into?=0A> =0A> > It's a newly created database from a schema only=
=0A> dump.=0A> =0A> So the difference is that you have initially-empty inde=
xes=0A> that are=0A> filled incrementally, whereas an ordinary dump-and-res=
tore=0A> would be=0A> creating fresh indexes.=A0 Incremental filling of a=
=0A> btree is usually said=0A> to result in about 66% fillfactor on average=
, 50%=0A> worst-case; whereas by=0A> default I think we build fresh indexes=
 at 90%=0A> fillfactor.=A0 You didn't=0A> say how much "bloat" you were see=
ing, but if it's less than=0A> 2X I think=0A> this is just expected.=A0 Unl=
ess the data is pretty=0A> static, it's useless=0A> to hope that the fill f=
actor will stay as high as 90%=0A> anyway.=0A> =0A=0AThanks Tom.=0A=0AYeah =
that's exactly what I'm seeing, my indexes would be about 66% fillfactor.=
=0A=0AI 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 th=
e data restore process.  I'd not bothered with any of that for my tests, an=
d assumed the difference was due to some sort of change in 9.0.=0A