* Simon Riggs (simon@2ndQuadrant.com) wrote: > I don't see anything for 9.4 in here now.
Attached is what I was toying with (thought I had attached it previously somewhere.. perhaps not), but in re-testing, it doesn't appear to do enough to move things in the right direction in all cases. I did play with this a fair bit yesterday and while it improved some cases by 20% (eg: a simple join between pgbench_accounts and pgbench_history), when we decide to *still* hash the larger side (as in my 'test_case2.sql'), it can cause a similairly-sized decrease in performance. Of course, if we can push that case to hash the smaller side (which I did by hand with cpu_tuple_cost), then it goes back to being a win to use a larger number of buckets.
I definitely feel that there's room for improvment here but it's not an easily done thing, unfortunately. To be honest, I was pretty surprised when I saw that the larger number of buckets performed worse, even if it was when we picked the "wrong" side to hash and I plan to look into that more closely to try and understand what's happening. My first guess would be what Tom had mentioned over the summer- if the size of the bucket array ends up being larger than the CPU cache, we can end up paying a great deal more to build the hash table than it costs to scan through the deeper buckets that we end up with as a result (particularly when we're scanning a smaller table). Of course, choosing to hash the larger table makes that more likely..
This topic is interesting - we found very bad performance with hashing large tables with high work_mem. MergeJoin with quicksort was significantly faster.
I didn't deeper research - there is a possibility of virtualization overhead.