RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

-----Message d'origine-----
De : ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Envoyé : mardi 31 août 2021 04:18
À : 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?
   >
   >  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.
   >
   >  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
installerand the issue disappeared!!! I think this is proof that it's not my personal environment, nor something
intrinsicin 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=2137.00..2137.01rows=1 width=4) (actual time=2092.922..2092.923 rows=1 loops=1) 
--  Buffers: shared hit=637
--  ->  Seq Scan on sampletest  (cost=0.00..1637.00 rows=100000 width=15) (actual time=0.028..23.925 rows=100000
loops=1)
--        Buffers: shared hit=637
--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=2137.00..2137.01rows=1 width=4) (actual time=369.475..369.476 rows=1 loops=1) 
--  Buffers: shared hit=637
--  ->  Seq Scan on sampletest  (cost=0.00..1637.00 rows=100000 width=8) (actual time=0.020..18.746 rows=100000
loops=1)
--        Buffers: shared hit=637
--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=> select substr('abcde', 0, 3),  substr('abcde', 1, 3);
 substr | substr
--------+--------
 ab     | abc

Michel SALAIS