From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Carey <scott.carey@algonomy.com>
cc: Jeff Davis <pgsql@j-davis.com>, David Rowley <dgrowleyml@gmail.com>,
        pgsql-performance@lists.postgresql.org
Subject: Re: Significant performance issues with array_agg() + HashAggregate
 plans on Postgres 17
In-reply-to: 
 <CA+vubOGqg7f2E0_OW8EMruAObejnCS1gCgguT+cnwHG9bRxMDQ@mail.gmail.com>
References: 
 <CA+vubOGaanYaGGb98etJPeJCbN=RAawx652MdmJVRK8+BeEP0w@mail.gmail.com>
 <CAApHDvq5fMzhK7OwGOhOgqRF-wErGNsT75yie5LtobSir1HhnA@mail.gmail.com>
 <CA+vubOE6feEWh7XxoWqdPh634SUVjHKW7s9BgMD755wJTqQFPw@mail.gmail.com>
 <CA+vubOHgrdkm+3wgC0FPcStDeMS9ZQ_Du9vUXWjxN9MN9CO2uw@mail.gmail.com>
 <2764544.1774985169@sss.pgh.pa.us>
 <CA+vubOEbfm5+BxtuFGb_tTj3sSX1u0Y+=zHUxoF1Q9feGpRfNg@mail.gmail.com>
 <2897039.1775051081@sss.pgh.pa.us>
 <CA+vubOGqg7f2E0_OW8EMruAObejnCS1gCgguT+cnwHG9bRxMDQ@mail.gmail.com>
Comments: In-reply-to Scott Carey <scott.carey@algonomy.com>
	message dated "Thu, 02 Apr 2026 00:23:43 -0700"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3579197.1775151468.0@sss.pgh.pa.us>
Date: Thu, 02 Apr 2026 13:38:18 -0400
Message-ID: <3579239.1775151498@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3579239.1775151498%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3579197.1775151468.1@sss.pgh.pa.us>

Scott Carey <scott.carey@algonomy.com> writes:
> I have discovered the root cause.
> This database is old.  It pre-dates Postgres 8.4 which introduced
> array_agg.   Apparently, in some version prior to 8.4 array_agg was added
> as a user function, defined as below for bigint:

>  create AGGREGATE array_agg(
>       BASETYPE = bigint,
>       SFUNC = array_append,
>       STYPE = bigint[],
>       INITCOND = '{}'
>  );

> So if you create a test database and run the previous test, performance
> will be fine and the query will be fast.  Then run:
> create AGGREGATE array_agg(BASETYPE = bigint, SFUNC = array_append,STYPE =
> bigint[], INITCOND = '{}');

> It will be slow and reproduce this behavior.

Thank you for running that to ground!  I confirm your results that v13
and up are far slower for this example than v12 was.

> Why would this run so much more slowly after updating from postgres 12 to
> 17?   It is a user defined aggregate, although maybe not as optimized as
> the intrinsic one it shouldn't behave this way.

I did some bisecting using the attached simplified test case, and found
that the query execution time jumps from circa 60ms to circa 7500ms here:

1f39bce021540fde00990af55b4432c55ef4b3c7 is the first bad commit
commit 1f39bce021540fde00990af55b4432c55ef4b3c7
Author: Jeff Davis <jdavis@postgresql.org>
Date:   Wed Mar 18 15:42:02 2020 -0700

    Disk-based Hash Aggregation.

    While performing hash aggregation, track memory usage when adding new
    groups to a hash table. If the memory usage exceeds work_mem, enter
    "spill mode".

(Times quoted are on a Mac M4 Pro, but in assert-enabled builds so
maybe not directly comparable to production.)

I'm bemused as to why: the test case has work_mem set high enough that
we shouldn't be triggering spill mode, so why did this change affect
it at all?

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="hashaggperformance.sql"; charset="us-ascii"
Content-ID: <3579197.1775151468.2@sss.pgh.pa.us>
Content-Description: hashaggperformance.sql

CREATE AGGREGATE array_agg(
      BASETYPE = bigint,
      SFUNC = array_append,
      STYPE = bigint[],
      INITCOND = '{}'
);

drop table if exists array_agg_test;

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;

\set ECHO all

-- set hash_mem_multiplier = 2;
set work_mem = "200MB";

explain (analyze, buffers) select product_id, array_agg(region_id) from
array_agg_test group by product_id;

------- =_aaaaaaaaaa0--