Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

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 <MKumar(at)peabodyenergy(dot)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
>
>
>
>
>