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

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

>
> On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml(at)gmail(dot)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
>>
>