On Sun, Jul 19, 2020 at 4:38 AM Stephen Frost <sfr...@snowman.net> wrote:
> > (The only reason I'm in favor of heap_mem[_multiplier] is that it > > seems like it might be possible to use it to get *better* plans > > than before. I do not see it as a backwards-compatibility knob.) > > I still don't think a hash_mem-type thing is really the right direction > to go in, even if making a distinction between memory used for sorting > and memory used for hashing is, and I'm of the general opinion that we'd > be thinking about doing something better and more appropriate- except > for the fact that we're talking about adding this in during beta. > > In other words, if we'd stop trying to shoehorn something in, which > we're doing because we're in beta, we'd very likely be talking about all > of this in a very different way and probably be contemplating something > like a query_mem that provides for an overall memory limit and which > favors memory for hashing over memory for sorting, etc. > At minimum we'd need a patch we would be happy with dropping in should there be user complaints. And once this conversation ends with that in hand I have my doubts whether there will be interest, or even project desirability, in working toward a "better" solution should this one prove itself "good enough". And as it seems unlikely that this patch would foreclose on other promising solutions, combined with there being a non-trivial behavioral change that we've made, suggests to me that we might as well just deploy whatever short-term solution we come up with now. As for hashagg_avoid_disk_plan... The physical processes we are modelling here: 1. Processing D amount of records takes M amount of memory 2. Processing D amount of records in-memory takes T time per record while doing the same on-disk takes V time per record 3. Processing D amount of records via some other plan has an effective cost U 3. V >> T (is strictly greater than) 4. Having chosen a value for M that ensures T it is still possible for V to end up used Thus: If we get D wrong the user can still tweak the system by changing the hash_mem_multiplier (this is strictly better than v12 which used work_mem) Setting hashagg_avoid_disk_plan = off provides a means to move V infinitely far away from T (set to on by default, off reverts to v12 behavior). There is no way for the user to move V's relative position toward T (n/a in v12) The only way to move T is to make it infinitely large by setting enable_hashagg = off (same as in v12) Is hashagg_disk_cost_multiplier = [0.0, 1,000,000,000.0] i.e., (T * hashagg_disk_cost_multiplier == V) doable? It has a nice symmetry with hash_mem_multiplier and can move V both toward and away from T. To the extent T is tunable or not in v12 it can remain the same in v13. David J.
