Skip site navigation (1) Skip section navigation (2)

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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "negora" <negora(at)negora(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Internal operations when the planner makes a hash join.
Date: 2010-02-23 15:30:38
Message-ID: 4B83A03E020000250002F4FD@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
negora <negora(at)negora(dot)com> 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

In response to

Responses

pgsql-performance by date

Next:From: negoraDate: 2010-02-23 16:39:40
Subject: Re: Internal operations when the planner makes a hash join.
Previous:From: negoraDate: 2010-02-23 14:40:05
Subject: Internal operations when the planner makes a hash join.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group