Thread: Index fillfactor changed in pg9?
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 tablesizes 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
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 tablesizes 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
--- On Fri, 1/4/11, Kenneth Marshall <ktm@rice.edu> 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 kernel2.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 theparrallel (-j) option?
--- On Fri, 1/4/11, Glyn Astill <glynastill@yahoo.co.uk> 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 avacuum 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
Glyn Astill <glynastill@yahoo.co.uk> writes:
> So it appears now that if I restore the database using pg_restore, I end up with bloated indexes, which are fixed
witha 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
--- 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.
Glyn Astill <glynastill@yahoo.co.uk> writes:
> --- 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.
regards, tom lane
--- 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.