public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mariel Cherkassky <[email protected]>
To: [email protected]
Subject: performance problem on big tables
Date: Mon, 14 Aug 2017 16:24:24 +0300
Message-ID: <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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. After that
I create 13 regular indexes on the table and it takes 10 minutes per table
->2 hours and 10 minutes in total.

The second table has 29M records and its size is 26G. The import of the
data from the oracle remote database is taking 2 hours and 30 minutes. The
creation of the indexes takes 1 hours and 30 minutes (some are indexes on
one column and the creation takes 5 min and some are indexes on multiples
column and it takes 11 min.

Those operation are very problematic for me and I'm searching for a
solution to improve the performance. The parameters I assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 2000MB
RAM : 5G
CPU CORES : 8

*-I tried running select count(*) from table in oracle and in postgresql
the running time is almost equal.*

*-Before importing the data I drop the indexes and the constraints.*

*-I tried to copy a 23G file from the oracle server to the postgresql
server and it took me 12 minutes.*

Please advice how can I continue ? How can I improve something in this
operation ?

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: performance problem on big tables
  In-Reply-To: <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox