From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: increase insert into local table from remote oracle table preformance
Date: Tue, 14 Aug 2018 09:12:40 +0200
Message-ID: <1534230760.2379.6.camel@cybertec.at> (raw)
In-Reply-To: <CA+t6e1nxh1VVMjD2NJZV_Mcb7UyF8-F3y=hxjamuMFA1bAk2Jg@mail.gmail.com>
References: <CA+t6e1nxh1VVMjD2NJZV_Mcb7UyF8-F3y=hxjamuMFA1bAk2Jg@mail.gmail.com>
Mariel Cherkassky wrote:
> 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 indexes on the postgresql`s table.
> It takes me 10 hours to copy 200G over the network and it is very slow.
> Any recommandations what can I change or improve ?
Hard to say anything with so little data.
You could try a bigger value for the "prefetch" option.
One known reason for slow performance is if there are LOBs in the Oracle table.
You could parallelize processing by running several such INSERTs in
parallel, perhaps one per partition, and inserting directly into
the partitions.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: increase insert into local table from remote oracle table preformance
In-Reply-To: <1534230760.2379.6.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox