RE: Query Tunning related to function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All ,

 

We request you to please provide some assistance on below issue and it is impacting the migration project.

 

Thanks and Regards,

Mukesh Kumar

 

From: Kumar, Mukesh
Sent: Friday, April 15, 2022 11:43 AM
To: Bhupendra Babu <bbabu12@xxxxxxxxx>
Cc: Michel SALAIS <msalais@xxxxxxx>; Ranier Vilela <ranier.vf@xxxxxxxxx>; postgres performance list <pgsql-performance@xxxxxxxxxxxxxx>; MUKESH KUMAR <mukesh.kumar14@xxxxxxx>; heda.giriraj@xxxxxxx
Subject: RE: Query Tunning related to function

 

Hi Babu ,

 

Please find below the script for the function from Oracle

 

Hi babu ,

 

Please find attached the script for function from Oracle .

 

Please revert in case of any query.

 

Thanks and Regards,

Mukesh Kumar

 

From: Bhupendra Babu <bbabu12@xxxxxxxxx>
Sent: Friday, April 15, 2022 3:44 AM
To: Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx>
Cc: Michel SALAIS <msalais@xxxxxxx>; Ranier Vilela <ranier.vf@xxxxxxxxx>; postgres performance list <pgsql-performance@xxxxxxxxxxxxxx>; MUKESH KUMAR <mukesh.kumar14@xxxxxxx>; heda.giriraj@xxxxxxx
Subject: Re: Query Tunning related to function

 

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 <MKumar@xxxxxxxxxxxxxxxxx> 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

 

Any help would be appreciated.

 

 

 

Thanks and Regards,

Mukesh Kumar

 

From: Michel SALAIS <msalais@xxxxxxx>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx>; 'Ranier Vilela' <ranier.vf@xxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx; 'MUKESH KUMAR' <mukesh.kumar14@xxxxxxx>
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 <MKumar@xxxxxxxxxxxxxxxxx>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela <ranier.vf@xxxxxxxxx>
Cc : pgsql-performance@xxxxxxxxxxxxxx; MUKESH KUMAR <mukesh.kumar14@xxxxxxx>
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

 

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>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx; MUKESH KUMAR <mukesh.kumar14@xxxxxxx>
Subject: Re: Query Tunning related to function

 

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

 

 

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:

 

regards,

Ranier Vilela


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux