Re: Fwd: increase insert into local table from remote oracle table preformance

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

2018-08-15 1:28 GMT+03:00 legrand legrand <legrand_legrand(at)hotmail(dot)com>:

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