in nodeHash.c, the function ExecChooseHashTableSize() uses two different
methods for determining the number of buckets to use.
the current code looks something like:
if (ntuples * tuplesize > work_mem * 1024)buckets = (work_mem * 1024) / (tupsize * 10);
elsebuckets = ntuples/10
So for the case where a spill is expected; we use work_mem to decide on our
hash size. For the case where a spill isn't expected; we rely on the row
estimate alone -- and make no provision for speeding the join by using the
memory that we're allowed to use.
When profiling large hash-joins, it often is the case that scanning the
hash-buckets is a bottleneck; it would be nice for the user to be able to
"throw memory" at a join to improve performance.
Am I missing something about the current implementation ? I would expect
that the bucket count would be calculated something like:
buckets = (work_mem * 1024L) / (tup_size * NTUP_PER_BUCKET)
for both cases ?
making this change appears to improve hash-join performance substantially in
some cases, and as far as I can tell doesn't hurt anything (apart from using
memory that it is "allowed" to use given a particular work_mem setting).
-Tim
--
tkordas@greenplum.com