| From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
|---|---|
| To: | Sajith Prabhakar Shetty <ssajith(at)blackduck(dot)com> |
| Cc: | Andrei Lepikhov <lepihov(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Subject: | Re: Postgres: Queries are too slow after upgrading to PG17 from PG15 |
| Date: | 2025-07-17 15:05:01 |
| Message-ID: | CAH2-WzmBXPLn=j+nqwGtzoQiMzjY=otTt1irzb39+NkfnHMsnw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Thu, Jul 17, 2025 at 5:58 AM Sajith Prabhakar Shetty
<ssajith(at)blackduck(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Álvaro Herrera | 2025-07-17 15:48:27 | Re: BUG #18984: Empty prepared statement from psql \parse triggers assert in PortalRunMulti |
| Previous Message | Tom Lane | 2025-07-17 15:00:56 | Re: BUG #18984: Empty prepared statement from psql \parse triggers assert in PortalRunMulti |