Horton

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

Todd Cook <cookt@blackduck.com>

View thread
From: Todd Cook <cookt@blackduck.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Sajith Prabhakar Shetty <ssajith@blackduck.com>, 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-31T20:03:21Z
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.

On 7/30/25, 10:49 PM, "David Rowley" <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com>> wrote:
> On Thu, 31 Jul 2025 at 07:49, Todd Cook <cookt@blackduck.com <mailto:cookt@blackduck.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