From: | "Kumar, Mukesh" <MKumar(at)peabodyenergy(dot)com> |
---|---|
To: | Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, MUKESH KUMAR <mukesh(dot)kumar14(at)tcs(dot)com> |
Subject: | RE: Query Tunning related to function |
Date: | 2022-04-14 14:44:43 |
Message-ID: | CH0P221MB0474389B89798466EE64B228DEEF9@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)vf(at)gmail(dot)com>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar(at)peabodyenergy(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org; MUKESH KUMAR <mukesh(dot)kumar14(at)tcs(dot)com>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar(at)peabodyenergy(dot)com<mailto:MKumar(at)peabodyenergy(dot)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-7DvYJA0HYFVpY9mtO6TaqIGRjTLI2G1WNjwK8KA9I8JaEr9OWwGy5F6fC4Ed5dwEjCf_1rBCDg9rA$>
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__;!!KupS4sW4BlfImQPd!M8K66GpB-7DvYJA0HYFVpY9mtO6TaqIGRjTLI2G1WNjwK8KA9I8JaEr9OWwGy5F6fC4Ed5dwEjCf_1quLi3m8Q$>
regards,
Ranier Vilela
From | Date | Subject | |
---|---|---|---|
Next Message | Michel SALAIS | 2022-04-14 18:14:58 | RE: Query Tunning related to function |
Previous Message | Andrew Dunstan | 2022-04-14 14:40:43 | Re: SQL performance issue after migration from Oracle to Aurora postgres |