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

On Thu, Sep 25, 2025 at 3:28 PM Todd Cook <cookt@blackduck.com> wrote:
> The tests I reported on are application-level load tests that last about
> 10 hours and generate ~4 million queries, so I don't have any of that
> per-query info.  We generate a lot of queries with "IN (constant_list)"
> expressions, so that seemed like a logical explanation, but maybe
> something else is going on too?

The Postgres 17 improvements to "IN (constant_list)" index scans
(which indirectly caused this regression) tend to make things much
better with workloads such as this. For example, one blog post
reported an across-the-board 30% increase in application throughput
after an upgrade to 17, which was tied back to that work:

https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scans

So, yes, it's plausible that an application that made heavy use of
queries with large IN() lists could be much faster now. For better or
worse, that's a reasonably common pattern with the "eager loading"
used by frameworks such as Ruby on Rails.

> Nothing really stands out from
> crawling through pg_stat_statements, so maybe it's a small effect that
> is multiplied by repetition?

Should be noted that the regression itself is definitely "a small
effect that is multiplied by repetition". This makes the problem
rather perverse (though it's not exactly unusual for a complaint about
a planner regression to be perverse, in one way or another).

> FWIW, except for large multi-join queries with "IN (constant_list)"
> expressions, nearly every individual query I run is as fast or faster in
> 17 than in 16.  The ones that are slower are ones that were previously
> munged around to get a specific plan; those that I've unmunged have
> so far been much faster on 17.

That's great news.

> It just occurred to me while typing this that I should go count joins to
> see if we're exceeding join_collapse_limit.  Could something have
> changed in 17 that would affect how such queries are planned?

It'll be hard (very hard) to establish any relationship between the
overall improvements you're seeing and any changes in the planner.
Anything is possible, but in general planner changes rarely make an
across-the-board difference to most of an application's queries. Plus
the workload itself is probably fairly varied and complicated.

I only suggested that the improvements to the B-Tree code were likely
relevant because you mentioned that your application had many queries
with "IN (constant_list)", often with larger arrays of constants
(hundreds, say). And because I've direct knowledge of across-the-board
speedups for applications with those kinds of queries (the blog post
is the best example of this).

--
Peter Geoghegan