From: Justin Pryzby <pryzby@telsasoft.com>
To: João Paulo Luís <joao.luis@pdmfc.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres12 looking for possible HashAggregate issue workarounds?
Date: Fri, 16 Dec 2022 10:06:55 -0600
Message-ID: <20221216160655.GQ1153@telsasoft.com> (raw)
In-Reply-To: <AM4PR08MB2915B7B8C13B7A2BB553BF49EFE69@AM4PR08MB2915.eurprd08.prod.outlook.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, joao.luis@pdmfc.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres12 looking for possible HashAggregate issue workarounds?
In-Reply-To: <20221216160655.GQ1153@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox