Re: HashJoin w/option to unique-ify inner rel

On Sat, Apr 25, 2009 at 6:42 AM, Grzegorz Jaskiewicz
<gj@pointblue.com.pl> wrote:
> On 25 Apr 2009, at 04:52, Robert Haas wrote:
>> blow the hash-join plan out of the water anyway... but Stephen Frost
>> was telling me at JDcon East that he sometimes sets it to something
>> like 8GB when he's the only user on his apparently-quite-awesome
>> hardware...)
>
> For the record, because most queries have 5-6 joins here, I always set it up
> to 32MB on production. We don't have more than 100-150 connections, so it
> plays well on normal 32bit machine with 4GB.
>
> If what you wrote about hash-join is confirmed by others, than I am pretty
> much +100 for fixing it.
>
> (just my penny).

You may find the attached patch interesting to play around with.  It
changes the NTUP_PER_BUCKET into a GUC called hash_load, and adds
EXPLAIN support to show the number of buckets and batches.  This is
just for experimentation: I'm not in favor of adding Yet Another Thing
for users to tune, but if you try it out, you will see (I think) that
changing hash_load has a dramatic effect on the estimated cost of a
hash join but a much less dramatic effect on the actual run-time.

...Robert