Re: slow building index and reference after Sybase to Pg

On 2/23/2011 12:31 PM, Gary Fu wrote:
> Hi,
>
> I'm testing on converting a big Sybase db to Pg. It took about 45 hours
> to convert all sybase tables (bcp) to Pg (copy) without index and
> reference. After that I built the index (one by one, sequentially) and
> it took about 25 hours and then I started to add the references (one by
> one), however, it has been more than 30 hours and still has no sign of
> finishing. I wonder, is there any suggestion that may speed up the index
> and reference building (on Pg).
>
> Thanks,
> Gary
>

In addition to Toms answer, disable fsync for a bit.

( http://www.postgresql.org/docs/9.0/static/non-durability.html )

Also, why one at a time? Are you IO bound? If you are IO bound then
ok, but otherwise try a few at a time. (I mean COPY, create index, and
add constraint)

While this was going on, did you view vmstat? Did you look at PG's log?

-Andy