pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: negora <negora@negora.com>
To: pgsql-performance@postgresql.org
Subject: Re: Internal operations when the planner makes a hash join.
Date: Tue, 23 Feb 2010 09:30:38 -0600
Message-ID: <4B83A03E020000250002F4FD@gw.wicourts.gov> (raw)
In-Reply-To: <4B83E8C5.2050902@negora.com>
References: <4B83E8C5.2050902@negora.com>

negora <negora@negora.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



view thread (11+ messages)  latest in thread

Message-ID: <4B83A03E020000250002F4FD@gw.wicourts.gov>
Permalink:  ../4B83A03E020000250002F4FD@gw.wicourts.gov/
Also on:    postgresql.org/message-id/4B83A03E020000250002F4FD@gw.wicourts.gov

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, negora@negora.com
  Subject: Re: Internal operations when the planner makes a hash join.
  In-Reply-To: <4B83A03E020000250002F4FD@gw.wicourts.gov>
  References: <4B83E8C5.2050902@negora.com> <4B83A03E020000250002F4FD@gw.wicourts.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox