Re: Hash Join vs. Nested Loops

Mikito Harakiri wrote:
>
>Once again, ordinary Nested Loops is so obviously flawed that it doesn't
>make sence to compare it to anything. Indexed Nested Loops, on the other
>hand, have performance profile comarable to either Hash Join or Sort Merge
>Join on large inputs. It blows them away on small inputs.

I believe your original guess was right: the differences are important when the data is mainly on disk. The advantage of an hash-index vs. something like a B-tree index is that if you have a good hash function you probably only need to acces 1 disk page where with a B-tree this is only true if the whole index fits in main memory. The advantage of the merge join vs. nested loop with index is that chances are bigger that you still have the relevant pages in your page buffer when you re-encounter the same join value in the relation of the outside loop.

• Jan Hidders