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

This is not so bad, you where at 10h for 200GB (20GB/h), And now at 24GB/h, it makes a 20% increase ;0) Could you tell us what are the résults with parallel exécutions (Before to switch to unload reload strategy) Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-perfor

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

The Postgres command of choice to load bulk data is COPY https://www.postgresql.org/docs/current/static/sql-copy.html is much faster than anything else. It’s likely that the slowest part could be Oracle exporting it’s data. Try to

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

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

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/') and value='value1' please check execution plan (in O

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

Hi, I'll try to answer all your question so that you will have more information about the situation : I have one main table that is called main_table_hist. The "main_table _hist" is partitioned by range (date column) and includes data that is considered as "history data" . I'm trying to copy the d

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

Did you try - runing multiple inserts in parallel, - Stop wal archiving, - Tune fetch sise ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

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

Hi, I'm using postgresql v10.4. I have a local partitioned table (by range - data, every day has its own table). I'm using the oracle_fdw extension to bring data from the oracle partitioned table into my local postgresql (insert into local select * from remote_oracle). Currently, I dont have any in