MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com>
References: 
 <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Mon, 14 Aug 2017 09:39:06 -0700
Message-ID: 
 <CAMkU=1zsJd4eKhDa0MLzDxUzPnJ=0YaPK01YMx3-izm=gwiJfA@mail.gmail.com>
Subject: Re: performance problem on big tables
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a114368b62314a60556b94d39"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114368b62314a60556b94d39
Content-Type: text/plain; charset="UTF-8"

On Mon, Aug 14, 2017 at 6:24 AM, Mariel Cherkassky <
mariel.cherkassky@gmail.com> wrote:

> I have performance issues with two big tables. Those tables are located on
> an oracle remote database. I'm running the quert : insert into
> local_postgresql_table select * from oracle_remote_table.
>
> The first table has 45M records and its size is 23G. The import of the
> data from the oracle remote database is taking 1 hour and 38 minutes.
>
To investigate this, I'd decouple the two steps and see how long each one
takes:

\copy (select * from oracle_remote_table) to /tmp/tmp with binary
\copy local_postresql_table from /tmp/tmp with binary

Cheers,

Jeff

--001a114368b62314a60556b94d39
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote">On M=
on, Aug 14, 2017 at 6:24 AM, Mariel Cherkassky <span dir=3D"ltr">&lt;<a hre=
f=3D"mailto:mariel.cherkassky@gmail.com" target=3D"_blank">mariel.cherkassk=
y@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div di=
r=3D"rtl"><p style=3D"direction:ltr;margin:0px 0px 1em;padding:0px;border:0=
px;font-variant-numeric:inherit;font-stretch:inherit;font-size:15px;line-he=
ight:inherit;font-family:Arial,&quot;Helvetica Neue&quot;,Helvetica,sans-se=
rif;vertical-align:baseline;clear:both;color:rgb(36,39,41)">I have performa=
nce issues with two big tables. Those tables are located on an oracle remot=
e database. I&#39;m running the quert :=C2=A0<code style=3D"margin:0px;padd=
ing:1px 5px;border:0px;font-style:inherit;font-variant:inherit;font-weight:=
inherit;font-stretch:inherit;font-size:13px;line-height:inherit;font-family=
:Consolas,Menlo,Monaco,&quot;Lucida Console&quot;,&quot;Liberation Mono&quo=
t;,&quot;DejaVu Sans Mono&quot;,&quot;Bitstream Vera Sans Mono&quot;,&quot;=
Courier New&quot;,monospace,sans-serif;vertical-align:baseline;background-c=
olor:rgb(239,240,241);white-space:pre-wrap">insert into local_postgresql_ta=
ble select * from oracle_remote_table.</code></p><p style=3D"direction:ltr;=
margin:0px 0px 1em;padding:0px;border:0px;font-variant-numeric:inherit;font=
-stretch:inherit;font-size:15px;line-height:inherit;font-family:Arial,&quot=
;Helvetica Neue&quot;,Helvetica,sans-serif;vertical-align:baseline;clear:bo=
th;color:rgb(36,39,41)">The first table has 45M records and its size is 23G=
. The import of the data from the oracle remote database is taking 1 hour a=
nd 38 minutes.</p></div></blockquote><div>To investigate this, I&#39;d deco=
uple the two steps and see how long each one takes:</div><div><br></div><di=
v>\copy (select * from oracle_remote_table) to /tmp/tmp with binary</div><d=
iv>\copy local_postresql_table from /tmp/tmp with binary</div><div><br></di=
v><div>Cheers,</div><div><br></div><div>Jeff</div></div></div></div>

--001a114368b62314a60556b94d39--