Re: Hash function constant in the Hash join algorithm - Mailing list pgsql-general

From Tom Lane
Subject Re: Hash function constant in the Hash join algorithm
Date
Msg-id 26370.1431024568@sss.pgh.pa.us
Whole thread Raw
In response to Hash function constant in the Hash join algorithm  (Ravi Kiran <ravi.kolanpaka@gmail.com>)
List pgsql-general
Ravi Kiran <ravi.kolanpaka@gmail.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


pgsql-general by date:

Previous
From: Ravi Kiran
Date:
Subject: Hash function constant in the Hash join algorithm
Next
From: Rajiv M Ranganath
Date:
Subject: Re: Any freeware graphic display of DDL software available?