I have 3 queries that are running very slow. All of the are doing the same in general : select from a view in oracle (viw oracle_fdw extension). All the views look like that : select /parallel (table_Name,4)/ column1,column2,replace(column 3,' ',null),...,replace(...),column8 from table_name.
*I have more than 40 columns for every table so I just mentioned the format of the query.
the selects i run in postgresql look like that:
select * from oracle_table1_view;
table1 size is 10G with 100,000,000 records.
table2 size is 1.3G with 6,000,000 records.
table3 size is 8G with 75,000,000 records.
All this happens as part of a big function that gets data from the oracle db. Before Importing the data to the local tables in postgresql I delete the indexes and the constraints of the local tables and after the import of the data I create them.
Some info about my server :
I have 5 GB RAM on server and 4 are free.
I have 2 cpus
Some info about my Postgresql instance:
Currently I have only 1 db on the instance.
shared_buffers = 1000MB
effective_cache_size = 2GB
autovacuum = on
work_mem = 4MB
Moreover, I have a lot of selects * from foreign_Table. All of them taking some time but those 3 are taking too much. Please help improve performance of those 3 and if you can of all my selects.
Do the queries run fast when you execute them with sqlplus?
If not, you have to solve the problem on the Orace side.
To see the Oracle execution plan used by oracle_fdw, run
EXPLAIN (VERBOSE) SELECT * FROM oracle_table1_view;
Check if that matches the plan when you run from sqlplus. If not, try to spot the difference and figure out why.
If the plan looks the same, but the execution time is different, it could be that you select some LOB columns. Row prefetching does not work if such columns are involved, so there will be one round trip from PostgreSQL to Oracle for each selected row, which can make things really slow.