Re: Hash function constant in the Hash join algorithm

Ravi Kiran <ravi(dot)kolanpaka(at)gmail(dot)com> writes:
> From the above two cases, I am understanding that even even when the hash
> function is made to return a constant, The hashjoin agorithm is taking
> significantly lower time compared to nested loop.

> Could anyone explain why does the hashjoin algorithm takes significantly
> lower time compared to nested loop when the hash function is made to return
> a constant value or have I done any mistake at any part of the code?

Well, hashjoin would suck the inner relation into memory (into its
hashtable) whereas nestloop would rescan the inner relation each time,
implying more buffer-access overhead, tuple visibility checking, etc.

A closer comparison might be to a nestloop that has a Materialize node
above its inner relation.

regards, tom lane