Re: Fwd: increase insert into local table from remote oracle tablepreformance - Mailing list pgsql-performance

main ideas are:

- 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)

- for prefetch see

https://github.com/laurenz/oracle_fdw

prefetch (optional, defaults to "200")

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.


Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html