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 21:29, ldh@xxxxxxxxxxxxxxxxxx <ldh@xxxxxxxxxxxxxxxxxx> escreveu:

From: Ranier Vilela <ranier.vf@xxxxxxxxx>
Sent: Sunday, August 29, 2021 14:20
To: ldh@xxxxxxxxxxxxxxxxxx
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; Andrew Dunstan <andrew@xxxxxxxxxxxx>; Justin Pryzby <pryzby@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

Em dom., 29 de ago. de 2021 às 13:03, mailto:ldh@xxxxxxxxxxxxxxxxxx <mailto:ldh@xxxxxxxxxxxxxxxxxx> escreveu:
>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.

------------------------------------------------------------------------------------------------------------------------

Hello all,

I don't think this reproduces the issue I experience. I saw a difference of around 500x! What you see is 5x, which according to Tom would be expected for an execution path involving exceptions. And NLS should have an impact as well since more work happens. From the numbers you published, I see 10-15% change which again would be expected?
Yes, It seems to me that is expected for NLS usage.
 

I cannot think of anything that would be specific to me with regards to this scenario given that I have tried it in quite a few environments from plain stock installs. Until one of you is able to reproduce this, you may be chasing other issues.
I think I'm unable to reproduce the issue, because I didn't use any plain stock installs.
Postgres env tests here, is a fresh build with the latest msvc.
I have no intention of repeating the issue, with something exactly the same as your environment,
but with a very different environment.

Can you show the version of Postgres, at your Windows 10 env, who got this result?
Planning Time: 0.171 ms
Execution Time: 88031.585 ms

regards,
Ranier Vilela



-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello Ranier,

All my tests were on latest 13.4 install I downloaded from the main site.

SELECT version();
PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit


As per the following:

> I think I'm unable to reproduce the issue, because I didn't use any plain stock installs.
> Postgres env tests here, is a fresh build with the latest msvc.
> I have no intention of repeating the issue, with something exactly the same as your environment,
> but with a very different environment.

I am not sure I understand. Are you saying the standard installs may be faulty?

Not exactly.

A stock install from the stock installer on a windows machine should take 10mn top. If it doesn't reproduce the issue out of the box, then at least I have a confirmation that there may be something weird that I am somehow repeating across all the installs I have performed???

Most likely it's something in your environment, along with your client.

All I can say is that it is unreproducible with a build/test made with the latest version of msvc.

Windows 10 64 bits.

msvc 2019 64 bits.

git clone --branch remote/origins/REL_13_4 https://github.com/postgres/postgres/ postgres_13_4

cd postgres_13_4

cd src

cd tools

cd msvc

build

install c:\postgres_bench

cd\postgres_bench\bin

initdb -D c:\postgres_bench\data -E UTF-8 -U postgres -W

pg_ctl -D c:\postgres_bench\data -l c:\postgres_bench\log\log1 start

psql -U postgres

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

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(a, null)) as "a" from sampletest;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1477.84..1477.85 rows=1 width=4) (actual time=830.404..830.404 rows=1 loops=1)
   Buffers: shared hit=646 read=1
   ->  Seq Scan on sampletest  (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.035..12.222 rows=100000 loops=1)
         Buffers: shared hit=637
 Planning:
   Buffers: shared hit=12 read=12
 Planning Time: 0.923 ms
 Execution Time: 830.743 ms
(8 rows)


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=123.660..123.660 rows=1 loops=1)
   Buffers: shared hit=637
   ->  Seq Scan on sampletest  (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.028..7.762 rows=100000 loops=1)
         Buffers: shared hit=637
 Planning Time: 0.152 ms
 Execution Time: 123.691 ms
(6 rows)

 regards,

Ranier Vilela