Subject: Re: increase insert into local table from remote oracle table
 preformance
To: pgsql-admin@lists.postgresql.org
References: 
 <CA+t6e1nxh1VVMjD2NJZV_Mcb7UyF8-F3y=hxjamuMFA1bAk2Jg@mail.gmail.com>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <2e6e6255-d33f-dfc5-0470-b3adff075525@catalyst.net.nz>
Date: Tue, 14 Aug 2018 17:21:23 +1200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1nxh1VVMjD2NJZV_Mcb7UyF8-F3y=hxjamuMFA1bAk2Jg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

Hi, we probably need more inforation to offer anything useful here, e.g:

- The network bandwidth between the 2 hosts

- the number of partitions on the Postgres end (i.e how many days in 
your case)

- single or batched INSERT

The lack of indexes is probably not going to effect INSERT performance 
that much, but the number of partition tables has a huge impact, so we 
need to know this stuff!

Cheers

Mark


On 14/08/18 08:34, 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 ?
>
>
> Thanks , Mariel.