From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Carey <scott.carey@algonomy.com>
cc: 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+vubOHgrdkm+3wgC0FPcStDeMS9ZQ_Du9vUXWjxN9MN9CO2uw@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>
Comments: In-reply-to Scott Carey <scott.carey@algonomy.com>
	message dated "Tue, 31 Mar 2026 11:06:36 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2764543.1774985169.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 31 Mar 2026 15:26:09 -0400
Message-ID: <2764544.1774985169@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2764544.1774985169%40sss.pgh.pa.us>
Precedence: bulk

Scott Carey <scott.carey@algonomy.com> writes:
>> On Tue, Mar 31, 2026 at 5:03=E2=80=AFAM 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.  Thi=
s
>> 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.

Like David, I can't reproduce the described behavior.  I tried on
RHEL8/x86_64 and on macOS/M4, and got runtimes that barely vary
across different work_mem settings, all sub-100ms.  It should be
noted that I tested v17 branch tip not precisely 17.9 --- but there's
nothing in the commit log to suggest that we changed v17's behavior
since February.

One thing I find interesting is that your results show significantly
more memory consumption as well as runtime.  I had to add a run with
work_mem =3D "200MB" to get the no-batching behavior you show at
work_mem =3D "100MB", and then my results look like

$ egrep 'Exec|Batches' v17.out
   Batches: 1  Memory Usage: 17937kB
 Execution Time: 62.494 ms
   Planned Partitions: 4  Batches: 5  Memory Usage: 9009kB  Disk Usage: 37=
44kB
 Execution Time: 80.044 ms
   Planned Partitions: 16  Batches: 17  Memory Usage: 2385kB  Disk Usage: =
7112kB
 Execution Time: 93.572 ms
   Planned Partitions: 32  Batches: 33  Memory Usage: 1393kB  Disk Usage: =
14088kB
 Execution Time: 97.021 ms
   Planned Partitions: 64  Batches: 65  Memory Usage: 1089kB  Disk Usage: =
12200kB
 Execution Time: 98.887 ms
 Execution Time: 120.179 ms
   Planned Partitions: 32  Batches: 33  Memory Usage: 1073kB  Disk Usage: =
14088kB
 Execution Time: 98.609 ms
   Batches: 1  Memory Usage: 67089kB
 Execution Time: 110.035 ms
 Execution Time: 82.040 ms

Your memory-usage numbers are integer multiples of mine.
That makes little sense either.

It seems like the planner is choosing the same plans for me as for
you, other than having a higher cutoff for when not to select
batching.  So this is an executor issue not a planner issue.

Some thoughts:

* Does it repro without the "vector" extension?  Seems unlikely that
that is related, but we're at the grasping-at-straws stage.

* More grasping at straws: is this stock community Postgres, or
some vendor's modification (eg RDS or Aurora)?

* It would be worth doing the EXPLAINs with the SETTINGS option,
just to make sure that there's not some non-default setting you
forgot to mention.

			regards, tom lane