Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)

--- 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 &
constraintseither side of the data restore process.  I'd not bothered with any of that for my tests, and assumed the
differencewas due to some sort of change in 9.0.