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 <mku...@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!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
>
>
>
> Any help would be appreciated.
>
>
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kumar
>
>
>
> *From:* Michel SALAIS <msal...@msym.fr>
> *Sent:* Thursday, April 14, 2022 11:45 PM
> *To:* Kumar, Mukesh <mku...@peabodyenergy.com>; 'Ranier Vilela' <
> ranier...@gmail.com>
> *Cc:* pgsql-performa...@postgresql.org; 'MUKESH KUMAR' <
> mukesh.kuma...@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 <mku...@peabodyenergy.com>
> *Envoyé :* jeudi 14 avril 2022 16:45
> *À :* Ranier Vilela <ranier...@gmail.com>
> *Cc :* pgsql-performa...@postgresql.org; MUKESH KUMAR <
> mukesh.kuma...@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!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJVb2g-4s$>
>
>
>
> 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!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
>
>
>
> Please assist
>
>
>
>
>
> Thanks and Regards,
>
> Mukesh Kuma
>
>
>
> *From:* Ranier Vilela <ranier...@gmail.com>
> *Sent:* Thursday, April 14, 2022 7:56 PM
> *To:* Kumar, Mukesh <mku...@peabodyenergy.com>
> *Cc:* pgsql-performa...@postgresql.org; MUKESH KUMAR <
> mukesh.kuma...@tcs.com>
> *Subject:* Re: Query Tunning related to function
>
>
>
> 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://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
>
