From: | "Michel SALAIS" <msalais(at)msym(dot)fr> |
---|---|
To: | <ldh(at)laurent-hasson(dot)com>, "'Julien Rouhaud'" <rjuju123(at)gmail(dot)com> |
Cc: | "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Ranier Vilela'" <ranier(dot)vf(at)gmail(dot)com>, "'Andrew Dunstan'" <andrew(at)dunslane(dot)net>, "'Justin Pryzby'" <pryzby(at)telsasoft(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
Date: | 2021-08-31 06:27:40 |
Message-ID: | 009201d79e31$4d86b960$e8942c20$@msym.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
-----Message d'origine-----
De : ldh(at)laurent-hasson(dot)com <ldh(at)laurent-hasson(dot)com>
Envoyé : mardi 31 août 2021 04:18
À : ldh(at)laurent-hasson(dot)com; Julien Rouhaud <rjuju123(at)gmail(dot)com>
Cc : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>; Andrew Dunstan <andrew(at)dunslane(dot)net>; Justin Pryzby <pryzby(at)telsasoft(dot)com>; pgsql-performance(at)postgresql(dot)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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2021-08-31 13:40:12 | Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
Previous Message | ldh@laurent-hasson.com | 2021-08-31 02:18:16 | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |