Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17

On Tue, Mar 31, 2026 at 10:55 AM Scott Carey <scott.carey@algonomy.com>
wrote:

>
> On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
>>
>> I tried and failed to recreate this locally on 17.9. For me the
>> json_agg query is slower than array_agg(). I tried making the table
>> 10x bigger and still don't see the same issue. The one with more
>> work_mem and fewer batches is always faster for me.
>
>
> I don't know what other differences there could be, other than OS.  This
> reproduces for me on Linux with the above on a RHEL 9 clone (pg 17) or with
> Ubuntu 25.10 (pg 16) so I suspect it is not too picky about the distro used.
>
> -Scott
>

I thought of two other possible differences:  extensions and JIT:

                                             List of installed extensions
       Name        | Version | Default version |   Schema   |
                       Description
--------------------+---------+-----------------+------------+-----------------------------------------------------------
pg_stat_statements | 1.11    | 1.11            | public     | track
execution statistics of all SQL statements executed
plpgsql            | 1.0     | 1.0             | pg_catalog | PL/pgSQL
procedural language
vector             | 0.8.2   | 0.8.2           | public     | vector data
type and ivfflat and hnsw access methods

show jit;
jit
-----
on

select pg_jit_available();
pg_jit_available
------------------
t




>
>
>>
>> David
>>
>