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