DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=negora.com;
	h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:Content-Type:Content-Transfer-Encoding:X-Identified-User;
	b=Oh55LoW9eNze6dbbA6vuIovDjM5TwxG2kfWAe+lLdTPPGmkv5e1hzCN3YPISgabTi9nIRYgZv7ANJXypr9Q2COywQoi8yS1tutDEKdEgkK21U6OTRckHUPevNiPzRwxE;
Message-ID: <4B83E8C5.2050902@negora.com>
Date: Tue, 23 Feb 2010 15:40:05 +0100
From: negora <negora@negora.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Internal operations when the planner makes a hash join.
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello:

I'm an ignorant in what refers to performance analysis of PostgreSQL.
I've a doubt about how the PostgreSQL planner makes a hash join. I've
tried to "dig" into the archive of this mailing list but I haven't found
what I'm looking for. So I'm explaining my doubt with an example to see
if anyone can help me.

Let's suppose that I've 2 tables, one of students and the other one of
parents in a many-to-one relation. I want to do something like this:

        SELECT s.complete_name, f.complete_name
        FROM students AS s
        JOIN fathers AS f ON f.id_father = s.id_father;

Using the ANALYZE command, I've checked that the planner firstly scans
and extracts the required information from "fathers", builds a temporary
hash table from it, then scans "students", and finally joins the
information from this table and the temporary one employing the relation
"f.id_father = s.id_father".

My doubt is about this last step. When the planner checks the temporary
table looking for the parent of a student:

A) Does it run through the temporary father's table one time per
student? This means that if there are 500 students, it's doing 500 loops
on the temporary table.
B) Or does it try to internally group students with the same father ID
to avoid doing "absurd" loops on the temporary one?

That's all. Thank you very much for your kindness :) .