Re: PATCH: postpone building buckets to the end of Hash (in HashJoin)

Hi,

On 12/17/2015 10:28 PM, Tomas Vondra wrote:
> Hi,
>
> On 12/17/2015 07:20 PM, Robert Haas wrote:
> ...
>>
>> If this doesn't regress performance in the case where the number of
>> buckets is estimated accurately to begin with, then I think this is
>> a great idea. Can you supply some performance tests results for that
>> case, and maybe some of the other cases also?
>
> I don't see how it could regress performance, and the benchmarks I've
> done confirm that. I'll do more thorough benchmarking and post the
> results here, but not now as this patch is in 2016-01 CF and I want to
> put all my time into reviewing patches from the open commitfest.

I've finally got to do more thorough benchmarks, and surprisingly there
seems to be a minor regression. The scripts I've used are attached,
along with results, but in short it joins two tables, with different
combinations:

1M x 10M
10M x 100M
5M x 250M

with different work_mem sizes (so that the smallest value uses a bunch
of batches while the largest value uses a single batch).

The 1x10 and 10x100 are designed to fit into RAM on the machine
(including the temporary files in batching mode), while 5x250 is
designed to specifically force the temp files to disk (but it's on fast
SSD array, so not a big deal).

Average timings for current master and the first two patches of the
series (0001 postpones the build of buckets and 0002 always starts
without batching) look like this (the timings are in milliseconds):

size work_mem master postpone no-batch
-----------------------------------------------------
1x10 8 5735 5760 6107
32 5939 5955 6124
128 4852 5038 5175
-----------------------------------------------------
5x250 64 158512 158429 159008
256 144046 144584 144994
1024 111478 117529 116933
-----------------------------------------------------
10x100 64 68389 68278 68530
256 66693 66415 66605
1024 48606 50654 50490

So compared to master, the differences look like this:

size work_mem postpone no-batch
-----------------------------------------
1x10 8 0.44% 6.50%
32 0.28% 3.13%
128 3.83% 6.65%
-----------------------------------------
5x250 64 -0.05% 0.31%
256 0.37% 0.66%
1024 5.43% 4.89%
-----------------------------------------
10x100 64 -0.16% 0.21%
256 -0.42% -0.13%
1024 4.21% 3.88%

So for the first patch (postponing building of buckets) with batching,
the difference is rather negligible, possibly within noise (~0.5%). When
the hash join runs with a single batch, the difference however gets much
more significant 4-5%.

I'm not going to discuss the second patch for now, but naturally it has
the same issue and apparently also some additional ones (at least on the
1x10 data set).

I have spent a fair amount of time profiling this, and I can't wrap my
head around where the difference comes from, though. Essentially we
don't do any additional stuff, we've just moved some of the stuff to a
different place in the code.

It might change the memory access pattern a bit, but the original
patterns are not exactly sequential or so, as we're moving random
updates to array of pointers. Or rather moving them to the
BuildBuckets() method, so if something consumes more time, it should be
in this method, I believe.

So I've measured how much time the function takes for the 1x10 and
10x100 datasets, and it's ~25ms and ~290ms respectively. That's way less
than the actual difference in query runtime, which is ~190ms and ~2050ms.

So even if we entirely skipped the bucket build, we would not recover
the difference. Not sure what's going on here.

I've also done some profiling using perf, but I haven't really found
anything suspicious there.

Any ideas what might be the cause of this?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services