Index fillfactor changed in pg9?

Поиск
Список
Период
Сортировка
Искать

Index fillfactor changed in pg9?

От:
Glyn Astill <glynastill@yahoo.co.uk>
Дата:
Hey Guys,

Just testing some new hardware on 9.0.3 and have restored one of our dumps from 8.4.7.  What I'm seeing is although table sizes are the same, indexes are a lot bigger, approx 50%.

I've done a search and so far can't find anything, but have default fillfactors changed? Or is it something else?

Thanks
Glyn

Re: Index fillfactor changed in pg9?

От:
Kenneth Marshall <ktm@rice.edu>
Дата:
On Fri, Apr 01, 2011 at 01:46:03PM +0100, Glyn Astill wrote:
> Hey Guys,
> 
> Just testing some new hardware on 9.0.3 and have restored one of our dumps from 8.4.7.  What I'm seeing is although table sizes are the same, indexes are a lot bigger, approx 50%.
> 
> I've done a search and so far can't find anything, but have default fillfactors changed? Or is it something else?
> 
> Thanks
> Glyn
> 

Given absolutely zero information, are both platforms the same
number of bits? 32-versus 64-bit? The alignment needs for 64-bit
could result in needing more space. Another alternative is that
the order of data insertion caused a bigger index. What happens
if you run a REINDEX on both DB's to the index sizes?

Regards,
Ken

Re: Index fillfactor changed in pg9?

От:
Glyn Astill <glynastill@yahoo.co.uk>
Дата:
--- On Fri, 1/4/11, Kenneth Marshall  wrote:

> > 
> > Just testing some new hardware on 9.0.3 and have
> restored one of our dumps from 8.4.7.  What I'm seeing
> is although table sizes are the same, indexes are a lot
> bigger, approx 50%.
> > 
> > I've done a search and so far can't find anything, but
> have default fillfactors changed? Or is it something else?
> > 
> > Thanks
> > Glyn
> > 
> 
> Given absolutely zero information, are both platforms the
> same
> number of bits? 32-versus 64-bit? The alignment needs for
> 64-bit
> could result in needing more space. Another alternative is
> that
> the order of data insertion caused a bigger index. What
> happens
> if you run a REINDEX on both DB's to the index sizes?
> 
> Regards,
> Ken
> 

Sorry for the lack of info there. Both are 64 bit, both have ext3 filesystems set up the same, the 8.4 machine is on kernel 2.6.26 whereas the 9.0 machine is on 2.6.32.

REINDEX does indeed decreace the size.  I guess the question is why does pg_restore create them bloated? Could it be the parrallel (-j) option?

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

От:
Glyn Astill <glynastill@yahoo.co.uk>
Дата:
--- On Fri, 1/4/11, Glyn Astill  wrote:
> --- On Fri, 1/4/11, Kenneth Marshall wrote:
> What
> > happens
> > if you run a REINDEX on both DB's to the index sizes?
> > 
> > Regards,
> > Ken
> > 
> 
> Sorry for the lack of info there. Both are 64 bit, both
> have ext3 filesystems set up the same, the 8.4 machine is on
> kernel 2.6.26 whereas the 9.0 machine is on 2.6.32.
> 
> REINDEX does indeed decreace the size.  I guess the
> question is why does pg_restore create them bloated? Could
> it be the parrallel (-j) option?
> 

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, taken with pg_dump as follows

pg_dump -Fc mydatabase -U postgres -h localhost -a --disable-triggers -f data-dump.gz

That appears to restore with COPY, using the following 

pg_restore -U postgres --disable-triggers -c -d mydatabase data-dump.gz

I'm a bit perplexed by this

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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Glyn Astill  writes:
> 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?

			regards, tom lane

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

От:
Glyn Astill <glynastill@yahoo.co.uk>
Дата:
--- On Mon, 4/4/11, Tom Lane  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.

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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Glyn Astill  writes:
> --- On Mon, 4/4/11, Tom Lane  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.

			regards, tom lane

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

От:
Glyn Astill <glynastill@yahoo.co.uk>
Дата:


--- On Mon, 4/4/11, Tom Lane  wrote:
> > --- On Mon, 4/4/11, Tom Lane 
> 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.

FAQ