El jue, 08-11-2007 a las 13:01 -0500, Tom Lane escribió:
> "Krasimir Hristozov \(InterMedia Ltd\)" <[EMAIL PROTECTED]> writes:
> > We need to import data from a relatively large MySQL database into an
> > existing PostgreSQL database, using a PHP5 script that SELECTs from MySQL
> > and INSERTs in
Try to one of these:
a) don't use INSERT statements, use a COPY instead
b) from time to time run ANALYZE on the "public" table (say 1000
inserts, then one analyze)
c) create the table without constraints (primary / foreign keys in this
case), import all the data, and then create the const
Thanks to all who responded. Using COPY instead of INSERT really solved the
problem - the whole process took about 1h 20min on an indexed table, with
constraints (which is close to our initial expectations). We're performing some
additional tests now. I'll post some more observations when finish
Hello Krasimir,
You got a lot of good advices above and I would like to add another one:
d) Make sure of your PHP code is not recursive. As you said the memory is
stable so I think your method is iterative.
A recursive method certainly will increase a little time for each insert
using more memory
[EMAIL PROTECTED] ("=?UTF-8?B?VG9tw6HFoSBWb25kcmE=?=") writes:
> Try to one of these:
>
> a) don't use INSERT statements, use a COPY instead
>
> b) from time to time run ANALYZE on the "public" table (say 1000
>inserts, then one analyze)
>
> c) create the table without constraints (primary / fo
In response to "Krasimir Hristozov \(InterMedia Ltd\)" <[EMAIL PROTECTED]>:
> We need to import data from a relatively large MySQL database into an
> existing PostgreSQL database, using a PHP5 script that SELECTs from MySQL
> and INSERTs in PostgreSQL. A part of the import involves moving about
>
"Krasimir Hristozov \(InterMedia Ltd\)" <[EMAIL PROTECTED]> writes:
> We need to import data from a relatively large MySQL database into an
> existing PostgreSQL database, using a PHP5 script that SELECTs from MySQL
> and INSERTs in PostgreSQL. A part of the import involves moving about
> 1,300,000
Try to one of these:
a) don't use INSERT statements, use a COPY instead
b) from time to time run ANALYZE on the "public" table (say 1000
inserts, then one analyze)
c) create the table without constraints (primary / foreign keys in this
case), import all the data, and then create the const
We need to import data from a relatively large MySQL database into an
existing PostgreSQL database, using a PHP5 script that SELECTs from MySQL
and INSERTs in PostgreSQL. A part of the import involves moving about
1,300,000 records from one MySQL table to one of our PostgreSQL tables. The
problem