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
