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

Sajith Prabhakar Shetty <ssajith@blackduck.com>

From: Sajith Prabhakar Shetty <ssajith@blackduck.com>

To: Peter Geoghegan <pg@bowt.ie>

Cc: Andrei Lepikhov <lepihov@gmail.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>

Date: 2025-07-28T06:20:13Z

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 →

Convert strategies to and from compare types
- c09e5a6a0165 18.0 cited
Enhance nbtree ScalarArrayOp execution.
- 5bf748b86bc6 17.0 cited
Improve planning of btree index scans using ScalarArrayOpExpr quals.
- a4523c5aa534 9.5.0 cited
Fix planning of btree index scans using ScalarArrayOpExpr quals.
- 807a40c551dd 9.3.0 cited

Attachments

simple_reproducer_setup_sql.pgsql (application/octet-stream)
simple_reproducer.dump (application/octet-stream)
simple_reproducer_message_readme.txt (text/plain)

Hello,

We are able to get you a self-contained reproducer, please find attached dump, sql script and read me files.
Sorry for the delay, since our initial reproducer from the product was too large and sensitive to share.

This can clearly demonstrate performance degradation from postgres 17 versus postrges 15.

NOTE: This degradation has blocked our PG upgrade on multiple products in our portfolio.

Thanks.

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: Thursday, 17 July 2025 at 9:25 PM
To: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
On Thu, Jul 17, 2025 at 11:49 AM Sajith Prabhakar Shetty
<ssajith@blackduck.com> wrote:
> Thanks for the response, but I don’t understand when you meant “you are using different index”, by any chance did you mean the optimizer?
> Because I have used exactly the same data dump for all PG15,16 and 17 for my tests with no difference in data nor schema structure.

I simply mean that the plan is substantially different, in that there
is an index scan node on 17 that uses a completely different index to
the corresponding index scan node on 15. While the plan looks almost
the same, this one detail is huge.

In other words, I disagree with your summary of the plan, when you
said "Explain plan of the two queries almost same, all the joins and
paths used are exactly same". The paths are not the same.

--
Peter Geoghegan