Horton

Re: Postgres: Queries are too slow after upgrading to PG17 from PG15

Sajith Prabhakar Shetty <ssajith@blackduck.com>

View thread
From: Sajith Prabhakar Shetty <ssajith@blackduck.com>
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>
Date: 2025-08-12T04:43:20Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Convert strategies to and from compare types

  2. Enhance nbtree ScalarArrayOp execution.

  3. Improve planning of btree index scans using ScalarArrayOpExpr quals.

  4. Fix planning of btree index scans using ScalarArrayOpExpr quals.

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.




Sajith P Shetty

Principal Engineer

Black Duck

M +91 9448389989<tel:+919448389989>| ssajith@blackduck.com<mailto:ssajith@blackduck.com>

[signature_778616162]



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