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

From: tushar <tushar(dot)ahuja(at)enterprisedb(dot)com>
Sent: Monday, September 27, 2021 11:50
To: Andrew Dunstan <andrew(at)dunslane(dot)net>; ldh(at)laurent-hasson(dot)com; Julien Rouhaud <rjuju123(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>; 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

On 9/27/21 6:55 PM, Andrew Dunstan wrote:
Hello Andrew,

I just download the 13.4 Windows x86-64 installer from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads but it's the exact same file bit for bit from the previous version I had. Am I looking at the wrong place?

Thanks. We're dealing with that. However, you can update that version
via stackbuilder. It will show you that 13.4.2 is available. This has
the correct libintl DLL. I just did this to verify it.

Thanks, look like the issue is fixed now, you can try to download the 'postgresql-13.4-2-windows-x64.exe' installer from the above mentioned link.
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

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

Hello all!

WOW!!!! Time for a cigar as there is double good news 😊
- The scenario no longer exacerbates the system and performance went from around 90s to around 2.7 seconds! That's in line with older 11.2 builds I was measuring against.
- The simpler scenario (no throw) looks like it improved by roughly 20%, from 186ms to 146ms

I had run the scenarios multiple times before and the times were on the average, so I think those gains are real. Thank you for all your efforts. The Postgres community is amazing!

Here is the scenario again:

drop table sampletest;
create table sampletest (a varchar, b varchar);
insert into sampletest (a, b)
select substr(md5(random()::text), 0, 15), (100000000*random())::integer::varchar
from generate_series(1,100000);
CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
RETURNS real AS $$
BEGIN
RETURN case when str is null then val else str::real end;
EXCEPTION WHEN OTHERS THEN
RETURN val;
END;
$$ LANGUAGE plpgsql COST 1 IMMUTABLE;

This is what I had on the original 13.4 Windows x64 eDB build:

explain (analyze,buffers,COSTS,TIMING)
select MAX(toFloat(a, null)) as "a" from sampletest
--Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=89527.032..89527.033 rows=1 loops=1)
-- Buffers: shared hit=647
-- -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.024..37.811 rows=100000 loops=1)
-- Buffers: shared hit=637
--Planning:
-- Buffers: shared hit=24
--Planning Time: 0.347 ms
--Execution Time: 89527.501 ms

explain (analyze,buffers,COSTS,TIMING)
select MAX(toFloat(b, null)) as "b" from sampletest
--Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=186.605..186.606 rows=1 loops=1)
-- Buffers: shared hit=637
-- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=100000 width=8) (actual time=0.008..9.679 rows=100000 loops=1)
-- Buffers: shared hit=637
--Planning:
-- Buffers: shared hit=4
--Planning Time: 0.339 ms
--Execution Time: 186.641 ms

This is what I get on the new build

explain (analyze,buffers,COSTS,TIMING)
select MAX(toFloat(a, null)) as "a" from sampletest
--QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------|
--Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=2711.314..2711.315 rows=1 loops=1) |
-- Buffers: shared hit=637 |
-- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=100000 width=15) (actual time=0.009..12.557 rows=100000 loops=1)|
-- Buffers: shared hit=637 |
--Planning Time: 0.062 ms |
--Execution Time: 2711.336 ms |

explain (analyze,buffers,COSTS,TIMING)
select MAX(toFloat(b, null)) as "b" from sampletest
--QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------|
--Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=146.689..146.689 rows=1 loops=1) |
-- Buffers: shared hit=637 |
-- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=100000 width=8) (actual time=0.009..8.060 rows=100000 loops=1)|
-- Buffers: shared hit=637 |
--Planning Time: 0.060 ms |
--Execution Time: 146.709 ms |

Thank you,
Laurent.