title: Slow cross-loading from oracle (oracle-fdw) into PostgreSQL
body: <p>I created multiple posts in the forum about the performance problem that I have but now after i made some tests and gathered all the info that is needed I'm creating this post.</p>
  
  <p>I have performance issues with two big tables. Those tables are located on an oracle remote database. I'm running the quert : 
  <code>insert into local_postgresql_table select * from oracle_remote_table.</code></p>
  
  <p>The first table has 45M records and its size is 23G. The import of the data from the oracle remote database is taking 1 hour and 38 minutes. After that I create 13 regular indexes on the table and it takes 10 minutes per table ->2 hours and 10 minutes in total.</p>
  
  <p>The second table has 29M records and its size is 26G. The import of the data from the oracle remote database is taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours and 30 minutes (some are indexes on one column and the creation takes 5 min and some are indexes on multiples column and it takes 11 min.</p>
  
  <p>Those operation are very problematic for me and I'm searching for a solution to improve the performance. The parameters I assigned : </p>
  
  <pre><code>min_parallel_relation_size = 200MB
  max_parallel_workers_per_gather = 5 
  max_worker_processes = 8 
  effective_cache_size = 2500MB
  work_mem = 16MB
  maintenance_work_mem = 1500MB
  shared_buffers = 2000MB
  RAM : 5G
  CPU CORES : 8
  </code></pre>
  
  <p><strong>-I tried running select count(*) from table in oracle and in postgresql the running time is almost equal.</strong> </p>
  
  <p><strong>-Before importing the data I drop the indexes and the constraints.</strong></p>
  
  <p><strong>-I tried to copy a 23G file from the oracle server to the postgresql server and it took me 12 minutes.</strong></p>
  
  <p>Please advice how can I continue ? How can I improve something in this operation ?</p>