Inserting directly into the partition didnt help, the performance are just the same. I tried to increase the prefetch value to 1000(alter foreign table hist_oracle options (add prefetch '1000') but still no change - 15 minutes for one partition(6GB).
On the oracle side the plan is full scan on the partition (I'm copying the entire partition into a postgresql partition..)
- inserting directly to the right partition: perform as many inserts as pg partitions found in main_table_hist, like INSERT INTO 14/08/2018_value1 select * from remote_oracle_hist where day=to_date('14/08/2018','DD/MM/YYYY') and value='value1'
please check execution plan (in Oracle db) using EXPLAIN ANALYZE
- all those inserts should be executed in // (with 4 or 8 sql scripts)
- wal archiving should be disabled during hist data recovery only (not during day to day operations)
Sets the number of rows that will be fetched with a single round-trip between PostgreSQL and Oracle during a foreign table scan. This is implemented using Oracle row prefetching. The value must be between 0 and 10240, where a value of zero disables prefetching.
Higher values can speed up performance, but will use more memory on the PostgreSQL server.
