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

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(at)blackduck(dot)com<mailto:ssajith(at)blackduck(dot)com>
[signature_778616162]

From: Peter Geoghegan <pg(at)bowt(dot)ie>
Date: Thursday, 17 July 2025 at 9:25 PM
To: Sajith Prabhakar Shetty <ssajith(at)blackduck(dot)com>
Cc: Andrei Lepikhov <lepihov(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)blackduck(dot)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