From: | Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> |
---|---|
To: | "Kumar, Mukesh" <MKumar(at)peabodyenergy(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, MUKESH KUMAR <mukesh(dot)kumar14(at)tcs(dot)com> |
Subject: | Re: Query Tunning related to function |
Date: | 2022-04-14 14:26:05 |
Message-ID: | CAEudQAoD2zrThSuQdg4-=e88He8yEkpXnJAWSvL0r-pTyLy8eg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2022-04-14 14:40:43 | Re: SQL performance issue after migration from Oracle to Aurora postgres |
Previous Message | Goti | 2022-04-14 09:35:05 | SQL performance issue after migration from Oracle to Aurora postgres |