Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (1.0)
Subject: Re: increase insert into local table from remote oracle table
 preformance
From: Andrew Kerber <andrew.kerber@gmail.com>
In-Reply-To: <1534230760.2379.6.camel@cybertec.at>
Date: Wed, 15 Aug 2018 06:42:37 -0500
Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>,
 pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <305B48BC-C989-4CE5-AFCE-64E985F2D590@gmail.com>
References: 
 <CA+t6e1nxh1VVMjD2NJZV_Mcb7UyF8-F3y=hxjamuMFA1bAk2Jg@mail.gmail.com>
 <1534230760.2379.6.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Precedence: bulk

You need to track down your limited resource.  IO, CPU, or network.  I would=
 say it=E2=80=99s unlikely to be CPU, but you never know.  Look at the activ=
ities on each server and see what resource is maxed out. My guess is IO, but=
 you could also have your network choked. =20

Sent from my iPad

> On Aug 14, 2018, at 02:12, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>=20
> Mariel Cherkassky wrote:
>> Hi,
>> I'm using postgresql v10.4. I have a local partitioned table (by range - d=
ata, every day has its own table).
>> I'm using the oracle_fdw extension to bring data from the oracle partitio=
ned table into my local postgresql
>> (insert into local select * from remote_oracle). Currently, I dont have a=
ny 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 ?
>=20
> Hard to say anything with so little data.
>=20
> You could try a bigger value for the "prefetch" option.
>=20
> One known reason for slow performance is if there are LOBs in the Oracle t=
able.
>=20
> You could parallelize processing by running several such INSERTs in
> parallel, perhaps one per partition, and inserting directly into
> the partitions.
>=20
> Yours,
> Laurenz Albe
> --=20
> Cybertec | https://www.cybertec-postgresql.com
>=20