Sorry missed to mention that for each instance, five runs were executed, and the latest execution plan and timing metrics were recorded and shared.
From: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Date: Tuesday, 12 August 2025 at 10:13 AM
To: Peter Geoghegan <pg@bowt.ie>
Cc: Merlin Moncure <mmoncure@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Andrei Lepikhov <lepihov@gmail.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, Todd Cook <cookt@blackduck.com>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Hi Peter,
I can confirm with full certainty that both VACUUM and ANALYZE were executed on all three instances whose results I shared. Additionally, each instance was freshly created on same hardware and configured with the same postgresql.conf settings.
From: Peter Geoghegan <pg@bowt.ie>
Date: Tuesday, 12 August 2025 at 12:15 AM
To: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Andrei Lepikhov <lepihov@gmail.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, Todd Cook <cookt@blackduck.com>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
On Mon, Aug 11, 2025 at 10:01 AM Sajith Prabhakar Shetty
<ssajith@blackduck.com> wrote:
> While the patch improves performance compared to PG17, it still doesn't match the efficiency observed in PG16.9.
Postgres 17 shows "Buffers: shared hit=5071180 read=42049" for the
top-level scan node/the plan as a whole, while patched master shows
"Buffers: shared hit=5608415 read=103886 written=2". There are only
minor differences in each plan. It looks like these results aren't
representative.
I see lots of heap fetches for index-only scans on patched master.
That factor alone could easily make a huge difference. I suggest
running "VACUUM ANALYZE" on both setups to get more consistent
results. I also suggest using pg_prewarm and/or repeated execution to
make sure that the number of buffer misses/reads is kept to an
absolute minimum. You should also make sure to use exactly the same
settings for each test case -- ideally while using the same hardware
for both.
> Is there any scope for further optimization to bring it closer to PG16's performance levels?
Probably not. At least not if it must be backpatched to Postgres 17.
The patch needs to be reasonably non-invasive for that to happen.
--
Peter Geoghegan
