pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Tunning related to function
10+ messages / 6 participants
[nested] [flat]
* Query Tunning related to function
@ 2022-04-14 06:03 Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 2 replies; 10+ messages in thread
From: Kumar, Mukesh @ 2022-04-14 06:03 UTC (permalink / raw)
To: pgsql-performance; +Cc: MUKESH KUMAR <mukesh.kumar14@tcs.com>
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
Attachments:
[application/octet-stream] function.sql (1.9K, ../../CH0P221MB04744DB33957A93BB2F7782EDEEF9@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/3-function.sql)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query Tunning related to function
@ 2022-04-14 14:26 Ranier Vilela <ranier.vf@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ranier Vilela @ 2022-04-14 14:26 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
MKumar@peabodyenergy.com> 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
> <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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Query Tunning related to function
@ 2022-04-14 14:44 Kumar, Mukesh <MKumar@peabodyenergy.com>
parent: Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kumar, Mukesh @ 2022-04-14 14:44 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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 <ranier.vf@gmail.com>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com>
Cc: pgsql-performance@postgresql.org; MUKESH KUMAR <mukesh.kumar14@tcs.com>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/J...;
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<https://urldefense.com/v3/__https:/w...;
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Query Tunning related to function
@ 2022-04-14 18:14 Michel SALAIS <msalais@msym.fr>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michel SALAIS @ 2022-04-14 18:14 UTC (permalink / raw)
To: 'Kumar, Mukesh' <MKumar@peabodyenergy.com>; 'Ranier Vilela' <ranier.vf@gmail.com>; +Cc: pgsql-performance; 'MUKESH KUMAR' <mukesh.kumar14@tcs.com>
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 <MKumar@peabodyenergy.com>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela <ranier.vf@gmail.com>
Cc : pgsql-performance@postgresql.org; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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 <ranier.vf@gmail.com <mailto:ranier.vf@gmail.com> >
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com <mailto:MKumar@peabodyenergy.com> >
Cc: pgsql-performance@postgresql.org <mailto:pgsql-performance@postgresql.org> ; MUKESH KUMAR <mukesh.kumar14@tcs.com <mailto:mukesh.kumar14@tcs.com> >
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@peabodyenergy.com <mailto:MKumar@peabodyenergy.com> > 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 <https://urldefense.com/v3/__https:/explain.depesz.com/s/Jsiw*stats__;Iw!!KupS4sW4BlfImQPd!M8K66GpB-7...;
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 <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/indexes-partial.html__;!!KupS4sW4...;
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Query Tunning related to function
@ 2022-04-14 18:45 Kumar, Mukesh <MKumar@peabodyenergy.com>
parent: Michel SALAIS <msalais@msym.fr>
0 siblings, 1 reply; 10+ messages in thread
From: Kumar, Mukesh @ 2022-04-14 18:45 UTC (permalink / raw)
To: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com <heda.giriraj@tcs.com>
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS <msalais@msym.fr>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; 'Ranier Vilela' <ranier.vf@gmail.com>
Cc: pgsql-performance@postgresql.org; 'MUKESH KUMAR' <mukesh.kumar14@tcs.com>
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 <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Cc : pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/...;
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Please assist
Thanks and Regards,
Mukesh Kuma
From: Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/J...;
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<https://urldefense.com/v3/__https:/w...;
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query Tunning related to function
@ 2022-04-14 22:13 Bhupendra Babu <bbabu12@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bhupendra Babu @ 2022-04-14 22:13 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com
Can you paste from oracle for
Set lines 10000
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 <MKumar@peabodyenergy.com>
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
> <https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv8...;
>
>
>
> Any help would be appreciated.
>
>
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kumar
>
>
>
> *From:* Michel SALAIS <msalais@msym.fr>
> *Sent:* Thursday, April 14, 2022 11:45 PM
> *To:* Kumar, Mukesh <MKumar@peabodyenergy.com>; 'Ranier Vilela' <
> ranier.vf@gmail.com>
> *Cc:* pgsql-performance@postgresql.org; 'MUKESH KUMAR' <
> mukesh.kumar14@tcs.com>
> *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 <MKumar@peabodyenergy.com>
> *Envoyé :* jeudi 14 avril 2022 16:45
> *À :* Ranier Vilela <ranier.vf@gmail.com>
> *Cc :* pgsql-performance@postgresql.org; MUKESH KUMAR <
> mukesh.kumar14@tcs.com>
> *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
> <https://urldefense.com/v3/__https:/explain.depesz.com/s/YRWIW*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv...;
>
>
>
> 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
> <https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv8...;
>
>
>
> Please assist
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kuma
>
>
>
> *From:* Ranier Vilela <ranier.vf@gmail.com>
> *Sent:* Thursday, April 14, 2022 7:56 PM
> *To:* Kumar, Mukesh <MKumar@peabodyenergy.com>
> *Cc:* pgsql-performance@postgresql.org; MUKESH KUMAR <
> mukesh.kumar14@tcs.com>
> *Subject:* Re: Query Tunning related to function
>
>
>
> Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
> MKumar@peabodyenergy.com> 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
> <https://urldefense.com/v3/__https:/explain.depesz.com/s/Jsiw*stats__;Iw!!KupS4sW4BlfImQPd!M8K66GpB-7...
>
>
>
>
>
> 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
> <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/indexes-partial.html__;!!KupS4sW4...;
>
>
>
> regards,
>
> Ranier Vilela
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Query Tunning related to function
@ 2022-04-15 06:13 Kumar, Mukesh <MKumar@peabodyenergy.com>
parent: Bhupendra Babu <bbabu12@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kumar, Mukesh @ 2022-04-15 06:13 UTC (permalink / raw)
To: Bhupendra Babu <bbabu12@gmail.com>; +Cc: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com <heda.giriraj@tcs.com>
Hi Babu ,
Please find below the script for the function from Oracle
Hi babu ,
Please find attached the script for function from Oracle .
Please revert in case of any query.
Thanks and Regards,
Mukesh Kumar
From: Bhupendra Babu <bbabu12@gmail.com>
Sent: Friday, April 15, 2022 3:44 AM
To: Kumar, Mukesh <MKumar@peabodyenergy.com>
Cc: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; postgres performance list <pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com
Subject: Re: Query Tunning related to function
Can you paste from oracle for
Set lines 10000
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 <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS <msalais@msym.fr<mailto:msalais@msym.fr>>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>; 'Ranier Vilela' <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; 'MUKESH KUMAR' <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
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 <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Cc : pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/...;
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Please assist
Thanks and Regards,
Mukesh Kuma
From: Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/J...;
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<https://urldefense.com/v3/__https:/w...;
regards,
Ranier Vilela
Attachments:
[application/octet-stream] translate_payment_status.sql (1.6K, ../../CH0P221MB0474955C0A445D7FAAD2ED38DEEE9@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/3-translate_payment_status.sql)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Query Tunning related to function
@ 2022-04-15 16:46 Kumar, Mukesh <MKumar@peabodyenergy.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kumar, Mukesh @ 2022-04-15 16:46 UTC (permalink / raw)
To: Bhupendra Babu <bbabu12@gmail.com>; +Cc: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com <heda.giriraj@tcs.com>
Hi All ,
We request you to please provide some assistance on below issue and it is impacting the migration project.
Thanks and Regards,
Mukesh Kumar
From: Kumar, Mukesh
Sent: Friday, April 15, 2022 11:43 AM
To: Bhupendra Babu <bbabu12@gmail.com>
Cc: Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; postgres performance list <pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com
Subject: RE: Query Tunning related to function
Hi Babu ,
Please find below the script for the function from Oracle
Hi babu ,
Please find attached the script for function from Oracle .
Please revert in case of any query.
Thanks and Regards,
Mukesh Kumar
From: Bhupendra Babu <bbabu12@gmail.com<mailto:bbabu12@gmail.com>>
Sent: Friday, April 15, 2022 3:44 AM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Cc: Michel SALAIS <msalais@msym.fr<mailto:msalais@msym.fr>>; Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>; postgres performance list <pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>; heda.giriraj@tcs.com<mailto:heda.giriraj@tcs.com>
Subject: Re: Query Tunning related to function
Can you paste from oracle for
Set lines 10000
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 <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS <msalais@msym.fr<mailto:msalais@msym.fr>>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>; 'Ranier Vilela' <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; 'MUKESH KUMAR' <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
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 <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Cc : pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/...;
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<https://urldefense.com/v3/__https:/explain.depesz.com/s/w...;
Please assist
Thanks and Regards,
Mukesh Kuma
From: Ranier Vilela <ranier.vf@gmail.com<mailto:ranier.vf@gmail.com>>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; MUKESH KUMAR <mukesh.kumar14@tcs.com<mailto:mukesh.kumar14@tcs.com>>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@peabodyenergy.com<mailto:MKumar@peabodyenergy.com>> 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<https://urldefense.com/v3/__https:/explain.depesz.com/s/J...;
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<https://urldefense.com/v3/__https:/w...;
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query Tunning related to function
@ 2022-04-17 03:13 Justin Pryzby <pryzby@telsasoft.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
1 sibling, 0 replies; 10+ messages in thread
From: Justin Pryzby @ 2022-04-17 03:13 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: pgsql-performance@postgresql.org, MUKESH KUMAR <mukesh.kumar14@tcs.com>
On Thu, Apr 14, 2022 at 06:03:33AM +0000, Kumar, Mukesh wrote:
> We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec to run the query.
>
> Query - 1 ...
>
> 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
There's a list of information to provide in the postgres wiki, and people here
sent you a link to that wiki page on Feb 27, Mar 1, and Apr 12. Your problem
report is still missing a lot of that information. Asking for it piece by
piece would be tedious.
--
Justin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query Tunning related to function
@ 2022-04-17 16:47 David G. Johnston <david.g.johnston@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2022-04-17 16:47 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: Bhupendra Babu <bbabu12@gmail.com>; Michel SALAIS <msalais@msym.fr>; Ranier Vilela <ranier.vf@gmail.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>; heda.giriraj@tcs.com <heda.giriraj@tcs.com>
On Sun, Apr 17, 2022 at 8:53 AM Kumar, Mukesh <MKumar@peabodyenergy.com>
wrote:
> We request you to please provide some assistance on below issue and it is
> impacting the migration project.
>
I suggest you try and re-write the loop-based function into a set-oriented
view.
Specifically, I think doing: "array_agg(DISTINCT paymenttype)" and then
checking for various array results will be considerably more efficient.
Or do a combination: write the set-oriented query in an SQL function. You
should not need pl/pgsql for this and avoiding it should improve
performance.
David J.
p.s., The convention on these lists is to inline post and remove unneeded
context. Or at least bottom post.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-04-17 16:47 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-14 06:03 Query Tunning related to function Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-14 14:26 ` Ranier Vilela <ranier.vf@gmail.com>
2022-04-14 14:44 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-14 18:14 ` Michel SALAIS <msalais@msym.fr>
2022-04-14 18:45 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-14 22:13 ` Bhupendra Babu <bbabu12@gmail.com>
2022-04-15 06:13 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-15 16:46 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-17 16:47 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-17 03:13 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox