Date: Fri, 16 Dec 2022 10:06:55 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?iso-8859-1?Q?Jo=E3o_Paulo_Lu=EDs?= <joao.luis@pdmfc.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres12 looking for possible HashAggregate issue workarounds?
Message-ID: <20221216160655.GQ1153@telsasoft.com>
References: 
 <21468a50-0684-4482-8f4d-d01b1f08021b.4e1a2d27-b57e-4ab1-b752-eaba6c32bb5b.359beaa8-9ef0-413f-a9b8-a521522bf026@emailsignatures365.codetwo.com>
 <AM4PR08MB2915B7B8C13B7A2BB553BF49EFE69@AM4PR08MB2915.eurprd08.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <AM4PR08MB2915B7B8C13B7A2BB553BF49EFE69@AM4PR08MB2915.eurprd08.prod.outlook.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20221216160655.GQ1153%40telsasoft.com>
Precedence: bulk

On Fri, Dec 16, 2022 at 03:24:17PM +0000, João Paulo Luís wrote:
> Hi! Sorry to post to this mailing list, but I could not find many tips working around HashAggregate issues.
> 
> In a research project involving text repetition analysis (on top of public documents)
> I have a VirtualMachine (CPU AMD Epyc 7502P, 128GB RAM, 12TB HDD, 2TB SSD),
> running postgres 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1)
> and some tables with many rows:

> 1 - the query is making a postgresql project have 76.7 GB resident RAM usage.
> Having a WORK_MEM setting of 2GB (and "simple" COUNT() results),
> that was not expected.
> (I risk oom-killer killing my postgres as soon as I run another concurrent
> query.)

> The rows=261275 on HashAggregate  (cost=26397219.92..26399832.67 rows=261275 width=8) seems VERY WRONG!
> I was expecting something like rows=1.0E+09 instead.

> I would guess that HashAggregate is behaving very badly (using to much RAM beyond WORK_MEM, amd also badly estimating the #rows and taking forever...)

Huge memory use sounds like what was fixed in postgres 13.

https://www.postgresql.org/docs/13/release-13.html

Allow hash aggregation to use disk storage for large aggregation result
sets (Jeff Davis)

Previously, hash aggregation was avoided if it was expected to use more
than work_mem memory. Now, a hash aggregation plan can be chosen despite
that. The hash table will be spilled to disk if it exceeds work_mem
times hash_mem_multiplier.

This behavior is normally preferable to the old behavior, in which once
hash aggregation had been chosen, the hash table would be kept in memory
no matter how large it got — which could be very large if the planner
had misestimated. If necessary, behavior similar to that can be obtained
by increasing hash_mem_multiplier.

-- 
Justin