Re: Default setting for enable_hashagg_disk (hash_mem)

On Wed, 8 Jul 2020 at 07:25, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Jul 7, 2020 at 5:55 AM David Rowley <dgrowleyml@gmail.com> wrote:
> > We're certainly not
> > going to get that for PG13, so I do think what we need here is just a
> > simple escape hatch. I mentioned my thoughts in [2], so won't go over
> > it again here. Once we've improved the situation in some future
> > version of postgres, perhaps along the lines of what Tomas mentioned,
> > then we can get rid of the escape hatch.
>
> If it really has to be a simple escape hatch in Postgres 13, then I
> could live with a hard disabling of spilling at execution time. That
> seems like the most important thing that is addressed by your
> proposal. I'm concerned that way too many users will have to use the
> escape hatch, and that that misses the opportunity to provide a
> smoother experience.

Yeah. It's a valid concern. I'd rather nobody would ever have to exit
through the escape hatch either. I don't think anyone here actually
wants that to happen. It's only been proposed to allow users a method
to escape the new behaviour and get back what they're used to.

I think the smoother experience will come in some future version of
PostgreSQL with generally better memory management for work_mem all
round. It's certainly been talked about enough and I don't think
anyone here disagrees that there is a problem with N being unbounded
when it comes to N * work_mem.

I'd really like to see this thread move forward to a solution and I'm
not sure how best to do that. I started by reading back over both this
thread and the original one and tried to summarise what people have
suggested.

I understand some people did change their minds along the way, so I
may have made some mistakes. I could have assumed the latest mindset
overruled, but it was harder to determine that due to the thread being
split.

For hash_mem = Justin [16], PeterG [15], Tomas [7]
hash_mem out of scope for PG13 = Bruce [8], Andres [9]
Wait for reports from users = Amit [10]
Escape hatch that can be removed later when we get something better =
Jeff [11], David [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?)
Maybe do nothing until we see how things go during beta = Bruce [3]
Just let users set work_mem = Alvaro [4] (I think he changed his mind
after Andres pointed out that changes other nodes in the plan too)
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]

David

[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