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

--- On Fri, 1/4/11, Glyn Astill <glynastill(at)yahoo(dot)co(dot)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 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