|
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 <msalais@xxxxxxx> 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 |
