Re: performance problem on big tables

Total RAM on your host is 5GB, really? Before touching anything else,
increase your RAM. That will be your big performance boost right
there. Then, you can "up" your effective_cache_size and
maintenance_work_mem.

Regards,
Michael Vitale

> Mariel Cherkassky <mailto:mariel(dot)cherkassky(at)gmail(dot)com>
> Monday, August 14, 2017 9:24 AM
>
> 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 ?
>