Em dom., 29 de ago. de 2021 às 21:29, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu:
> > > From: Ranier Vilela <ranier...@gmail.com> > Sent: Sunday, August 29, 2021 14:20 > To: l...@laurent-hasson.com > Cc: Tom Lane <t...@sss.pgh.pa.us>; Andrew Dunstan <and...@dunslane.net>; > Justin Pryzby <pry...@telsasoft.com>; pgsql-performa...@postgresql.org > 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:l...@laurent-hasson.com > <mailto:l...@laurent-hasson.com> 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
