Re: Internal operations when the planner makes a hash join.

On Feb 23, 2010, at 8:53 AM, Alvaro Herrera wrote:

> negora wrote:
>
>> According to how I understood the process, the engine would get the
>> name from the student with ID 1 and would look for the name of the
>> father with ID 1 in the hashed table. It'd do exactly the same with the
>> student #2 and father #2. But my big doubt is about the 3rd one
>> (Anthony). Would the engine "know" that it already had retrieved the
>> father's name for the student 1 and would avoid searching for it into
>> the hashed table (using some kind of internal mechanism which allows to
>> "re-utilize" the name)? Or would it search into the hashed table again?<br>
>
> The hash table is searched again. But that's fast, because it's a hash
> table.
>

To answer the question another way, "remembering" that it has already seen father A once and tracking that would use a hash table to remember that fact.

The hash table created by the first scan IS the "remember you have seen this father" data structure, optimized for fast lookup. So before even looking at the first student, the hash table is built so that it is fast to find out if a father has been seen before, and if so where that father's data is located. Looking this data up is often referred to as a "probe" and not a "scan" because it takes just as long to do if the hash table has 100 entries or 10000 entries. The drawback is that the whole thing has to fit in RAM.

> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance