|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
"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
|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|