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

   >  -----Original Message-----
   >  From: Justin Pryzby <pryzby@telsasoft.com>
   >  Sent: Saturday, August 21, 2021 18:17
   >  To: ldh@laurent-hasson.com
   >  Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@postgresql.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
outof 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