Horton

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

Merlin Moncure <mmoncure@gmail.com>

View thread
From: Merlin Moncure <mmoncure@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Sajith Prabhakar Shetty <ssajith@blackduck.com>, 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-30T18:58:55Z
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.

On Mon, Jul 28, 2025 at 2:42 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Jul 28, 2025 at 2:20 AM Sajith Prabhakar Shetty
> <ssajith@blackduck.com> wrote:
> > We are able to get you a self-contained reproducer, please find attached dump, sql script and read me files.
>
> I find that your test case spends a great deal of time on nbtree
> preprocessing, which happens once per execution of the inner index
> scan on "zsf". According to "perf top", most cycles on spent on these:
>
>  32.02%  postgres                    [.] FunctionCall2Coll
>   22.01%  postgres                    [.] qsort_arg
>   18.64%  postgres                    [.] _bt_compare_array_elements
>    8.20%  postgres                    [.] btint8cmp
>    3.97%  postgres                    [.] _bt_preprocess_keys
>   ...
>
> The query takes ~1550ms on my local workstation. If I just comment out
> the relevant qsort, it'll take only ~190 ms. That qsort might not be

side question: if 50% of time (per perf top) is spent in qsort and
subroutines, how come query time goes down ~85%? is this a limitation
of perf or some other issue?

side question #2: 32% time spent on FunctionCall2Coll seems like a lot
-- is this inclusive of the routine under the function pointer?

just curious on both of these

merlin