Re: Query Tunning related to function

Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh <
MKumar(at)peabodyenergy(dot)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