Re: [GENERAL] How does Postgres estimate the memory needed forsorting/aggregating

On 01/25/2017 10:47 PM, John R Pierce wrote:
> On 1/25/2017 12:59 PM, Thomas Kellerer wrote:
>> So here is my question: how does Postgres estimate/know the memory
>> needed for the aggregation? Or does it dynamically resize the memory
>> if the initial assumption was wrong?
>
> my understanding is it fits as much as it can into a work_mem sized
> allocation, and if thats not enough uses temporary files and multiple
> passes.
>

That only works for hash joins, not for hash aggregates. Hash aggregate
is about the only operation in PostgreSQL that can cause OOM because of
under-estimation.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services