Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 - Mailing list pgsql-performance

Problem Summary:

A simple aggregate using array_agg goes significantly faster and faster the smaller the (work_mem * hash_mem_multiplier), with the same simple query plan:  HashAggregate over a sequential scan.   Changing to a simple aggregate, such as max() does not have this behavior and is always fast.  Switching to another aggregate that grows in size for each element, such as json_agg or string_agg also does not have this behavior.  If I add an order by clause inside array_agg, performance significantly improves as it changes from a HashAggregate of a sequential scan to  a GroupAggregate over a sort over a sequential scan.  Something seems specifically broken with array_agg + HashAggregate.

These queries are anywhere from 10x to 1000x slower on Postgres 17.9 than they were on Postgres 12.19 on production data.  Some of our OLTP queries  have gone from minutes to 6 hours to complete.   I do not know if this happens on Postgres 18,  I can confirm it also happens on Postgres 16.8.  I do not know about 13 through 15.

Below is a simplified reproduction with a test table below:

show server_version;
server_version  
----------------
17.9

create table array_agg_test(product_id bigint not null, region_id bigint not null, available boolean not null);

insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[1,2,3,4])[floor(random()*4)+1] as region_id, true as available;   
insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[11,12,13,14])[floor(random()*4)+1] as region_id, true as available;   
insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[111,112,113,114])[floor(random()*4)+1] as region_id, true as available;   
insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[1111,1112,1113,1114])[floor(random()*4)+1] as region_id, true as available;   
vacuum analyze array_agg_test;

We now have a table with 200000 rows, 50000 distinct product_id with 4 rows each, with a distinct region_id.   It is simple enough that default statistics are fine here; we want to trigger HashAgg over SeqScan anyway.

Note the performance is a function of hash_mem_multiplier * work_mem, as it seems to be related to the number of Batches.  The more batches the faster it goes.

Below, note json_agg does not have this problem:

set hash_mem_multiplier = 2;
set work_mem = "500MB";

explain (analyze, buffers) select product_id, json_agg(region_id) from array_agg_test group by product_id;
                                                        QUERY PLAN                                                           
-----------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=4274.00..4895.86 rows=49749 width=40) (actual time=110.975..122.781 rows=50000 loops=1)
  Group Key: product_id
  Batches: 1  Memory Usage: 67089kB
  Buffers: shared read=1274
  ->  Seq Scan on array_agg_test  (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.034..17.659 rows=200000 loops=1)
        Buffers: shared read=1274
Planning:
  Buffers: shared hit=10
Planning Time: 0.054 ms
Execution Time: 124.929 ms

and adding a useless 'order by' clause inside array_agg triggers a GroupAggregate which is ok as well:

explain (analyze, buffers) select product_id, array_agg(region_id order by available) from array_agg_test group by product_id;
                                                           QUERY PLAN                                                              
-----------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=20883.64..22881.13 rows=49749 width=40) (actual time=40.013..73.385 rows=50000 loops=1)
  Group Key: product_id
  Buffers: shared hit=5 read=1274
  ->  Sort  (cost=20883.64..21383.64 rows=200000 width=17) (actual time=40.000..46.090 rows=200000 loops=1)
        Sort Key: product_id, available
        Sort Method: quicksort  Memory: 13957kB
        Buffers: shared hit=5 read=1274
        ->  Seq Scan on array_agg_test  (cost=0.00..3274.00 rows=200000 width=17) (actual time=0.033..17.911 rows=200000 loops=1)
              Buffers: shared read=1274
Planning:
  Buffers: shared hit=7
Planning Time: 0.063 ms
Execution Time: 74.823 ms

I am fairly stuck here.  I am looking at modifying client code to use json_agg instead of array_agg where possible as a work-around, but ideally that would not be needed, array_agg shouldn't be significantly different.

A secondary observation, related but not the issue at hand:

The row size estimate for the aggregate is always `width=40` here, no matter how large the resulting arrays are expected to be. In extreme cases this can lead to hash memory consumption that is far larger than predicted.  On postgres 12 a several years ago, I once saw a query with work_mem 1000MB use up 290GB and crash the server as it was running a complex json_agg across a large number of values per bucket and the query planner did not expect to store json data so large per output row of the aggregate.  Disk backed aggregates now prevent the crash, but it would probably help the query planner if array_agg (and other accumulating aggregators like json_agg) could provide an output size estimate that is a function of the number of expected elements aggregated over.

Thanks in advance for any help here!

Scott Carey