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

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