Try
Home   >   mailing lists

Re: Postgres: Queries are too slow after upgrading to PG17 from PG15 - Mailing list pgsql-bugs

From Sajith Prabhakar Shetty
Subject Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Date
Msg-id DM4PR19MB6486B947E5F0D800127897F5B551A@DM4PR19MB6486.namprd19.prod.outlook.com
Whole thread Raw
In response to Re: Postgres: Queries are too slow after upgrading to PG17 from PG15  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
List pgsql-bugs
Tree view

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| 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

Attachment

pgsql-bugs by date:

Previous
From: Álvaro Herrera
Date:
Subject: Re: BUG #18984: Empty prepared statement from psql \parse triggers assert in PortalRunMulti
Next
From: Peter Geoghegan
Date:
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15