Re: Default setting for enable_hashagg_disk

On Sun, Jul 19, 2020 at 4:38 AM Stephen Frost <sfrost@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.