-----Message d'origine----- De : ldh@xxxxxxxxxxxxxxxxxx <ldh@xxxxxxxxxxxxxxxxxx> Envoyé : mardi 31 août 2021 04:18 À : ldh@xxxxxxxxxxxxxxxxxx; Julien Rouhaud <rjuju123@xxxxxxxxx> Cc : Tom Lane <tgl@xxxxxxxxxxxxx>; Ranier Vilela <ranier.vf@xxxxxxxxx>; Andrew Dunstan <andrew@xxxxxxxxxxxx>; Justin Pryzby <pryzby@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx 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 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=2137.00..2137.01 rows=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.01 rows=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
