Re: Default setting for enable_hashagg_disk - Mailing list pgsql-hackers

On Tue, 14 Jul 2020 at 01:13, Stephen Frost <sfrost@snowman.net> wrote:
> Yes, increasing work_mem isn't unusual, at all.  What that tweet shows
> that I don't think folks who are suggesting things like setting this
> factor to 2.0 is that people may have a work_mem configured in the
> gigabytes- meaning that a 2.0 value would result in a work_mem of 5GB
> and a hash_mem of 10GB.  Now, I'm all for telling people to review their
> configurations between major versions, but that's a large difference
> that's going to be pretty deeply hidden in a 'multiplier' setting.

I think Peter seems to be fine with setting the default to 1.0, per [0].

This thread did split off a while back into "Default setting for
enable_hashagg_disk (hash_mem)", I did try and summarise who sits
where on this in [19].

I think it would be good if we could try to move towards getting
consensus here rather than reiterating our arguments over and over.

Updated summary:
* For hash_mem = Tomas [7], Justin [16]
* For hash_mem_multiplier with a default > 1.0 = DavidG [21]
* For hash_mem_multiplier with default = 1.0 =  PeterG [15][0], Tom [20][24]
* hash_mem out of scope for PG13 = Bruce [8], Andres [9]
* hashagg_mem default to -1 meaning use work_mem = DavidR [23] (2nd preference)
* Escape hatch that can be removed later when we get something better
= Jeff [11], DavidR [12], Pavel [13], Andres [14], Justin [1]
* Add enable_hashagg_spill = Tom [2] (I'm unclear on this proposal.
Does it affect the planner or executor or both?) (updated opinion in
[20])
* Maybe do nothing until we see how things go during beta = Bruce [3], Amit [10]
* Just let users set work_mem = Stephen [21], Alvaro [4] (Alvaro
changed his mind after Andres pointed out that changes other nodes in
the plan too [25])
* Swap enable_hashagg for a GUC that specifies when spilling should
occur. -1 means work_mem = Robert [17], Amit [18]
* hash_mem does not solve the problem = Tomas [6] (changed his mind in [7])

Perhaps people who have managed to follow this thread but not chip in
yet can reply quoting the option above that they'd be voting for. Or
if you're ok changing your mind to some option that has more votes
than the one your name is already against. That might help move this
along.

David

[0] https://www.postgresql.org/message-id/CAH2-Wz=VV6EKFGUJDsHEqyvRk7pCO36BvEoF5sBQry_O6R2=nw@mail.gmail.com
[1] https://www.postgresql.org/message-id/20200624031443.GV4107@telsasoft.com
[2] https://www.postgresql.org/message-id/2214502.1593019796@sss.pgh.pa.us
[3] https://www.postgresql.org/message-id/20200625182512.GC12486@momjian.us
[4] https://www.postgresql.org/message-id/20200625224422.GA9653@alvherre.pgsql
[5] https://www.postgresql.org/message-id/CAA4eK1K0cgk_8hRyxsvppgoh_Z-NY+UZTcFWB2we6baJ9DXCQw@mail.gmail.com
[6] https://www.postgresql.org/message-id/20200627104141.gq7d3hm2tvoqgjjs@development
[7] https://www.postgresql.org/message-id/20200629212229.n3afgzq6xpxrr4cu@development
[8] https://www.postgresql.org/message-id/20200703030001.GD26235@momjian.us
[9] https://www.postgresql.org/message-id/20200707171216.jqxrld2jnxwf5ozv@alap3.anarazel.de
[10] https://www.postgresql.org/message-id/CAA4eK1KfPi6iz0hWxBLZzfVOG_NvOVJL=9UQQirWLpaN=kANTQ@mail.gmail.com
[11] https://www.postgresql.org/message-id/8bff2e4e8020c3caa16b61a46918d21b573eaf78.camel@j-davis.com
[12] https://www.postgresql.org/message-id/CAApHDvqFZikXhAGW=UKZKq1_FzHy+XzmUzAJiNj6RWyTHH4UfA@mail.gmail.com
[13] https://www.postgresql.org/message-id/CAFj8pRBf1w4ndz-ynd+mUpTfiZfbs7+CPjc4ob8v9d3X0MscCg@mail.gmail.com
[14] https://www.postgresql.org/message-id/20200624191433.5gnqgrxfmucexldm@alap3.anarazel.de
[15] https://www.postgresql.org/message-id/CAH2-WzmD+i1pG6rc1+Cjc4V6EaFJ_qSuKCCHVnH=oruqD-zqow@mail.gmail.com
[16] https://www.postgresql.org/message-id/20200703024649.GJ4107@telsasoft.com
[17] https://www.postgresql.org/message-id/CA+TgmobyV9+T-Wjx-cTPdQuRCgt1THz1mL3v1NXC4m4G-H6Rcw@mail.gmail.com
[18] https://www.postgresql.org/message-id/CAA4eK1K0cgk_8hRyxsvppgoh_Z-NY+UZTcFWB2we6baJ9DXCQw@mail.gmail.com
[19] https://www.postgresql.org/message-id/CAApHDvrP1FiEv4AQL2ZscbHi32W+Gp01j+qnhwou7y7p-QFj_w@mail.gmail.com
[20] https://www.postgresql.org/message-id/2107841.1594403217@sss.pgh.pa.us
[21] https://www.postgresql.org/message-id/20200710141714.GI12375@tamriel.snowman.net
[22] https://www.postgresql.org/message-id/CAKFQuwa2gwLa0b%2BmQv5r5A_Q0XWsA2%3D1zQ%2BZ5m4pQprxh-aM4Q%40mail.gmail.com
[23] https://www.postgresql.org/message-id/CAApHDvpxbHHP566rRjJWgnfS0YOxR53EZTz5LHH-jcEKvqdj4g@mail.gmail.com
[24] https://www.postgresql.org/message-id/2463591.1594514874@sss.pgh.pa.us
[25] https://www.postgresql.org/message-id/20200625225853.GA11137%40alvherre.pgsql