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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx> escreveu:
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: regards, Ranier Vilela |
