Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh < mku...@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