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

   >  -----Original Message-----
   >  From: Tom Lane <tgl@sss.pgh.pa.us>
   >  Sent: Friday, August 27, 2021 13:43
   >  To: ldh@laurent-hasson.com
   >  Cc: Justin Pryzby <pryzby@telsasoft.com>; Ranier Vilela
   >  <ranier.vf@gmail.com>; pgsql-performance@postgresql.org
   >  Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2
   >  and 13.4
   >
   >  "ldh@laurent-hasson.com" <ldh@laurent-hasson.com> 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), (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;

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=100000 width=8) (actual time=0.014..9.270 rows=100000 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=100000 width=15) (actual time=0.008..34.494 rows=100000 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.