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

[Ranier Vilela <ranier.vf@xxxxxxxxx>]

Em dom., 29 de ago. de 2021 às 10:35, Tom Lane <tgl@xxxxxxxxxxxxx> escreveu:

Ranier Vilela <ranier.vf@xxxxxxxxx> 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