You might be comparing apples and pears.. Your Oracle is running on prem while Postgres is running on Azure. Azure does not really have disks; it seems to have just a bunch of old people writing the data on paper - I/O on Azure is ridiculously slow. What disks/hardware does the on-prem Oracle have?
On Tue, Apr 12, 2022 at 4:16 PM Kumar, Mukesh <mku...@peabodyenergy.com> wrote: > Hi Team, > > > > Greetings !! > > > > We have recently done the migration from Oracle Database Version 12C to > Azure PostgreSQL PaaS instance version 11.4 and most of the application > functionality testing has been over and tested successfully > > > > However, there is 1 process at application level which is taking approx. > 10 mins in PostgreSQL and in oracle it is taking only 3 mins. > > > > After investigating further we identified that process which is executed > from application end contains 500 to 600 no of short SQL queries into the > database. We tried to run the few queries individually on database and they > are taking less than sec in Postgres Database to execute, and we noticed > that in Oracle taking half of the time as is taking in PostgreSQL. for ex . > in oracle same select statement is taking 300 millisecond and in PostgreSQL > it is taking approx. 600 millisecond which over increases the execution of > the process. > > > > Oracle Database are hosted on ON- Prem DC with dedicated application > server on OnPrem and same for PostgreSQL. > > We are using below specifications for PostgreSQL > > PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage on > general purpose tier ) = 8 Core and 40 Gb of Memory > > PostgreSQL version - 11.4 > > > > We have tried running maintenance Jobs like vaccum, analyze, creating > indexes, increasing compute but no sucess > > > > > > I am happy to share my server parameter for PostgreSQL for more > information. > > > > Please let us know if this is expected behavior in PostgreSQL or is there > any way i can decrease the time for the SQL queries and make it a > comparison with Oracle > > > > Regards, > > Mukesh Kumar > > > > >
