Re: bloating index, pg_restore

Thanks for the reply,

Well my question was not very precise, the postgresql version is 8.3 which is not supported, so I wanted to migrate to a newer version which is 9.1.

I have used pg_dump with -Fc option and I was monitoring the pg_restore activity.  Normally, the dump and restore takes from 30-40 minutes; but yesterday when the indexes are bloated  - I do not know how this could happen in one or two days, the database size increased from 700 MiB to 13 GiB -  the pg_restore on 9.1 takes around 6 hours.  Since pg_restore is using insert into (....) . How can bloated indexes affect the restore performance.

I have re-indexed one table  and the size dropped to again 700 MiB. So what could be the problem here?

Thanks

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc: salah jubeh <s_jubeh(at)yahoo(dot)com>; pgsql <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, March 28, 2013 5:08 AM
Subject: Re: [GENERAL] bloating index, pg_restore

Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> writes:
> On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:
>> I have a database which is bloated because of vacuum full, so you find
>> indexes bigger than the table itself.

> Table can not be bloated because of vacuum full, it removes bloat from
> the table and its indexes.

Um, well, that depends a lot on which PG version the OP is running
(which he didn't say).  The pre-9.0 implementation of VACUUM FULL
was notorious for creating index bloat, because it shuffled heap
entries around to compact heap space, but created an additional
index entry for each such heap-tuple motion.

            regards, tom lane