I'm trying to improve the performance of a postgresql databases that I migrated from oracle. I`m bringing alot of data from oracle tables with oracle_fdw extension into local postgresql tables and after that I'll create indexes on them.
In total, I'm bringing 56 GB of data from 10 tables. When I say "bringing" I mean that I run insert into local_postgresql_table
select * from remote_oracle_table. After that I'm building many multiple indexes on the local postgresql tables.
Lets look on specific table ps_rf. The size of the table is 23 GB and it took 1.5 hours only to bring the data and 14 min. for creation of every index (5 indexes for this table).
Another example is a table with a size of 3.3 GB, it took me 10 min. to bring its data.
The parameters that 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 = 1500MB RAM : 5G
I would like to improve both operations - bringing data from oracle and creation on indexes. Please advice how can I continue.