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

On 7/30/25, 10:49 PM, "David Rowley" <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com>> wrote:
> On Thu, 31 Jul 2025 at 07:49, Todd Cook <cookt(at)blackduck(dot)com <mailto:cookt(at)blackduck(dot)com>> wrote:
>> I work at the same company as Sajith, but on a different product. The reproducer he
>> provided is just a sample; it's not the only problem. Load testing in my team shows
>> that PG 17 is about 4x slower than PG 15 across the board. It's bordering on unusable
>> for production deployments.
>>
>> Unfortunately, the load testing setup doesn't really help isolate individual, regressing
>> queries. However, I'm more than willing to help support any further investigation if
>> needed or helpful.

> Unfortunately, we can't really work with that much information. It's
> not like we have a list of things we know are slower in newer versions
> vs older versions. Changes generally go through a large amount of
> testing to help ensure these regressions don't happen, so if you
> report one, unless someone else beat you to it, there's a decent
> chance we didn't know about it. There's nothing we can really do to
> help you based on this much information. There's just no chance we'd
> have shipped PG17 if it was known to be x4 slower than some previous
> version.

Sorry, it was not my intent to cast aspersions on PG; I was simply trying
to indicate the scale of the problem we're facing. I've been using PG for
21+ years now, and it always has been, and still is, the most reliable
component in our software stack.

> You may be able to narrow down what's slower using pg_stat_statements.
> If you can, then use EXPLAIN and compare the plans. Did PG17 choose a
> different plan? Does EXPLAIN ANALYZE reveal any inaccurate statistics?
> Are both instances configured the same way?

We make liberal use of "IN (unsorted_constant_list)" in our queries, so a
regression there could easily explain all the slowdown we're seeing. However,
I will confirm that just to be certain.

Also FWIW, I've bisected the slowdown to this commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5bf748b86.
The reproducer that Sajith posted takes ~1.1 seconds when run on a build at
that commit (with the same index-only scan as 17.5), but only ~85ms on the
prior commit (with a filtered index scan like with 15).

-- todd