Join Operations In PostgreSQL

In this post, we will discuss about various Join Operations in PostgreSQL. PostgreSQL supports three join operations: nested loop join, merge join and hash join.

Note that the three join methods supported by PostgreSQL can perform all join operations, not only INNER JOIN, but also LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN and so on:

✔️ Nested loop join:

The nested loop join is the most fundamental join operation, and it can be used in any join conditions. PostgreSQL supports the nested loop join and four variations of it. which are-:

1. Basic Nested loop join
2. Materialized Nested Loop Join
3. Indexed Nested Loop Join
4. With outer index scan

✔️ Merge join:

Unlike the nested loop join, merge join can be only used in natural joins and equi-joins.

The cost of the merge join is estimated by the initial_cost_mergejoin() and final_cost_mergejoin() functions.

As the exact cost estimation is complicated, it is omitted and only the runtime order of the merge join algorithm is shown. The start-up cost of the merge join is the sum of sorting costs of both inner and outer tables; Similar to the nested loop join, the merge join in PostgreSQL has Three variations.

1. Basic Merge Join
2. Materialized Merge Join
3. With outer index scan

✔️ Hash join:

Similar to the merge join, the hash join can be only used in natural joins and equi-joins. The hash join in PostgreSQL behaves differently depending on the sizes of the tables. If the target table is small enough (more precisely, the size of the inner table is 25% or less of work_mem), it will be a simple two-phase in-memory hash join; otherwise, the hybrid hash join is used with the skew method.

✔️ References:

1. https://age.apache.org/
2. https://github.com/apache/age
3. https://www.interdb.jp/pg/index.html