I'm trying to create some regular indexes on a big table (26G), but it takes a lot of time - more than 2 hours. Every index is taking about 11 minutes.
Maybe I'm wrong and I should concentrate on improving the time it takes me to load the data into postgres from oracle (oracle_fdw). I preform a lot of inserts into local_postgresql_table select * from remote_oracle_table (about 200G), which also takes a lot of time.
If there is a way to change one of the parameters to improve the performance, I would be happy to hear how. Running this query on 26G takes two hours.
Is there a way to improve this operation? Is there a way to improve this operation by improving the hardware (I didn't see that the server is overloaded)?
The parameters that I configured:
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