3.5. Join Operations

PostgreSQL supports three primary join operations: nested loop join, merge join, and hash join. Both the nested loop join and the merge join include several variations.

This section assumes a basic familiarity with the behavior of these three join methods. For further details on the fundamental concepts, refer to the following resources:

References

  1. Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, “Database System Concepts”, McGraw-Hill Education, ISBN-13: 978-0073523323

  2. Thomas M. Connolly, and Carolyn E. Begg, “Database Systems”, Pearson, ISBN-13: 978-0321523068

While standard joins are well-documented, the hybrid hash join with skew supported by PostgreSQL often lacks detailed explanation; therefore, it is covered in greater depth in this section.

Section Contents

The three join methods in PostgreSQL can perform all join types, including INNER JOIN, LEFT/RIGHT OUTER JOIN, and FULL OUTER JOIN. For simplicity, this chapter focuses on the NATURAL INNER JOIN.

Cardinality Estimation in Multi-table Joins

As mentioned in Section 3.2.4, cardinality estimation for multi-table joins is calculated under the assumption that columns are statistically independent.

If correlations exist between columns, estimation accuracy decreases. Consequently, the query planner may fail to create an efficient plan.

Database engineers and administrators should remain mindful of this limitation.