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

Em dom., 29 de ago. de 2021 às 21:29, ldh(at)laurent-hasson(dot)com <
ldh(at)laurent-hasson(dot)com> escreveu:

>
>
> From: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
> Sent: Sunday, August 29, 2021 14:20
> To: ldh(at)laurent-hasson(dot)com
> Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Andrew Dunstan <andrew(at)dunslane(dot)net>;
> Justin Pryzby <pryzby(at)telsasoft(dot)com>; pgsql-performance(at)postgresql(dot)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:ldh(at)laurent-hasson(dot)com
> <mailto:ldh(at)laurent-hasson(dot)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