From: | "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Justin Pryzby <pryzby(at)telsasoft(dot)com>, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
Date: | 2021-08-28 18:27:27 |
Message-ID: | DM6PR15MB2554454557C7612A93F76A9D85C99@DM6PR15MB2554.namprd15.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Friday, August 27, 2021 13:43
> To: ldh(at)laurent-hasson(dot)com
> Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>; Ranier Vilela
> <ranier(dot)vf(at)gmail(dot)com>; pgsql-performance(at)postgresql(dot)org
> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2
> and 13.4
>
> "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-08-28 19:50:43 | Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
Previous Message | ldh@laurent-hasson.com | 2021-08-28 18:21:43 | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |