Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id ED75F632D78 for ; Tue, 23 Feb 2010 11:30:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 54397-10 for ; Tue, 23 Feb 2010 15:30:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 0EB4162FB78 for ; Tue, 23 Feb 2010 11:30:41 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Tue, 23 Feb 2010 09:30:40 -0600 Message-Id: <4B83A03E020000250002F4FD@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Tue, 23 Feb 2010 09:30:38 -0600 From: "Kevin Grittner" To: "negora" , Subject: Re: Internal operations when the planner makes a hash join. References: <4B83E8C5.2050902@negora.com> In-Reply-To: <4B83E8C5.2050902@negora.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.458 tagged_above=-10 required=5 tests=AWL=0.141, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/322 X-Sequence-Number: 37743 negora wrote: > I've a doubt about how the PostgreSQL planner makes a hash join. > 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". This sort of plan is sometimes used when the optimizer expects the hash table to fit into RAM, based on statistics and your work_mem setting. If it does fit, that's one sequential scan of the father table's heap, and a hashed lookup into RAM to find the father to match each student. For the sort of query you're showing, that's typically a very good plan. -Kevin