Subject: Re: Default setting for enable_hashagg_disk
To: Jeff Davis <pgsql@j-davis.com>, pgsql-hackers@postgresql.org
References: <9d9d1e1252a52ea1bad84ea40dbebfd54e672a0f.camel@j-davis.com>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <55a29622-b5c7-8cc5-3a49-0bf89d0bb92e@2ndquadrant.com>
Date: Mon, 13 Jul 2020 13:51:42 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <9d9d1e1252a52ea1bad84ea40dbebfd54e672a0f.camel@j-davis.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 2020-04-07 20:20, Jeff Davis wrote:
> Now that we have Disk-based Hash Aggregation, there are a lot more
> situations where the planner can choose HashAgg. The
> enable_hashagg_disk GUC, if set to true, chooses HashAgg based on
> costing. If false, it only generates a HashAgg path if it thinks it
> will fit in work_mem, similar to the old behavior (though it wlil now
> spill to disk if the planner was wrong about it fitting in work_mem).
> The current default is true.

I have an anecdote that might be related to this discussion.

I was running an unrelated benchmark suite.  With PostgreSQL 12, one 
query ran out of memory.  With PostgreSQL 13, the same query instead ran 
out of disk space.  I bisected this to the introduction of disk-based 
hash aggregation.  Of course, the very point of that feature is to 
eliminate the out of memory and make use of disk space instead.  But 
running out of disk space is likely to be a worse experience than 
running out of memory.  Also, while it's relatively easy to limit memory 
use both in PostgreSQL and in the kernel, it is difficult or impossible 
to limit disk space use in a similar way.

I don't have a solution or proposal here, I just want to mention this as 
a possibility and suggest that we look out for similar experiences.

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