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

Hi Peter,
I successfully applied your patch and ran tests using the same database and query that originally triggered this bug.
Please find below the EXPLAIN ANALYZE data for your review:
Database Server
Execution Time
Execution Plan
Notes
PostgreSQL 17.5 (Stable)
9–10 sec
https://explain.depesz.com/s/mCTp
Execution time remains consistent across runs.
PostgreSQL 16.9 (Stable)
1–2 sec
https://explain.depesz.com/s/mYpn
First run takes ~5 sec; subsequent runs stabilize at 1–2 sec.
PostgreSQL Master (with patch)
5–6 sec
https://explain.depesz.com/s/2JJZ
Execution time is consistent across runs.
While the patch improves performance compared to PG17, it still doesn't match the efficiency observed in PG16.9. Is there any scope for further optimization to bring it closer to PG16's performance levels?
Please let me know if you'd like me to run additional tests or provide more details.








Sajith P Shetty

Principal Engineer

Black Duck

M +91 9448389989<tel:+919448389989>| ssajith@blackduck.com<mailto:ssajith@blackduck.com>

[signature_778616162]



From: Merlin Moncure <mmoncure@gmail.com>
Date: Saturday, 9 August 2025 at 3:34 AM
To: Todd Cook <cookt@blackduck.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Tom Lane <tgl@sss.pgh.pa.us>, Sajith Prabhakar Shetty <ssajith@blackduck.com>, Andrei Lepikhov <lepihov@gmail.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15

On Thu, Aug 7, 2025 at 11:58 AM Todd Cook <cookt@blackduck.com> wrote:
>
> On 8/4/25, 4:12 PM, "Peter Geoghegan" <pg@bowt.ie <mailto:pg@bowt.ie>> wrote:
> > Todd, Sajith: it would be helpful if you could test this patch.
> > Possibly by using the original problematic query, rather than the
> > minimized version that you posted to the list. The patch won't bring
> > performance up to parity with Postgres 15, but it should be a great
> > deal faster. Note that the patch that I've posted will only apply
> > against the current master branch (I'll prepare patches for earlier
> > branches once I have some buy-in).
>
> I'm working with our performance testing team to rerun our load tests
> with the patch applied.  However, the current infrastructure doesn't
> support deploying custom PG builds, so it's unlikely we'll be able to
> provide results in time for the upcoming minor releases.
>
> Also, is it significant effort to produce a patch for PG 17?  Running the
> load tests against master would make it chancy to compare the results
> with the data we already have.

Looks like it, _bt_preprocess_array_keys was whacked around quite a
bit in master vs 17 as part of the skip scan work, and a couple of the
bits the patch relies on are not there.  I would also question the
point of that, since the patch vs master is much closer to what you
would ultimately be using as master is pretty close to the v18 release
(currently in beta, scheduled for sep/oct).

I think it is very likely going to address your issue, but
confirmation helps, and will make it more likely for this patch to be
pushed.   After that, you will have to decide to downgrade to 15 or
suffer the status. quo until 18 releases. Running a custom patched 17
is possible if a patch could be made to appear, but I do not recommend
it given where you are at in the release calendar.
merlin