From: "Michel SALAIS" <msalais@msym.fr>
To: <ldh@laurent-hasson.com>,
	"'Julien Rouhaud'" <rjuju123@gmail.com>
Cc: "'Tom Lane'" <tgl@sss.pgh.pa.us>,
	"'Ranier Vilela'" <ranier.vf@gmail.com>,
	"'Andrew Dunstan'" <andrew@dunslane.net>,
	"'Justin Pryzby'" <pryzby@telsasoft.com>,
	<pgsql-performance@postgresql.org>
References: 
 <MN2PR15MB2560AE2A878FBBE88DCFF33385C29@MN2PR15MB2560.namprd15.prod.outlook.com>
 <2092859.1629558281@sss.pgh.pa.us>
 <MN2PR15MB25607BDC72A325CA9B9D3E3385C29@MN2PR15MB2560.namprd15.prod.outlook.com>
 <2136207.1629569846@sss.pgh.pa.us> <20210821191950.GW10479@telsasoft.com>
 <MN2PR15MB2560ED2A8F967B14E7D7881E85C29@MN2PR15MB2560.namprd15.prod.outlook.com>
 <20210821221729.GY10479@telsasoft.com>
 <MN2PR15MB2560FB03666CEABB651FB32185C29@MN2PR15MB2560.namprd15.prod.outlook.com>
 <MN2PR15MB256034533A31A7C11E3C347985C39@MN2PR15MB2560.namprd15.prod.outlook.com>
 <CAEudQAocYZieAMgCtXa8Oau8=dVJ1_R1r0coLeF1LfiYkBCTNA@mail.gmail.com>
 <20210822154758.GZ10479@telsasoft.com> <30496.1629654632@sss.pgh.pa.us>
 <MN2PR15MB2560C3FF951319B18A3CA83585C39@MN2PR15MB2560.namprd15.prod.outlook.com>
 <MN2PR15MB25601CCA9E337403E1754BDE85C39@MN2PR15MB2560.namprd15.prod.outlook.com>
 <35729.1629660223@sss.pgh.pa.us>
 <MN2PR15MB2560634E97465D56FFB4601D85C39@MN2PR15MB2560.namprd15.prod.outlook.com>
 <846170.1630086205@sss.pgh.pa.
	us>
 <DM6PR15MB2554454557C7612A93F76A9D85C99@DM6PR15MB2554.namprd15.prod.outlook.com>
 <1050917.1630180243@sss.pgh.pa.us>
 <MN2PR15MB2560A083DA8CD3418BEA1DA085CA9@MN2PR15MB2560.namprd15.prod.outlook.com>
 <CAEudQApD2F17Fz7cQNUZbrx7K3P1Y=KFCc4V3UZthPFgp+radQ@mail.gmail.com>
 <1124170.1630244105@sss.pgh.pa.us>
 <CAEudQAo75GRW3jMsJLXcN1ufXj=BTdkeO5cA_3yqHHJkCWqVag@mail.gmail.com>
 <MN2PR15MB25605C2D5D70A6EAF99B5F6085CA9@MN2PR15MB2560.namprd15.prod.outlook.com>
 <1177685.1630253948@sss.pgh.pa.us>
 <MN2PR15MB2560B122A49C4C06483183D785CB9@MN2PR15MB2560.namprd15.prod.outlook.com>
 <CAOBaU_bYtEDWwj+6-RsGkikP3xKmq=J8TBrOqUrw7h8CVoAVuA@mail.gmail.com>
 <MN2PR15MB25604A1E1EBBFDB13A736BB385CB9@MN2PR15MB2560.namprd15.prod.outlook.com>
 <MN2PR15MB256056461A0DC961388ECFA985CC9@MN2PR15MB2560.namprd15.prod.outlook.com>
In-Reply-To: 
 <MN2PR15MB256056461A0DC961388ECFA985CC9@MN2PR15MB2560.namprd15.prod.outlook.com>
Subject: RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Date: Tue, 31 Aug 2021 08:27:40 +0200
Message-ID: <009201d79e31$4d86b960$e8942c20$@msym.fr>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: 
 AQM/eypU0Br0reo5v0VXOMbHFxNU6AIJTxxcANfbqHUBfNjjyQIqbaOhAliooL0DNe+d1wFGSpaQAvrYEQcBNcDQ1wHCbfpdAih9qK8B+iOrRAGaYjL0AW8txD0CwaXG3AD7SIlAAiwe7O8CbLuufwHiaplVAh02190CU9vrKgIN1farAnF7MNMB7inxegHezXMpAW6yPoYBzIvfkAGn1YFipwrBNyA=
Content-Language: fr
Archived-At: 
 <https://www.postgresql.org/message-id/009201d79e31%244d86b960%24e8942c20%24%40msym.fr>
Precedence: bulk

-----Message d'origine-----
De : ldh@laurent-hasson.com <ldh@laurent-hasson.com>=20
Envoy=C3=A9 : mardi 31 ao=C3=BBt 2021 04:18
=C3=80 : ldh@laurent-hasson.com; Julien Rouhaud <rjuju123@gmail.com>
Cc : Tom Lane <tgl@sss.pgh.pa.us>; Ranier Vilela <ranier.vf@gmail.com>; =
Andrew Dunstan <andrew@dunslane.net>; Justin Pryzby =
<pryzby@telsasoft.com>; pgsql-performance@postgresql.org
Objet : RE: Big Performance drop of Exceptions in UDFs between V11.2 and =
13.4
Importance : Haute


   >  I see on https://www.postgresql.org/download/ that there is a =
different
   >  installer from 2ndQuadrant. I am going to try that one and see =
what I
   >  come up with. Are there any other "standard" distros of Postgres =
that I
   >  could try out?
   > =20
   >  I found out I could download Visual Studio community edition so I =
am
   >  trying this, but may not have the time to get through a build any =
time
   >  soon as per my unfamiliarity with the process. I'll follow =
Ranier's steps
   >  and see if that gets me somewhere.
   > =20
   >  Thank you,
   >  Laurent.


Hello all,

I think I had a breakthrough. I tried to create a local build and wasn't =
able to. But I downloaded the 2nd Quadrant installer and the issue =
disappeared!!! I think this is proof that it's not my personal =
environment, nor something intrinsic in the codebase, but definitely =
something in the standard EDB installer.


create table sampletest (a varchar, b varchar); insert into sampletest =
(a, b) select substr(md5(random()::text), 0, 15), =
(100000000*random())::integer::varchar
  from generate_series(1,100000);

CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) RETURNS real =
AS $$ BEGIN
  RETURN case when str is null then val else str::real end; EXCEPTION =
WHEN OTHERS THEN
  RETURN val;
END;
$$ LANGUAGE plpgsql COST 1 IMMUTABLE;

explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as =
"a" from sampletest; --Aggregate  (cost=3D2137.00..2137.01 rows=3D1 =
width=3D4) (actual time=3D2092.922..2092.923 rows=3D1 loops=3D1)
--  Buffers: shared hit=3D637
--  ->  Seq Scan on sampletest  (cost=3D0.00..1637.00 rows=3D100000 =
width=3D15) (actual time=3D0.028..23.925 rows=3D100000 loops=3D1)
--        Buffers: shared hit=3D637
--Planning Time: 0.168 ms
--Execution Time: 2092.957 ms

explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as =
"b" from sampletest; --Aggregate  (cost=3D2137.00..2137.01 rows=3D1 =
width=3D4) (actual time=3D369.475..369.476 rows=3D1 loops=3D1)
--  Buffers: shared hit=3D637
--  ->  Seq Scan on sampletest  (cost=3D0.00..1637.00 rows=3D100000 =
width=3D8) (actual time=3D0.020..18.746 rows=3D100000 loops=3D1)
--        Buffers: shared hit=3D637
--Planning Time: 0.129 ms
--Execution Time: 369.507 ms


Thank you,
Laurent!

_________________________________________________________
Hi,

Something which has nothing with the thread but I think it must be said =
:-)
Why substring(x, 0, ...)?
msym=3D> select substr('abcde', 0, 3),  substr('abcde', 1, 3);
 substr | substr
--------+--------
 ab     | abc

Michel SALAIS