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

Hi Andrei,

Thank you and sorry for the delay in response.

We have found the slowness in multiple other products in our portfolio and we are blocked to proceed further.

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

I found out something which might be linked to, in release notes of PG17 (http://postgresql.org/docs/release/17.0/)

“ Allow btree<https://www.postgresql.org/docs/17/btree.html> indexes to more efficiently find a set of values, such as those supplied by IN clauses using constants.”

@Tom Lane<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>, Sorry I could not get you reproducer yet but please do comment on this insight meanwhile I am working on it.

Thanks.

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

From: Andrei Lepikhov <lepihov(at)gmail(dot)com>
Date: Thursday, 15 May 2025 at 7:56 PM
To: Sajith Prabhakar Shetty <ssajith(at)blackduck(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
On 15/5/2025 07:33, Sajith Prabhakar Shetty wrote:
> Hi,
>
>
> Most of the queries got slower after upgrading our postgres from version
> 15 to 17 using pg_upgrade. I reconfirmed that "vacuum full, analyze"
> were all taken care.
>
> To debug, instead of upgrade, I installed two instances one with
> postgres 15 and another postgres 17 with the same application dump restored.
>
> Now surprisingly one of the query i took from application which used to
> execute in 2s in PG15, is now taking 1min+ in PG17. I also observed that
> some of the operations involving DML operations slowed down too in PG17.
>
> Explain plan of the two queries almost same, all the joins and paths
> used are exactly same.
>
> Could anybody please provide some insights here?
Curious, the difference in Index Only Scan node:

-> Index Scan using stream_file_pkey on stream_file sf
(cost=1.63..1.86 rows=1 width=8)
(actual time=0.006..0.006 rows=1 loops=598916)
Index Cond: (id = sdo.stream_file_id)
Filter: (component_id = ANY

-> Index Only Scan using ui_stream_file_id_component on stream_file sf
(cost=0.43..0.51 rows=1 width=8)
(actual time=0.014..0.014 rows=1 loops=598916)
Index Cond: ((id = sdo.stream_file_id) AND (component_id = ANY

Each time the index scan is 2.5 times slower on PG17. But:

PG 15:
Buffers: shared hit=2338397 read=57267
I/O Timings: shared read=3384.286

PG 17:
Buffers: shared hit=1909772 read=9933
I/O Timings: shared read=686.506

If I'm not mistaken, it seems like an insight.

--
regards, Andrei Lepikhov