On Wed, 8 Jul 2020 at 07:25, Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Jul 7, 2020 at 5:55 AM David Rowley <dgrowle...@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.gv4...@telsasoft.com [2] https://www.postgresql.org/message-id/2214502.1593019...@sss.pgh.pa.us [3] https://www.postgresql.org/message-id/20200625182512.gc12...@momjian.us [4] https://www.postgresql.org/message-id/20200625224422.GA9653@alvherre.pgsql [5] https://www.postgresql.org/message-id/caa4ek1k0cgk_8hryxsvppgoh_z-ny+uztcfwb2we6baj9dx...@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.gd26...@momjian.us [9] https://www.postgresql.org/message-id/20200707171216.jqxrld2jnxwf5...@alap3.anarazel.de [10] https://www.postgresql.org/message-id/CAA4eK1KfPi6iz0hWxBLZzfVOG_NvOVJL=9UQQirWLpaN=ka...@mail.gmail.com [11] https://www.postgresql.org/message-id/8bff2e4e8020c3caa16b61a46918d21b573eaf78.ca...@j-davis.com [12] https://www.postgresql.org/message-id/CAApHDvqFZikXhAGW=ukzkq1_fzhy+xzmuzajinj6rwythh4...@mail.gmail.com [13] https://www.postgresql.org/message-id/cafj8prbf1w4ndz-ynd+muptfizfbs7+cpjc4ob8v9d3x0ms...@mail.gmail.com [14] https://www.postgresql.org/message-id/20200624191433.5gnqgrxfmucex...@alap3.anarazel.de [15] https://www.postgresql.org/message-id/CAH2-WzmD+i1pG6rc1+Cjc4V6EaFJ_qSuKCCHVnH=oruqd-z...@mail.gmail.com [16] https://www.postgresql.org/message-id/20200703024649.gj4...@telsasoft.com [17] https://www.postgresql.org/message-id/ca+tgmobyv9+t-wjx-ctpdqurcgt1thz1ml3v1nxc4m4g-h6...@mail.gmail.com [18] https://www.postgresql.org/message-id/caa4ek1k0cgk_8hryxsvppgoh_z-ny+uztcfwb2we6baj9dx...@mail.gmail.com