From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: bad estimation together with large work_mem generates terrible slow hash joins |
Date: | 2014-06-26 18:43:59 |
Message-ID: | 53AC69EF.2000607@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Attached is v2 of the patch, with some cleanups / minor improvements:
* improved comments, whitespace fixed / TODOs etc.
* tracking inital # of buckets (similar to initial # of batches)
* adding info about buckets to EXPLAIN ANALYZE, similar to batches - I
didn't want to make it overly complex, so the info about initial
bucket/batch count is added if at least one them is modified
* modified threshold triggering the growth, to get NTUP_PER_BUCKET on
average (see the NTUP_GROW_THRESHOLD comment nodeHash.c)
* there's a single FIXME, related to counting tuples in the
One thing that's important to note is the difference between # of
batches and # of buckets. While one # of batches is "global" the # of
buckets is 'within a batch'. So theoretically each batch can use
different number of buckets.
However the value is reused between batches, so it only grows. That
means this is possible:
initial: 1024 buckets (before 1st batch)
batch 1: 1024 buckets
batch 2: 1024 buckets
batch 3: 4096 buckets
batch 4: 8192 buckets
while this is not:
initial: 1024 buckets (before 1st batch)
batch 1: 1024 buckets
batch 2: 4096 buckets
batch 3: 1024 buckets
batch 4: 8192 buckets
However in practice I expect the first batch will to do all the work,
and the following batches will just reuse the same number of buckets.
This of course assumes the batches have similar tuple sizes etc.
So the first batch will do all the reshuffling the tables, and the
following batches will reuse the 'right' number of buckets from the start.
regards
Tomas
Attachment | Content-Type | Size |
---|---|---|
hashjoin-nbuckets-growth-v2.patch | text/x-diff | 7.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-06-26 18:47:15 | Re: better atomics - v0.5 |
Previous Message | Robert Haas | 2014-06-26 18:37:27 | Re: better atomics - v0.5 |