Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf404-0000AM-AZ for pgsql-performance@arkaria.postgresql.org; Thu, 14 Apr 2022 18:15:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nf403-0000RD-6F for pgsql-performance@arkaria.postgresql.org; Thu, 14 Apr 2022 18:15:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf402-0000R4-Pn for pgsql-performance@lists.postgresql.org; Thu, 14 Apr 2022 18:15:22 +0000 Received: from mout.kundenserver.de ([212.227.126.131]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf3zy-0006R4-QK for pgsql-performance@postgresql.org; Thu, 14 Apr 2022 18:15:22 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue012 [212.227.15.168]) with ESMTPSA (Nemesis) id 1MdwRi-1oG2uA1fj4-00b4RX; Thu, 14 Apr 2022 20:15:00 +0200 From: "Michel SALAIS" To: "'Kumar, Mukesh'" , "'Ranier Vilela'" Cc: , "'MUKESH KUMAR'" References: In-Reply-To: Subject: RE: Query Tunning related to function Date: Thu, 14 Apr 2022 20:14:58 +0200 Message-ID: <004801d8502b$8e16e2f0$aa44a8d0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0049_01D8503C.51A1FCE0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQH9NoWbYdZqrpK6tICjf1yxkAiDNQGwtOJkAaEDDxmsi023QA== Content-Language: fr X-Provags-ID: V03:K1:wWAqmzCYxh1fTCP+2C1AsS4CYftaNY4D3ny2QUUHc4gJ00vadPw b5w4Za+OMti4I53SZyZ5slZrC6xpYKzLIM0DTSHkC8oi8pjPpRgDspSW8uYgZxq21DqrdMB u1p7GUJOHHO6EaBfXSiQRWNHJ3vWwFSpMezDW8vwn0qCcW6MZ0F0L4uWDiLrfZw3/uurTPA mjg8v8P/dPq7P0LwCUgdQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:1BFiMqfwLgA=:FxiHvscnUg9aiaGbIIvSJM twBaV1jj8JARZ59MvWVHiCkI9HQSMiMnm85oqTUOf9Dpbe3UuprxZfGbXOzei8lFwJZEdrfG3 CUxq7/iAu+Id9cGEMNWvheomMUMKteuqBYy+o0juwkmySvpZGNCXL/JaHjHBuezckAxxVM84x gdoT67AlvKY+ZmDd8Idih72ZsfYxIxZjL9CjyB0Sg9ql6kxRGfA06RVM+YBAxi8Fg3wH1L27R u5z4q6sFzMSonClpGP1or1KLGcvmVIjfBTzKErh9Ddw4wGbonEFljscDokfcRamuptg1pUzcU 1BXqzXJ9yO6CBnjtghCHFNXvbi3Fip0am37hFVDAUSUgri6M9fgI7dwDN7gONsbZrb4daC6PK s8NFvftYNNCEQZVGT7x8W0c/SKnhJE54G+eSlKjWEgLSbZxgg2WfEZz/cYXgyp+xisyHTOILz IbwLwogz8M3FA0sxPhgdUFhNoP7Eid9+rgRP4sruTQN8RjmDvACwUBPE7nljEjQfblA1s8vPn srNbDQb3XxCKbonIPdJfxKwzNJ+M2MsPNvLA9E3Yzo6fFGpGqrZR1vs23PA59Q8OxkC2WnBMb qLRjpAJOsjXw5y3s4v3T8e2Lg6cbaBwOuJuzq7ZQfP69llCzNA1lwHFlR/MhQdMjgBhAmhtId gaFwncpXPyxSLxsAcywim8QkjYdDejV2WFaCu7QmQ0oAhl9vw4lXFTNa4CziT/NsxNtI= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0049_01D8503C.51A1FCE0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi, =20 This part of the function is odd and must be dropped: IF (ret_status =3D payment_rec) THEN ret_status :=3D payment_rec; =20 I didn=E2=80=99t 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. =20 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 =20 If not, you can gain some performance if you rewrite your query to be = like this: =20 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') =20 And you can also try to write the query like this: =20 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 =20 Regards =20 Michel SALAIS De : Kumar, Mukesh =20 Envoy=C3=A9 : jeudi 14 avril 2022 16:45 =C3=80 : Ranier Vilela Cc : pgsql-performance@postgresql.org; MUKESH KUMAR = Objet : RE: Query Tunning related to function =20 Hi Rainer ,=20 =20 We tried to create the partial =E2=80=98index on table but it did not = help, and it is taking approx. 7 sec now. =20 Also we tried to force the query to use the index by enabling the = parameter at session level =20 set enable_seqscan=3Dfalse; =20 and it is still taking the time below is the explain plan for the same=20 =20 https://explain.depesz.com/s/YRWIW#stats =20 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. =20 =20 https://explain.depesz.com/s/wktl#stats =20 Please assist=20 =20 =20 Thanks and Regards,=20 Mukesh Kuma=20 =20 From: Ranier Vilela >=20 Sent: Thursday, April 14, 2022 7:56 PM To: Kumar, Mukesh > Cc: pgsql-performance@postgresql.org = ; MUKESH KUMAR = > Subject: Re: Query Tunning related to function =20 Em qui., 14 de abr. de 2022 =C3=A0s 08:01, Kumar, Mukesh = > escreveu: Hi Team,=20 =20 We are running the below query in PostgreSQL and its taking approx. 8 to = 9 sec to run the query. =20 Query =E2=80=93 1=20 =20 Select * from=20 ( Select payment_sid_c, lms_app.translate_payment_status(payment_sid_c) AS paymentstatus=20 from=20 lms_app.lms_payment_check_request group by payment_sid_c) a =20 where paymentstatus in ('PAID', 'MANUALLYPAID') =20 =20 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. =20 =20 https://explain.depesz.com/s/Jsiw#stats = =20 =20 =20 This query is using a function translate_payment_status on column = payment_sid_c whose script is attached in this mail =20 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 =3D ANY = ('{PAID,MANUALLYPAID}'::text[]))=20 =20 See at: https://www.postgresql.org/docs/current/indexes-partial.html = =20 =20 regards, Ranier Vilela ------=_NextPart_000_0049_01D8503C.51A1FCE0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

This part of the function is odd = and must be dropped:

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 IF (ret_status =3D = payment_rec)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 THEN

=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ret_status :=3D = payment_rec;

 

I didn=E2=80=99t 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' =C2=A0constitute a little percentage of the = table, then the partial index plus rewriting the query would be much = more efficient

Select

=C2=A0 = payment_sid_c,

= =C2=A0lms_app.translate_payment_status(payment_sid_c) as = paymentstatus

from

=C2=A0 = lms_app.lms_payment_check_request

where

= =C2=A0lms_app.translate_payment_status(payment_sid_c) IN ('PAID', = 'MANUALLYPAID')

group by

=C2=A0 = payment_sid_c

 

If not, you can gain some performance if you rewrite your = query to be like this:

 

Select

=C2=A0 = payment_sid_c,

= =C2=A0lms_app.translate_payment_status(payment_sid_c) as = paymentstatus

from

=C2=A0 = lms_app.lms_payment_check_request

group by

=C2=A0 = payment_sid_c

having

= =C2=A0lms_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

(

=C2=A0 Select

=C2=A0 = =C2=A0=C2=A0payment_sid_c

=C2=A0 from

= =C2=A0=C2=A0=C2=A0lms_app.lms_payment_check_request

=

=C2=A0 group by

=C2=A0 = =C2=A0=C2=A0payment_sid_c

=C2=A0 having

=C2=A0 = =C2=A0=C2=A0lms_app.translate_payment_status(payment_sid_c) IN ('PAID', = 'MANUALLYPAID')

) t

 

Regards

 

Michel = SALAIS

De : Kumar, Mukesh = <MKumar@peabodyenergy.com>
Envoy=C3=A9 : jeudi 14 = avril 2022 16:45
=C3=80 : Ranier Vilela = <ranier.vf@gmail.com>
Cc : = pgsql-performance@postgresql.org; MUKESH KUMAR = <mukesh.kumar14@tcs.com>
Objet : RE: Query Tunning = related to function

 

Hi Rainer ,

 

We tried to create the partial =E2=80=98index 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=3Dfalse;

 

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.c= om/s/wktl#stats

 

Please assist

 

 

Thanks and Regards,

Mukesh Kuma =

 

From: Ranier Vilela <ranier.vf@gmail.com> =
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, = Mukesh <MKumar@peabodyenergy.com>=
Cc: pgsql-performance@postgr= esql.org; MUKESH KUMAR <mukesh.kumar14@tcs.com>
= Subject: Re: Query Tunning related to = function

 

Em qui., 14 de abr. de 2022 =C3=A0s = 08:01, Kumar, Mukesh <MKumar@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 =E2=80=93 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<= span lang=3DEN-US>

 

 

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 =3D ANY = ('{PAID,MANUALLYPAID}'::text[]))

 

See = at:

 

regards,

Ranier = Vilela

------=_NextPart_000_0049_01D8503C.51A1FCE0--