Re: bad estimation together with large work_mem generates terrible slow hash joins

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tomas Vondra" <tv(at)fuzzy(dot)cz>
Cc: "Robert Haas" <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: bad estimation together with large work_mem generates terrible slow hash joins
Date: 2014-09-11 15:28:19
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

"Tomas Vondra" <tv(at)fuzzy(dot)cz> writes:
> On 11 Z 2014, 16:11, Tom Lane wrote:
>> Ah. Well, that would mean that we need a heuristic for deciding when to
>> increase the number of buckets versus the number of batches ... seems
>> like a difficult decision.

> That's true, but that's not the aim of this patch. The patch simply
> increases the number of buckets if the load happens to get too high, and
> does not try to decide between increasing nbuckets and nbatch.

On further thought, increasing nbuckets without changing the batch
boundaries would not get us out of an out-of-work_mem situation, in fact
it makes memory consumption worse not better (assuming you count the
bucket headers towards work_mem ;-)).

So in principle, the rule seems like it ought to go "if load (defined as
max bucket chain length, I imagine?) gets too high, but we are still
well below work_mem, increase nbuckets; else increase nbatch". And
perhaps we reset nbuckets again for the next batch, not sure. If we
are dealing with an out-of-work_mem situation then only increasing nbatch
would be a suitable response.

Because of the risk that increasing nbuckets would itself lead to a
work_mem violation, I don't think it's sane to ignore the interaction
entirely, even in a first patch.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2014-09-11 15:30:49 Re: Optimization for updating foreign tables in Postgres FDW
Previous Message Kouhei Kaigai 2014-09-11 15:24:07 Re: [v9.5] Custom Plan API