Horton

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

Sajith Prabhakar Shetty <ssajith@blackduck.com>

View thread
From: Sajith Prabhakar Shetty <ssajith@blackduck.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: 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-17T15:49:39Z
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.

Hi Peter,

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.



Sajith P Shetty
Principal Engineer
Black Duck
M +91 9448389989<tel:+919448389989>| ssajith@blackduck.com<mailto:ssajith@blackduck.com>
[signature_778616162]


From: Peter Geoghegan <pg@bowt.ie>
Date: Thursday, 17 July 2025 at 8:35 PM
To: Sajith Prabhakar Shetty <ssajith@blackduck.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
On Thu, Jul 17, 2025 at 5:58 AM Sajith Prabhakar Shetty
<ssajith@blackduck.com> wrote:
> In regards to your point below, it is true that the index only scan is 2.5 times slower on PG17 and  primary difference is in that index-only scan of ui_stream_file_id_component at line 14.  It takes 6 microseconds per row in PG 15, 2 microseconds per row in 16, and 14 microseconds in 17

The important difference is the choice of index for the outermost
nestloop join's inner index scan. A different index is used on
Postgres 17:

On Postgres 15, you're using the likely-single-column stream_file_pkey
index, which uses filter quals for the ScalarArrayOp/= ANY condition.
Whereas on Postgres 17, you're using the ui_stream_file_id_component
index instead (a multicolumn index), which uses a true index qual for
the "id = sdo.stream_file_id" as well as for the ScalarArrayOp/= ANY
condition.


--
Peter Geoghegan