On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote: > Hi Claudio, how can I do that ? Can you explain me what is this option ? > > 2017-08-24 2:15 GMT+03:00 Claudio Freire <klaussfreire@xxxxxxxxx>: >> >> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky >> <mariel.cherkassky@xxxxxxxxx> wrote: >> > To summarize, I still have performance problems. My current situation : >> > >> > I'm trying to copy the data of many tables in the oracle database into >> > my >> > postgresql tables. I'm doing so by running insert into >> > local_postgresql_temp >> > select * from remote_oracle_table. The performance of this operation are >> > very slow and I tried to check the reason for that and mybe choose a >> > different alternative. >> > >> > 1)First method - Insert into local_postgresql_table select * from >> > remote_oracle_table this generated total disk write of 7 M/s and actual >> > disk >> > write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes. >> > >> > 2)second method - copy (select * from oracle_remote_table) to /tmp/dump >> > generates total disk write of 4 M/s and actuval disk write of 100 K/s. >> > The >> > copy utility suppose to be very fast but it seems very slow. >> >> Have you tried increasing the prefetch option in the remote table? >> >> If you left it in its default, latency could be hurting your ability >> to saturate the network. > > Please don't top-post. I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/ If you check the docs, you'll see this: https://github.com/laurenz/oracle_fdw#foreign-table-options So I'm guessing you could: ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 ); -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
