Re: [HACKERS] Parallel Hash take II

On Thu, Nov 16, 2017 at 7:57 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> The contrast with the situation with Thomas and his hash join patch is
> interesting. Hash join is *much* more sensitive to the availability of
> memory than a sort operation is.
>
>> I don't really have a good answer to "but what should we otherwise do",
>> but I'm doubtful this is quite the right answer.
>
> I think that the work_mem model should be replaced by something that
> centrally budgets memory. It would make sense to be less generous with
> sorts and more generous with hash joins when memory is in short
> supply, for example, and a model like this can make that possible. The
> work_mem model has always forced users to be far too conservative.
> Workloads are very complicated, and always having users target the
> worst case leaves a lot to be desired.

In the old days, Oracle had only simple per-operation memory limits
too, and that applied to every operation in every thread just like our
work_mem. It's interesting that they had separate knobs for sort and
hash though, and defaulted to giving hash twice as much.

With a whole-plan memory target, our planner would probably begin to
plan join order differently to minimise the number of hash tables in
memory at once, like other RDBMSs. Not sure how the plan-wide target
should work though -- try many plans, giving different portions of
budget to different subplans? That should work fine if you like
O(please-melt-my-computer), especially if combined with a similar
approach to choosing worker numbers. Some kind of feedback system?
Seems like a different kind of planner, but I have no clue. If you
have ideas/papers/references, it'd be great to see a new thread on
that subject.

--
Thomas Munro
http://www.enterprisedb.com