From: marcos sicat <marcos.sicat@atlasifs.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Date: Wed, 30 Apr 2025 21:43:43 +0000
Message-ID: <MW5PR84MB2227FC185274F5C09423D596F2832@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAFj8pRAfVktAp6u26+JD_gnD+LcbCYfKrxxLtCMDP13QEVoGBA@mail.gmail.com>
References: <MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
<CAFj8pRBygw-a5+LdeWSW=8N091=i2Tgmx=73ORyTv6ksT3Zw5w@mail.gmail.com>
<MW5PR84MB2227BFE47602F2AAED04A0B3F2812@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
<775999.1745848757@sss.pgh.pa.us>
<MW5PR84MB22279A9C3D3990FAC0806679F2812@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
<CAFj8pRAfVktAp6u26+JD_gnD+LcbCYfKrxxLtCMDP13QEVoGBA@mail.gmail.com>
I was able to capture the Qplans for both, and I highlighted the differences in red. Are there server settings differences between v17 and v15 by default?
What would be the suggested configuration settings in v17 that would behave like v15 and match the performance with v15?
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Tuesday, April 29, 2025 at 1:01 AM
To: marcos sicat <marcos.sicat@atlasifs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Hi
po 28. 4. 2025 v 23:53 odesílatel marcos sicat <marcos.sicat@atlasifs.com<mailto:marcos.sicat@atlasifs.com>> napsal:
Thanks, Tom.
After you made your recommendation, the result returned much quicker at 2.62 seconds, but v15 is still faster at 1.82 seconds. No modification was made to the function.
and you look at the log file and separate query plans from there.
proposed changes just force storing query plans to the log file.
From plans we can see what is different
Regards
Pavel
[image.png]
From: Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>
Date: Monday, April 28, 2025 at 9:59 AM
To: marcos sicat <marcos.sicat@atlasifs.com<mailto:marcos.sicat@atlasifs.com>>
Cc: Pavel Stehule <pavel.stehule@gmail.com<mailto:pavel.stehule@gmail.com>>, pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org> <pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
marcos sicat <marcos.sicat@atlasifs.com<mailto:marcos.sicat@atlasifs.com>> writes:
> The function is the same between v15 and v17. Is there a subtle difference in performance for nested subqueries in v17?
Your next step should be to compare the plans for the function's
query. The auto_explain or pg_stat_statements extensions could
be used to check that in-situ, if manually EXPLAINing that query
doesn't yield insight.
regards, tom lane
Attachments:
[image/png] image.png (199.1K, ../MW5PR84MB2227FC185274F5C09423D596F2832@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/3-image.png)
download | view image
[application/vnd.openxmlformats-officedocument.spreadsheetml.sheet] postgres_v17_qplan_v15_qplan.xlsx (13.4K, ../MW5PR84MB2227FC185274F5C09423D596F2832@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/4-postgres_v17_qplan_v15_qplan.xlsx)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marcos.sicat@atlasifs.com, pavel.stehule@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
In-Reply-To: <MW5PR84MB2227FC185274F5C09423D596F2832@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox