SQL performance issue after migration from Oracle to Aurora postgres

2022-04-14 Thread Goti 
Hi All,

We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in Oracle
takes less than a millisecond however the same query in aurora is taking
more than a second. We have a larger number of executions for the SQL which
is causing an overall latency for the application. I am new to postgres and
trying to get some ideas around how better we can optimize. I have the plan
details for the SQL as below. Can someone shed some light on possible ways
that can make this query to meet its original execution time?

https://explain.depesz.com/s/jlVc#html

Thanks,

Goti

Query Tunning related to function

2022-04-14 Thread Kumar, Mukesh 
Hi Team,

We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec 
to run the query.

Query - 1

Select * from
  (
  Select payment_sid_c,
  lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
  from
  lms_app.lms_payment_check_request
  group by payment_sid_c) a
  where  paymentstatus in ('PAID', 'MANUALLYPAID')


The explain plan and other details are placed at below link for more 
information. We have checked the indexes on column but in the explain plan it 
is showing as Seq Scan which we have to find out.


https://explain.depesz.com/s/Jsiw#stats


This query is using a function translate_payment_status on column payment_sid_c 
whose script is attached in this mail

Could please anyone help or suggest how to improve the query performance.

Thanks and Regards,
Mukesh Kumar



function.sql
Description: function.sql

Re: Query Tunning related to function

2022-04-14 Thread Ranier Vilela 
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
[email protected]> escreveu:

> Hi Team,
>
>
>
> We are running the below query in PostgreSQL and its taking approx. 8 to 9
> sec to run the query.
>
>
>
> Query – 1
>
>
>
> Select * from
>
>   (
>
>   Select payment_sid_c,
>
>   lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
>
>   from
>
>   lms_app.lms_payment_check_request
>
>   group by payment_sid_c) a
>
>   where  paymentstatus in ('PAID', 'MANUALLYPAID')
>
>
>
>
>
> The explain plan and other details are placed at below link for more
> information. We have checked the indexes on column but in the explain plan
> it is showing as Seq Scan which we have to find out.
>
>
>
>
>
> *https://explain.depesz.com/s/Jsiw#stats
> *
>
>
>
>
>
> This query is using a function translate_payment_status on column
> payment_sid_c whose script is attached in this mail
>
>
>
> Could please anyone help or suggest how to improve the query performance.
>
You can try create a partial index that help this filter:
Filter: ((lms_app.translate_payment_status(payment_sid_c))::text = ANY
('{PAID,MANUALLYPAID}'::text[]))

See at:
https://www.postgresql.org/docs/current/indexes-partial.html

regards,
Ranier Vilela

Re: SQL performance issue after migration from Oracle to Aurora postgres

2022-04-14 Thread Andrew Dunstan 


On 2022-04-14 Th 05:35, Goti wrote:
> Hi All,
>
> We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in
> Oracle takes less than a millisecond however the same query in aurora
> is taking more than a second. We have a larger number of executions
> for the SQL which is causing an overall latency for the application. I
> am new to postgres and trying to get some ideas around how better we
> can optimize. I have the plan details for the SQL as below. Can
> someone shed some light on possible ways that can make this query to
> meet its original execution time?
>
> https://explain.depesz.com/s/jlVc#html
>

Without knowing much about your data I would suggest trying to rewrite
the query to get rid of the correlated subselect, using a join instead.
I note the use of both implicit and explicit joins in your FROM clause,
which is something I always advise against, as it hurts clarity, but
that's a matter of style rather than performance.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

RE: Query Tunning related to function

2022-04-14 Thread Kumar, Mukesh 
Hi Rainer ,

We tried to create the partial ‘index on table but it did not help, and it is 
taking approx. 7 sec now.

Also we tried to force the query to use the index by enabling the parameter at 
session level

set enable_seqscan=false;

and it is still taking the time below is the explain plan for the same

https://explain.depesz.com/s/YRWIW#stats

Also we running the query which is actually used in application and above query 
is used in below query. Below is the explain plan for same.


https://explain.depesz.com/s/wktl#stats

Please assist


Thanks and Regards,
Mukesh Kuma

From: Ranier Vilela 
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh 
Cc: [email protected]; MUKESH KUMAR 
Subject: Re: Query Tunning related to function

Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh 
mailto:[email protected]>> escreveu:
Hi Team,

We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec 
to run the query.

Query – 1

Select * from
  (
  Select payment_sid_c,
  lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
  from
  lms_app.lms_payment_check_request
  group by payment_sid_c) a
  where  paymentstatus in ('PAID', 'MANUALLYPAID')


The explain plan and other details are placed at below link for more 
information. We have checked the indexes on column but in the explain plan it 
is showing as Seq Scan which we have to find out.


https://explain.depesz.com/s/Jsiw#stats


This query is using a function translate_payment_status on column payment_sid_c 
whose script is attached in this mail

Could please anyone help or suggest how to improve the query performance.
You can try create a partial index that help this filter:
Filter: ((lms_app.translate_payment_status(payment_sid_c))::text = ANY 
('{PAID,MANUALLYPAID}'::text[]))

See at:
https://www.postgresql.org/docs/current/indexes-partial.html

regards,
Ranier Vilela

RE: Query Tunning related to function

2022-04-14 Thread Michel SALAIS 
Hi,

 

This part of the function is odd and must be dropped:

 IF (ret_status = payment_rec)

 THEN

  ret_status := payment_rec;

 

I didn’t look really the function code and stopped on the view referenced by 
the cursor.

The view (we know it just by its name) used in the function is a black box for 
us. Perhaps it is important to begin optimization there!

If values 'PAID' and 'MANUALLYPAID' are an important percentage of table rows 
forcing index use is not a good thing especially when it is done with a 
non-optimized function.

 

If rows with values 'PAID' and 'MANUALLYPAID'  constitute a little percentage 
of the table, then the partial index plus rewriting the query would be much 
more efficient

Select

  payment_sid_c,

 lms_app.translate_payment_status(payment_sid_c) as paymentstatus

from

  lms_app.lms_payment_check_request

where

 lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')

group by

  payment_sid_c

 

If not, you can gain some performance if you rewrite your query to be like this:

 

Select

  payment_sid_c,

 lms_app.translate_payment_status(payment_sid_c) as paymentstatus

from

  lms_app.lms_payment_check_request

group by

  payment_sid_c

having

 lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')

 

And you can also try to write the query like this:

 

Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)

From

(

  Select

payment_sid_c

  from

   lms_app.lms_payment_check_request

  group by

payment_sid_c

  having

lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')

) t

 

Regards

 

Michel SALAIS

De : Kumar, Mukesh  
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela 
Cc : [email protected]; MUKESH KUMAR 
Objet : RE: Query Tunning related to function

 

Hi Rainer , 

 

We tried to create the partial ‘index on table but it did not help, and it is 
taking approx. 7 sec now.

 

Also we tried to force the query to use the index by enabling the parameter at 
session level

 

set enable_seqscan=false;

 

and it is still taking the time below is the explain plan for the same 

 

https://explain.depesz.com/s/YRWIW#stats

 

Also we running the query which is actually used in application and above query 
is used in below query. Below is the explain plan for same.

 

 

https://explain.depesz.com/s/wktl#stats

 

Please assist 

 

 

Thanks and Regards, 

Mukesh Kuma 

 

From: Ranier Vilela mailto:[email protected]> > 
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh mailto:[email protected]> >
Cc: [email protected]  
; MUKESH KUMAR mailto:[email protected]> >
Subject: Re: Query Tunning related to function

 

Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh mailto:[email protected]> > escreveu:

Hi Team, 

 

We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec 
to run the query.

 

Query – 1 

 

Select * from 

  (

  Select payment_sid_c,

  lms_app.translate_payment_status(payment_sid_c) AS paymentstatus 

  from 

  lms_app.lms_payment_check_request

  group by payment_sid_c) a  

  where  paymentstatus in ('PAID', 'MANUALLYPAID')

 

 

The explain plan and other details are placed at below link for more 
information. We have checked the indexes on column but in the explain plan it 
is showing as Seq Scan which we have to find out.

 

 

https://explain.depesz.com/s/Jsiw#stats 

 

 

 

This query is using a function translate_payment_status on column payment_sid_c 
whose script is attached in this mail

 

Could please anyone help or suggest how to improve the query performance.

You can try create a partial index that help this filter:

Filter: ((lms_app.translate_payment_status(payment_sid_c))::text = ANY 
('{PAID,MANUALLYPAID}'::text[])) 

 

See at:

https://www.postgresql.org/docs/current/indexes-partial.html 

 

 

regards,

Ranier Vilela

RE: Query Tunning related to function

2022-04-14 Thread Kumar, Mukesh 
Hi Michael ,


We tried dropping the below values from the function, but it did not help.

Also, the values PAID and MANUALLY PAID constitutes about 60 % of the values in 
table ,  and infact we tried creating the partial index and it did not help.

The Strange thing is that we are trying to run this in oracle as we have done 
the migration recently and it is running in less than second with same indexes 
and other database objects . I can understand that comparing to oracle is 
stupidity, but this is only thing where we can compare.

Below is the query we are running on oracle and comparing in postgres

Below is the query and plan for same

https://explain.depesz.com/s/wktl#stats

Any help would be appreciated.



Thanks and Regards,
Mukesh Kumar

From: Michel SALAIS 
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh ; 'Ranier Vilela' 

Cc: [email protected]; 'MUKESH KUMAR' 
Subject: RE: Query Tunning related to function

Hi,

This part of the function is odd and must be dropped:
 IF (ret_status = payment_rec)
 THEN
  ret_status := payment_rec;

I didn’t look really the function code and stopped on the view referenced by 
the cursor.
The view (we know it just by its name) used in the function is a black box for 
us. Perhaps it is important to begin optimization there!
If values 'PAID' and 'MANUALLYPAID' are an important percentage of table rows 
forcing index use is not a good thing especially when it is done with a 
non-optimized function.

If rows with values 'PAID' and 'MANUALLYPAID'  constitute a little percentage 
of the table, then the partial index plus rewriting the query would be much 
more efficient
Select
  payment_sid_c,
 lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
  lms_app.lms_payment_check_request
where
 lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
group by
  payment_sid_c

If not, you can gain some performance if you rewrite your query to be like this:

Select
  payment_sid_c,
 lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
  lms_app.lms_payment_check_request
group by
  payment_sid_c
having
 lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')

And you can also try to write the query like this:

Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)
From
(
  Select
payment_sid_c
  from
   lms_app.lms_payment_check_request
  group by
payment_sid_c
  having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
) t

Regards

Michel SALAIS
De : Kumar, Mukesh mailto:[email protected]>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela mailto:[email protected]>>
Cc : [email protected]; 
MUKESH KUMAR mailto:[email protected]>>
Objet : RE: Query Tunning related to function

Hi Rainer ,

We tried to create the partial ‘index on table but it did not help, and it is 
taking approx. 7 sec now.

Also we tried to force the query to use the index by enabling the parameter at 
session level

set enable_seqscan=false;

and it is still taking the time below is the explain plan for the same

https://explain.depesz.com/s/YRWIW#stats

Also we running the query which is actually used in application and above query 
is used in below query. Below is the explain plan for same.


https://explain.depesz.com/s/wktl#stats

Please assist


Thanks and Regards,
Mukesh Kuma

From: Ranier Vilela mailto:[email protected]>>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh mailto:[email protected]>>
Cc: [email protected]; 
MUKESH KUMAR mailto:[email protected]>>
Subject: Re: Query Tunning related to function

Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh 
mailto:[email protected]>> escreveu:
Hi Team,

We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec 
to run the query.

Query – 1

Select * from
  (
  Select payment_sid_c,
  lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
  from
  lms_app.lms_payment_check_request
  group by payment_sid_c) a
  where  paymentstatus in ('PAID', 'MANUALLYPAID')


The explain plan and other details are placed at below link for more 
information. We have checked the indexes on column but in the explain plan it 
is showing as Seq Scan which we have to find out.


https

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

2022-04-14 Thread overland 
Azure VM's are incredibly slow. I couldn't host a OpenStreetMap
database because the disk IO would die off from reasonable performance
to about 5KB/s and the data import wouldn't finish. Reboot and it would
be fine for a while then repeat. $400 a month for that. 

You are better off on bare metal outside of Azure, otherwise it is
going to be cloudy misery. I'm saving hundreds renting a bare metal
machine in a data center and I get the expected performance on top of
the cost savings. 


-Original Message-
From: "Kumar, Mukesh" 
To: [email protected]
, MUKESH KUMAR

Subject: Performance for SQL queries on Azure PostgreSQL PaaS instance
Date: Tue, 12 Apr 2022 09:10:23 +

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
 
 

Re: Query Tunning related to function

2022-04-14 Thread Bhupendra Babu 
Can you paste from oracle for

Set lines 1
Select text from dba_source
Where name =
UPPER('translate_payment_status')
And owner = 'IMS_APP'

Thanks.


On Thu, Apr 14, 2022, 12:07 PM Kumar, Mukesh 
wrote:

> Hi Michael ,
>
>
>
>
>
> We tried dropping the below values from the function, but it did not help.
>
>
>
> Also, the values PAID and MANUALLY PAID constitutes about 60 % of the
> values in table ,  and infact we tried creating the partial index and it
> did not help.
>
>
>
> The Strange thing is that we are trying to run this in oracle as we have
> done the migration recently and it is running in less than second with same
> indexes and other database objects . I can understand that comparing to
> oracle is stupidity, but this is only thing where we can compare.
>
>
>
> Below is the query we are running on oracle and comparing in postgres
>
>
>
> Below is the query and plan for same
>
>
>
> https://explain.depesz.com/s/wktl#stats
> 
>
>
>
> Any help would be appreciated.
>
>
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kumar
>
>
>
> *From:* Michel SALAIS 
> *Sent:* Thursday, April 14, 2022 11:45 PM
> *To:* Kumar, Mukesh ; 'Ranier Vilela' <
> [email protected]>
> *Cc:* [email protected]; 'MUKESH KUMAR' <
> [email protected]>
> *Subject:* RE: Query Tunning related to function
>
>
>
> Hi,
>
>
>
> This part of the function is odd and must be dropped:
>
>  IF (ret_status = payment_rec)
>
>  THEN
>
>   ret_status := payment_rec;
>
>
>
> I didn’t look really the function code and stopped on the view referenced
> by the cursor.
>
> The view (we know it just by its name) used in the function is a black box
> for us. Perhaps it is important to begin optimization there!
>
> If values 'PAID' and 'MANUALLYPAID' are an important percentage of table
> rows forcing index use is not a good thing especially when it is done with
> a non-optimized function.
>
>
>
> If rows with values 'PAID' and 'MANUALLYPAID'  constitute a little
> percentage of the table, then the partial index plus rewriting the query
> would be much more efficient
>
> Select
>
>   payment_sid_c,
>
>  lms_app.translate_payment_status(payment_sid_c) as paymentstatus
>
> from
>
>   lms_app.lms_payment_check_request
>
> where
>
>  lms_app.translate_payment_status(payment_sid_c) IN ('PAID',
> 'MANUALLYPAID')
>
> group by
>
>   payment_sid_c
>
>
>
> If not, you can gain some performance if you rewrite your query to be like
> this:
>
>
>
> Select
>
>   payment_sid_c,
>
>  lms_app.translate_payment_status(payment_sid_c) as paymentstatus
>
> from
>
>   lms_app.lms_payment_check_request
>
> group by
>
>   payment_sid_c
>
> having
>
>  lms_app.translate_payment_status(payment_sid_c) IN ('PAID',
> 'MANUALLYPAID')
>
>
>
> And you can also try to write the query like this:
>
>
>
> Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)
>
> From
>
> (
>
>   Select
>
> payment_sid_c
>
>   from
>
>lms_app.lms_payment_check_request
>
>   group by
>
> payment_sid_c
>
>   having
>
> lms_app.translate_payment_status(payment_sid_c) IN ('PAID',
> 'MANUALLYPAID')
>
> ) t
>
>
>
> Regards
>
>
>
> *Michel SALAIS*
>
> *De :* Kumar, Mukesh 
> *Envoyé :* jeudi 14 avril 2022 16:45
> *À :* Ranier Vilela 
> *Cc :* [email protected]; MUKESH KUMAR <
> [email protected]>
> *Objet :* RE: Query Tunning related to function
>
>
>
> Hi Rainer ,
>
>
>
> We tried to create the partial ‘index on table but it did not help, and it
> is taking approx. 7 sec now.
>
>
>
> Also we tried to force the query to use the index by enabling the
> parameter at session level
>
>
>
> set enable_seqscan=false;
>
>
>
> and it is still taking the time below is the explain plan for the same
>
>
>
> https://explain.depesz.com/s/YRWIW#stats
> 
>
>
>
> Also we running the query which is actually used in application and above
> query is used in below query. Below is the explain plan for same.
>
>
>
>
>
> https://explain.depesz.com/s/wktl#stats
> 
>
>
>
> Please assist
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kuma
>
>
>
> *From:* Ranier Vilela 
> *Sent:* Thursday, April 14, 2022 7:56 PM
> *To:* Kumar, Mukesh 
> *Cc:* [email protected]; MUKESH KUMAR <
> [email protected]>
> *Subject:* Re: Query Tunning related to function
>
>
>
> Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
> [email protected]> escreveu:
>
> Hi Team,
>
>
>
> We are running the below query i