Re: BUG #16223: Performance regression between 11.6 and 12.1 in an SQL query with a recursive CTE based on function

On 27.01.20 16:31, Tom Lane wrote:
> Christian Schwaderer <schwaderer(at)ivocotec(dot)de> writes:
>> I only tested on those virtual environments mentioned (Virtual Box and
>> Docker) as they resemble my real life scenarios. Environments were
>> equivalent except for the PostgreSQL version. As you can see, I used
>> Docker images from the official PostgreSQL docker repo (
>> https://hub.docker.com/_/postgres ) and did nothing with them except
>> running the query.
>> Thanks for testing without virtualization! So, it seems we can narrow
>> down the issue to virtual environments. Strange enough, if you ask me.
> Or it could be the specific builds you used. Some of our packagers
> tend to enable debug options on early releases of a given major version;
> if that was the case for the v12 build you tested, it could account for
> at least some of the difference. Comparing pg_config output from the
> two installations (particularly the configure options) would be
> informative.
>
> Another thing that could be relevant is JIT, which is on by default
> in v12 (if enabled in configuration), and which is still suffering
> a lot of teething pains performance-wise. I'd check if that's
> on and try disabling it if so.
>
> regards, tom lane
>

Sorry for replying so late, I was busy with something else.

As I already pointed out, I'm able to reproduce this issue on images
from the official PostgreSQL docker repo on two different versions (12.0
and 12.1). I did not change anything in the config, just pulling the
images, executing the query and that's it. So, if actually there is
debugging code in both those builds or the default config is somewhat
problematic for this query, I think, this would be actually an issue, a
minor one of course.

Anyway: Here are the details of versions and pg_config

I) Postgres 12.1 on a virtual Ubuntu 18.04 running in VirtualBox
Output of SELECT version();
PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Output of SELECT pg_config();
  (BINDIR,/usr/lib/postgresql/12/bin)
 (DOCDIR,/usr/share/doc/postgresql-doc-12)
 (HTMLDIR,/usr/share/doc/postgresql-doc-12)
 (INCLUDEDIR,/usr/include/postgresql)
 (PKGINCLUDEDIR,/usr/include/postgresql)
 (INCLUDEDIR-SERVER,/usr/include/postgresql/12/server)
 (LIBDIR,/usr/lib/x86_64-linux-gnu)
 (PKGLIBDIR,/usr/lib/postgresql/12/lib)
 (LOCALEDIR,/usr/share/locale)
 (MANDIR,/usr/share/postgresql/12/man)
 (SHAREDIR,/usr/share/postgresql/12)
 (SYSCONFDIR,/etc/postgresql-common)
 (PGXS,/usr/lib/postgresql/12/lib/pgxs/src/makefiles/pgxs.mk)
 (CONFIGURE,"'--build=x86_64-linux-gnu' '--prefix=/usr'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu'
'--libexecdir=/usr/lib/x86_64-linux-gnu' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml'
'--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/12/man'
'--docdir=/usr/share/doc/postgresql-doc-12'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/12'
'--bindir=/usr/lib/postgresql/12/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu
12.1-1.pgdg18.04+1)' '--enable-nls' '--enable-integer-datetimes'
'--enable-thread-safety' '--enable-tap-tests' '--enable-debug'
'--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo'
'--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-6.0'
'CLANG=/usr/bin/clang-6.0' '--with-systemd' '--with-selinux'
'MKDIR_P=/bin/mkdir -p' 'TAR=/bin/tar' 'CFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro
-Wl,-z,now' '--with-gssapi' '--with-ldap'
'--with-includes=/usr/include/mit-krb5' '--with-libs=/usr/lib/mit-krb5'
'--with-libs=/usr/lib/x86_64-linux-gnu/mit-krb5'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time
-D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security'")
 (CC,gcc)
 (CPPFLAGS,"-Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/mit-krb5")
 (CFLAGS,"-Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
-fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2
-fstack-protector-strong -Wformat -Werror=format-security
-fno-omit-frame-pointer")
 (CFLAGS_SL,-fPIC)
 (LDFLAGS,"-Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now
-L/usr/lib/llvm-6.0/lib -L/usr/lib/x86_64-linux-gnu/mit-krb5
-Wl,--as-needed")
 (LDFLAGS_EX,"")
 (LDFLAGS_SL,"")
 (LIBS,"-lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam
-lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm ")
 (VERSION,"PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1)")

II) Postgres 12.1 from official PostgreSQL docker repo

Output of SELECT version();
PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Output of SELECT pg_config();
 (BINDIR,/usr/lib/postgresql/12/bin)
 (DOCDIR,/usr/share/doc/postgresql-doc-12)
 (HTMLDIR,/usr/share/doc/postgresql-doc-12)
 (INCLUDEDIR,/usr/include/postgresql)
 (PKGINCLUDEDIR,/usr/include/postgresql)
 (INCLUDEDIR-SERVER,/usr/include/postgresql/12/server)
 (LIBDIR,/usr/lib/x86_64-linux-gnu)
 (PKGLIBDIR,/usr/lib/postgresql/12/lib)
 (LOCALEDIR,/usr/share/locale)
 (MANDIR,/usr/share/postgresql/12/man)
 (SHAREDIR,/usr/share/postgresql/12)
 (SYSCONFDIR,/etc/postgresql-common)
 (PGXS,/usr/lib/postgresql/12/lib/pgxs/src/makefiles/pgxs.mk)
 (CONFIGURE,"'--build=x86_64-linux-gnu' '--prefix=/usr'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu'
'--libexecdir=/usr/lib/x86_64-linux-gnu' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml'
'--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/12/man'
'--docdir=/usr/share/doc/postgresql-doc-12'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/12'
'--bindir=/usr/lib/postgresql/12/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian
12.1-1.pgdg100+1)' '--enable-nls' '--enable-integer-datetimes'
'--enable-thread-safety' '--enable-tap-tests' '--enable-debug'
'--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo'
'--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-7'
'CLANG=/usr/bin/clang-7' '--with-systemd' '--with-selinux'
'MKDIR_P=/bin/mkdir -p' 'TAR=/bin/tar' 'CFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now'
'--with-gssapi' '--with-ldap' '--with-includes=/usr/include/mit-krb5'
'--with-libs=/usr/lib/mit-krb5'
'--with-libs=/usr/lib/x86_64-linux-gnu/mit-krb5'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time
-D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security'")
 (CC,gcc)
 (CPPFLAGS,"-Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/mit-krb5")
 (CFLAGS,"-Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
-fwrapv -fexcess-precision=standard -Wno-format-truncation
-Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat
-Werror=format-security -fno-omit-frame-pointer")
 (CFLAGS_SL,-fPIC)
 (LDFLAGS,"-Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-7/lib
-L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed")
 (LDFLAGS_EX,"")
 (LDFLAGS_SL,"")
 (LIBS,"-lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam
-lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm ")
 (VERSION,"PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1)")

III) Postgres 12.0 from official PostgreSQL docker repo
Output of SELECT version();
PostgreSQL 12.0 (Debian 12.0-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Output of SELECT pg_config();
  (BINDIR,/usr/lib/postgresql/12/bin)
 (DOCDIR,/usr/share/doc/postgresql-doc-12)
 (HTMLDIR,/usr/share/doc/postgresql-doc-12)
 (INCLUDEDIR,/usr/include/postgresql)
 (PKGINCLUDEDIR,/usr/include/postgresql)
 (INCLUDEDIR-SERVER,/usr/include/postgresql/12/server)
 (LIBDIR,/usr/lib/x86_64-linux-gnu)
 (PKGLIBDIR,/usr/lib/postgresql/12/lib)
 (LOCALEDIR,/usr/share/locale)
 (MANDIR,/usr/share/postgresql/12/man)
 (SHAREDIR,/usr/share/postgresql/12)
 (SYSCONFDIR,/etc/postgresql-common)
 (PGXS,/usr/lib/postgresql/12/lib/pgxs/src/makefiles/pgxs.mk)
 (CONFIGURE,"'--build=x86_64-linux-gnu' '--prefix=/usr'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu'
'--libexecdir=/usr/lib/x86_64-linux-gnu' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml'
'--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/12/man'
'--docdir=/usr/share/doc/postgresql-doc-12'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/12'
'--bindir=/usr/lib/postgresql/12/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian
12.0-2.pgdg100+1)' '--enable-nls' '--enable-integer-datetimes'
'--enable-thread-safety' '--enable-tap-tests' '--enable-debug'
'--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo'
'--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-7'
'CLANG=/usr/bin/clang-7' '--with-systemd' '--with-selinux'
'MKDIR_P=/bin/mkdir -p' 'TAR=/bin/tar' 'CFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now'
'--with-gssapi' '--with-ldap' '--with-includes=/usr/include/mit-krb5'
'--with-libs=/usr/lib/mit-krb5'
'--with-libs=/usr/lib/x86_64-linux-gnu/mit-krb5'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time
-D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security'")
 (CC,gcc)
 (CPPFLAGS,"-Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/mit-krb5")
 (CFLAGS,"-Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
-fwrapv -fexcess-precision=standard -Wno-format-truncation
-Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat
-Werror=format-security -fno-omit-frame-pointer")
 (CFLAGS_SL,-fPIC)
 (LDFLAGS,"-Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-7/lib
-L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed")
 (LDFLAGS_EX,"")
 (LDFLAGS_SL,"")
 (LIBS,"-lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam
-lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm ")
 (VERSION,"PostgreSQL 12.0 (Debian 12.0-2.pgdg100+1)")

 IV) For comparison: Output of Postgres 11.6 where everything seems
fine (also official PostgreSQL docker repo):
 Output of SELECT version();
 PostgreSQL 11.6 (Debian 11.6-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

 Output of SELECT pg_config();
  (BINDIR,/usr/lib/postgresql/11/bin)
 (DOCDIR,/usr/share/doc/postgresql-doc-11)
 (HTMLDIR,/usr/share/doc/postgresql-doc-11)
 (INCLUDEDIR,/usr/include/postgresql)
 (PKGINCLUDEDIR,/usr/include/postgresql)
 (INCLUDEDIR-SERVER,/usr/include/postgresql/11/server)
 (LIBDIR,/usr/lib/x86_64-linux-gnu)
 (PKGLIBDIR,/usr/lib/postgresql/11/lib)
 (LOCALEDIR,/usr/share/locale)
 (MANDIR,/usr/share/postgresql/11/man)
 (SHAREDIR,/usr/share/postgresql/11)
 (SYSCONFDIR,/etc/postgresql-common)
 (PGXS,/usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk)
 (CONFIGURE,"'--build=x86_64-linux-gnu' '--prefix=/usr'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu'
'--libexecdir=/usr/lib/x86_64-linux-gnu' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-icu' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml'
'--with-libxslt' 'PYTHON=/usr/bin/python3'
'--mandir=/usr/share/postgresql/11/man'
'--docdir=/usr/share/doc/postgresql-doc-11'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/11'
'--bindir=/usr/lib/postgresql/11/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/'
'--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian
11.6-1.pgdg90+1)' '--enable-nls' '--enable-integer-datetimes'
'--enable-thread-safety' '--enable-tap-tests' '--enable-debug'
'--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo'
'--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-6.0'
'CLANG=/usr/bin/clang-6.0' '--with-systemd' '--with-selinux'
'MKDIR_P=/bin/mkdir -p' 'TAR=/bin/tar' 'CFLAGS=-g -O2
-fstack-protector-strong -Wformat -Werror=format-security
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now'
'--with-gssapi' '--with-ldap' '--with-includes=/usr/include/mit-krb5'
'--with-libs=/usr/lib/mit-krb5'
'--with-libs=/usr/lib/x86_64-linux-gnu/mit-krb5'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time
-D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat
-Werror=format-security'")
 (CC,gcc)
 (CPPFLAGS,"-Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/mit-krb5")
 (CFLAGS,"-Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv
-fexcess-precision=standard -g -g -O2 -fstack-protector-strong -Wformat
-Werror=format-security -fno-omit-frame-pointer")
 (CFLAGS_SL,-fPIC)
 (LDFLAGS,"-Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-6.0/lib
-L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed")
 (LDFLAGS_EX,"")
 (LDFLAGS_SL,"")
 (LIBS,"-lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam
-lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm ")
 (VERSION,"PostgreSQL 11.6 (Debian 11.6-1.pgdg90+1)"

Hope this helps.

Best,
Christian

--
Christian Schwaderer, Software-Entwickler/software developer
------------------------------------------------------------
ivocoTec GmbH
Entwicklungsabteilung/IT department

Postplatz 3
D-16761 Hennigsdorf

https://ivocotec.de
Telefon/phone +49 (0)3302 20 63 230