Re: slow building index and reference after Sybase to Pg

On 02/28/11 19:30, Andres Freund wrote:
> Hi,
>
> On Wednesday 23 February 2011 19:31:58 Gary Fu wrote:
>> 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).
> I think some additional information would be useful:
> * pg version
> * kernel version
> * distribution
>
>
> Andres
>
Here are the information :

modaps_lads=> show server_version;
server_version
----------------
9.0.1

9:58am 32 gfu(at)moddblads:/dump/gfu> uname -a
Linux moddblads 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:50:31 EDT 2010
x86_64 x86_64 x86_64 GNU/Linux

9:58am 34 gfu(at)moddblads:/dump/gfu> cat /proc/version
Linux version 2.6.18-194.17.1.el5 (mockbuild(at)builder10(dot)centos(dot)org) (gcc
version 4.1.2 20080704 (Red Hat 4.1.2-48)) #1 SMP Wed Sep 29 12:50:31
EDT 2010

Also, we have RAID10 with 600GB SAS drives 15000RPM

Another question here is that why building the reference will lock the
table for reading ? I mean why I cannot build two references at the
same time on the same reference table. Does the reference build just read ?

Thanks,
Gary