RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Tom Lane > Sent: Friday, August 27, 2021 13:43 > To: [email protected] > Cc: Justin Pryzby ; Ranier Vilela > ; [email protected] > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > "[email protected]" writes: > > That being said, do you have any suggestion how I could circumvent > the > > issue altogether? > > Based on Andrew's report, it seems like you might be able to work > around it for the time being by disabling message translations, i.e. >SET lc_messages = 'C'; > Even if that's not acceptable in your work environment, it would be > useful to verify that you see an improvement from it. > >regards, tom lane SET lc_messages = 'C'; drop table sampletest; create table sampletest (a varchar, b varchar); insert into sampletest (a, b) select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar from generate_series(1,10); 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; show lc_messages; -- OK 'C'
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Tom Lane > Sent: Friday, August 27, 2021 13:43 > To: [email protected] > Cc: Justin Pryzby ; Ranier Vilela > ; [email protected] > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > "[email protected]" writes: > > That being said, do you have any suggestion how I could circumvent > the > > issue altogether? > > Based on Andrew's report, it seems like you might be able to work > around it for the time being by disabling message translations, i.e. >SET lc_messages = 'C'; > Even if that's not acceptable in your work environment, it would be > useful to verify that you see an improvement from it. > >regards, tom lane Hello Tom hit the send button accidentally. SET lc_messages = 'C'; drop table sampletest; create table sampletest (a varchar, b varchar); insert into sampletest (a, b) select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar from generate_series(1,10); 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; show lc_messages; --> OK 'C' 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=175.551..175.552 rows=1 loops=1) Buffers: shared hit=637 -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=8) (actual time=0.014..9.270 rows=10 loops=1) Buffers: shared hit=637 Planning Time: 0.087 ms Execution Time: 175.600 ms 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=88031.549..88031.551 rows=1 loops=1) Buffers: shared hit=637 -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=15) (actual time=0.008..34.494 rows=10 loops=1) Buffers: shared hit=637 Planning: Buffers: shared hit=4 Planning Time: 0.171 ms Execution Time: 88031.585 ms Doesn't seem to make a difference unless I misunderstood what you were asking for regarding the locale? Thank you, Laurent.
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
"[email protected]" writes: > SET lc_messages = 'C'; > show lc_messages; --> OK 'C' > explain (analyze,buffers,COSTS,TIMING) > select MAX(toFloat(b, null)) as "b" from sampletest > ... > Execution Time: 175.600 ms > explain (analyze,buffers,COSTS,TIMING) > select MAX(toFloat(a, null)) as "a" from sampletest > ... > Execution Time: 88031.585 ms > Doesn't seem to make a difference unless I misunderstood what you were asking > for regarding the locale? Hmm. This suggests that whatever effect Andrew found with NLS is actually not the explanation for your problem. So I'm even more confused than before. regards, tom lane
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Tom Lane > Sent: Saturday, August 28, 2021 15:51 > To: [email protected] > Cc: Andrew Dunstan ; Justin Pryzby > ; Ranier Vilela ; pgsql- > [email protected] > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > "[email protected]" writes: > > SET lc_messages = 'C'; > > show lc_messages; --> OK 'C' > > > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as > > "b" from sampletest ... > > Execution Time: 175.600 ms > > > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as > > "a" from sampletest ... > > Execution Time: 88031.585 ms > > > Doesn't seem to make a difference unless I misunderstood what you > were asking for regarding the locale? > > Hmm. This suggests that whatever effect Andrew found with NLS is > actually not the explanation for your problem. So I'm even more > confused than before. > >regards, tom lane I am so sorry to hear... So, curious on my end: is this something that you are not able to reproduce on your environments? On my end, I did reproduce it on different VMs and my local laptop, across windows Server 2012 and Windows 10, so I'd figure it would be pretty easy to reproduce? Thank you! Laurent.
