Re: performance problem on big tables

[Michael DNA <michaeldba@xxxxxxxxxxx>]

If your procedures to get the data is part is a query predicate, then you can still use ora2pg

Sent from my iPhone

On Aug 21, 2017, at 10:35 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

 

From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Mariel Cherkassky
Sent: Monday, August 21, 2017 10:20 AM
To: MichaelDBA <MichaelDBA@xxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: performance problem on big tables

 

I had a system that consist from many objects(procedures,functions..) on an oracle database. We decided to integrate that system to postgresql. That system coppied alot of big tables from a different read only oracle database and preformed on it alot of queries to produce reports. The part of getting the data is part of some procedures, I cant change it so freely. I'm searching a way to improve the perfomance of the database because I'm sure that I didnt conifgure something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it takes alot of time and I see that the server is cacheing all my ram memory.

 

 

Probably your joins are done on Postgres side.

 

m.b. instead of Postgres pulling data from Oracle, you should try pushing data from Oracle to Postgres using Oracle’s Heterogeneous Services and Postgres ODBC driver. In this case you do your joins and filtering on Oracles side and just push the result set to Postgres.

That’s how I did migration from Oracle to Postgres.

 

Regards,

Igor Neyman