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

   >  -----Original Message-----
   >  From: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
   >  Sent: Saturday, August 21, 2021 19:02
   >  To: Justin Pryzby <pryzby@telsasoft.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
   > 
   > 
   > 
   >     >  -----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 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
   > 


And by the way, I reproduced this again on my personal laptop with a fresh clean base-line install of 13.4.

Systeminfo
-------------------
OS Name:                   Microsoft Windows 10 Pro
OS Version:                10.0.19043 N/A Build 19043
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation
OS Build Type:             Multiprocessor Free
Registered Owner:          Windows User
Registered Organization:
Product ID:                00330-50535-98614-AAOEM
Original Install Date:     2021-04-04, 09:50:59
System Boot Time:          2021-08-19, 10:18:03
System Manufacturer:       LENOVO
System Model:              20HRCTO1WW
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.
                           [01]: Intel64 Family 6 Model 142 Stepping 9 GenuineIntel ~801 Mhz
BIOS Version:              LENOVO N1MET64W (1.49 ), 2020-10-14
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:     16,219 MB
Available Physical Memory: 4,971 MB
Virtual Memory: Max Size:  32,603 MB
Virtual Memory: Available: 12,168 MB
Virtual Memory: In Use:    20,435 MB
Page File Location(s):     C:\pagefile.sys


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