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

Em dom., 29 de ago. de 2021 às 10:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:

> Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> writes:
> > I retested this case with HEAD, and it seems to me that NLS does affect
> it.
>
> Sure, there's no question that message translation will have *some* cost.
> But on my machine it is an incremental tens-of-percent kind of cost,
> and that is the result you're getting as well. So it's not very clear
> where these factor-of-several-hundred differences are coming from.
>
A hypothesis that has not yet come up, may be some defect in the code
generation,
by the previous msvc compiler used, because in all my tests I always use
the latest version,
which has several corrections in the code generation part.

View this test with one of the attempts to reproduce the problem.
msvc: 19.29.30133 para x64
windows 10 64 bits
Postgres: 12.8

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.8, compiled by Visual C++ build 1929, 64-bit
(1 row)

postgres=# drop table sampletest;
DROP TABLE
postgres=# create table sampletest (a varchar, b varchar);
CREATE TABLE
postgres=# insert into sampletest (a, b)
postgres-# select substr(md5(random()::text), 0, 15),
(100000000*random())::integer::varchar
postgres-# from generate_series(1,100000);
INSERT 0 100000
postgres=#
postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
postgres-# RETURNS real AS $$
postgres$# BEGIN
postgres$# RETURN case when str is null then val else str::real end;
postgres$# EXCEPTION WHEN OTHERS THEN
postgres$# RETURN val;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql COST 1 IMMUTABLE;
CREATE FUNCTION
postgres=# explain (analyze,buffers,COSTS,TIMING)
postgres-# select MAX(toFloat(b, null)) as "b" from sampletest;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual
time=339.978..339.979 rows=1 loops=1)
Buffers: shared hit=644
-> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32)
(actual time=0.032..18.132 rows=100000 loops=1)
Buffers: shared hit=637
Planning Time: 3.631 ms
Execution Time: 340.330 ms
(6 rows)

postgres=# explain (analyze,buffers,COSTS,TIMING)
postgres-# select MAX(toFloat(a, null)) as "a" from sampletest;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual
time=1724.902..1724.903 rows=1 loops=1)
Buffers: shared hit=640
-> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32)
(actual time=0.021..23.489 rows=100000 loops=1)
Buffers: shared hit=637
Planning Time: 0.150 ms
Execution Time: 1724.930 ms
(6 rows)

regards,
Ranier Vilela