Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w8Q8N-000Vxg-15 for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2026 22:03:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w8Q8K-008FFV-15 for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2026 22:03:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w8Q8K-008FFK-07 for pgsql-performance@lists.postgresql.org; Thu, 02 Apr 2026 22:03:24 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w8Q8D-00000000Fku-1zzZ for pgsql-performance@lists.postgresql.org; Thu, 02 Apr 2026 22:03:18 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 632M3DD73671093; Thu, 2 Apr 2026 18:03:14 -0400 From: Tom Lane To: Scott Carey cc: Jeff Davis , David Rowley , pgsql-performance@lists.postgresql.org Subject: Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 In-reply-to: References: <2764544.1774985169@sss.pgh.pa.us> <2897039.1775051081@sss.pgh.pa.us> <3579239.1775151498@sss.pgh.pa.us> Comments: In-reply-to Scott Carey message dated "Thu, 02 Apr 2026 12:08:13 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3671091.1775167393.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 02 Apr 2026 18:03:13 -0400 Message-ID: <3671092.1775167393@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Scott Carey writes: > On Thu, Apr 2, 2026, 10:38 Tom Lane wrote: >> I did some bisecting using the attached simplified test case, and found >> that the query execution time jumps from circa 60ms to circa 7500ms her= e: >> ... >> 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? > Even stranger, the more spills induced via smaller work_mem the faster i= t > goes. > This suggests something getting more expensive as the hash table gets > larger. Significantly more, like O(n^2) or worse. Yeah. I watched this query (at work_mem=3D200MB) with "perf", and I find that essentially all of the runtime is spent here: --96.39%--agg_fill_hash_table (inlined) | = --95.95%--lookup_hash_entries | = --95.77%--initialize_hash_entry (inlined) | = --95.72%--hash_agg_check_limits | = --95.72%--MemoryContextMem= Allocated | = --83.22%--Memor= yContextTraverseNext (inlined) | = = --3.= 97%--MemoryContextTraverseNext (inlined) Drilling down further, the step that is slow is hash_agg_check_limits's Size tval_mem =3D MemoryContextMemAllocated(aggstate->hashconte= xt->ecxt_per_tuple_memory, true); and a look at the memory context tree explains why: ExecutorState: 32768 total in 3 blocks; 15768 free (5 chunks); 17000= used ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 use= d ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 use= d HashAgg hashed tuples: 2097040 total in 9 blocks; 1045752 free; 10= 51288 used HashAgg meta context: 1056816 total in 2 blocks; 4328 free (0 chun= ks); 1052488 used ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 u= sed ExprContext: 8192 total in 1 blocks; 7952 free (1 chunks); 240 use= d expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576= used ... quite a few more ... 26174 more child contexts containing 26802176 total in 26174 blo= cks; 11725952 free (0 chunks); 15076224 used So the main problem here is we're leaking the arrays made by array_agg, and a secondary problem is that that drives the cost of hash_agg_check_limits to an unacceptable level. regards, tom lane