Join algorithms
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
recognize the three join methods and when each wins.
Planned topics
- Nested loop: for each outer row, look up inner rows; great with a small outer side and an index on the inner side.
- Hash join: build a hash table of one side, probe with the other; good for large, unsorted inputs and equality joins.
- Merge join: both sides sorted on the join key; good when inputs are already ordered (index or sort).
- A nested loop with a huge outer row count and a seq scan inside is a classic disaster.
Draft outline details
Demonstration
three small plan excerpts with their node names and when you would expect each.
Check your understanding
Which join is best for one customer's few orders joined to products by primary key?
Show answer
Nested loop with an index lookup.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.