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

> -----Original Message-----
> From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
> Sent: Saturday, August 21, 2021 18:17
> To: ldh(at)laurent-hasson(dot)com
> Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-performance(at)postgresql(dot)org
> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2
> and 13.4
>
> Could you send SELECT * FROM pg_config() and try to find the CPU
> model ?
>
> I think it's possible the hypervisor is trapping and emulating unhandled
> CPU instructions.
>
> Actually, it would be interesting to see if the performance differs
> between
> 11.2 and 11.13. It's possible that EDB compiled 11.13 on a newer CPU
> (or a newer compiler) than 11.2 was compiled.
>
> If you test that, it should be on a separate VM, unless the existing data
> dir can be restored from backup. Once you've started a cluster with
> updated binaries, you should avoid downgrading the binaries.

Hello all,

OK, I was able to do a clean install of 13.4 on the VM. All stock settings, no extensions loaded, pure clean straight out of the install.

create table sampletest (a varchar, b varchar);
-- truncate table sampletest;
insert into sampletest (a, b)
select substr(md5(random()::text), 0, 15), (100000000*random())::integer::varchar
from generate_series(1,1000000);

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;

explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as "b" from sampletest

Aggregate (cost=21370.00..21370.01 rows=1 width=4) (actual time=1780.561..1780.563 rows=1 loops=1)
Buffers: shared hit=6387
-> Seq Scan on sampletest (cost=0.00..16370.00 rows=1000000 width=8) (actual time=0.053..97.329 rows=1000000 loops=1)
Buffers: shared hit=6370
Planning:
Buffers: shared hit=36
Planning Time: 2.548 ms
Execution Time: 1,810.330 ms

explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" from sampletest

Aggregate (cost=21370.00..21370.01 rows=1 width=4) (actual time=863243.876..863243.877 rows=1 loops=1)
Buffers: shared hit=6373
-> Seq Scan on sampletest (cost=0.00..16370.00 rows=1000000 width=15) (actual time=0.009..301.553 rows=1000000 loops=1)
Buffers: shared hit=6370
Planning:
Buffers: shared hit=44
Planning Time: 0.469 ms
Execution Time: 863,243.911 ms

So I am still able to reproduce this on a different VM and a clean install of 13.4 ☹

SELECT * FROM pg_config();

BINDIR C:/PROGRA~1/POSTGR~1/13/bin
DOCDIR C:/PROGRA~1/POSTGR~1/13/doc
HTMLDIR C:/PROGRA~1/POSTGR~1/13/doc
INCLUDEDIR C:/PROGRA~1/POSTGR~1/13/include
PKGINCLUDEDIR C:/PROGRA~1/POSTGR~1/13/include
INCLUDEDIR-SERVER C:/PROGRA~1/POSTGR~1/13/include/server
LIBDIR C:/PROGRA~1/POSTGR~1/13/lib
PKGLIBDIR C:/PROGRA~1/POSTGR~1/13/lib
LOCALEDIR C:/PROGRA~1/POSTGR~1/13/share/locale
MANDIR C:/Program Files/PostgreSQL/13/man
SHAREDIR C:/PROGRA~1/POSTGR~1/13/share
SYSCONFDIR C:/Program Files/PostgreSQL/13/etc
PGXS C:/Program Files/PostgreSQL/13/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE --enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid --with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python
CC not recorded
CPPFLAGS not recorded
CFLAGS not recorded
CFLAGS_SL not recorded
LDFLAGS not recorded
LDFLAGS_EX not recorded
LDFLAGS_SL not recorded
LIBS not recorded
VERSION PostgreSQL 13.4

And here is SYSINFO:

C:\Users\LHASSON>systeminfo

Host Name: PRODDB
OS Name: Microsoft Windows Server 2012 R2 Standard
OS Version: 6.3.9600 N/A Build 9600
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
Original Install Date: 2015-09-19, 18:19:41
System Boot Time: 2021-07-22, 11:45:09
System Manufacturer: VMware, Inc.
System Model: VMware Virtual Platform
System Type: x64-based PC
Processor(s): 4 Processor(s) Installed.
[01]: Intel64 Family 6 Model 79 Stepping 1 GenuineIntel ~2397 Mhz
[02]: Intel64 Family 6 Model 79 Stepping 1 GenuineIntel ~2397 Mhz
[03]: Intel64 Family 6 Model 79 Stepping 1 GenuineIntel ~2397 Mhz
[04]: Intel64 Family 6 Model 79 Stepping 1 GenuineIntel ~2397 Mhz
BIOS Version: Phoenix Technologies LTD 6.00, 2020-05-28
Windows Directory: C:\Windows
System Directory: C:\Windows\system32
Boot Device: \Device\HarddiskVolume1
System Locale: en-us;English (United States)
Input Locale: en-us;English (United States)
Time Zone: (UTC-05:00) Eastern Time (US & Canada)
Total Physical Memory: 65,535 MB
Available Physical Memory: 57,791 MB
Virtual Memory: Max Size: 75,263 MB
Virtual Memory: Available: 66,956 MB
Virtual Memory: In Use: 8,307 MB
Page File Location(s): C:\pagefile.sys