Nested Join vs Hash Join vs Merge Join in PostgreSQL

Introduction

In PostgreSQL, there are three main types of join algorithms: Nested Loop Join, Hash Join, and Merge Join. Each of these algorithms has its own advantages and use cases. Let me explain each of them and their differences with examples.
1-Nested Loop Join:
Nested Loop Join is the simplest join algorithm. It works by iterating through each row in the outer table and comparing it with each row in the inner table. If the join condition is met, the rows are combined and added to the result set.
Example:
Consider two tables, "orders" and "customers". To find all orders along with their customer information, you could use a Nested Loop Join:

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;

In this case, PostgreSQL would iterate through each row in the "orders" table and compare it with each row in the "customers" table. If the customer_id in the orders table matches the id in the customers table, the rows are combined and added to the result set.
2-Hash Join:
Hash Join is more efficient than Nested Loop Join for larger tables. It works by creating a hash table for the smaller table (inner table) based on the join condition. Then, it scans the larger table (outer table) and looks up the corresponding rows in the hash table.
Example:
Using the same "orders" and "customers" tables, a Hash Join could be used as follows:

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;

In this case, PostgreSQL would create a hash table for the "customers" table based on the customer id. Then, it would scan the "orders" table and look up the corresponding customer information in the hash table using the customer_id.

3-Merge Join:
Merge Join is an efficient algorithm for joining two sorted tables. It works by simultaneously scanning both tables and comparing the rows based on the join condition. If the join condition is met, the rows are combined and added to the result set.
Example:
Assuming the "orders" and "customers" tables are sorted by customer_id, a Merge Join could be used as follows:

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;

In this case, PostgreSQL would simultaneously scan both the "orders" and "customers" tables. If the customer_id in the orders table matches the id in the customers table, the rows are combined and added to the result set.

Enforcing PostgreSQL
Enforcing PostgreSQL to use hash join exclusively can be achieved by disabling nested loop join and merge join through the configuration parameters 'enable_nestloop' and 'enable_mergejoin'. However, it is generally not recommended to manually enforce specific join strategies, as PostgreSQL's query planner is adept at selecting the most suitable algorithms for optimal performance.

While you can use the commands SET enable_nestloop = off;and SET enable_mergejoin = off; to disable nested loop join and merge join, respectively, it is important to exercise caution when employing such measures. The query planner in PostgreSQL intelligently evaluates query complexity, available resources, and statistics to determine the appropriate join strategy for a given scenario.

By allowing the query planner to operate autonomously, you benefit from its advanced optimization capabilities. It can adapt to varying conditions and select the most efficient join algorithm based on the specific query and data characteristics, leading to better overall performance.

Hence, it is generally advisable to rely on PostgreSQL's query planner to make informed decisions regarding join strategies. Instead of manually enforcing a specific join algorithm, consider optimizing your queries, ensuring appropriate indexes are in place, and maintaining up-to-date statistics for accurate planning decisions.

Conclusion
Nested Loop Join is suitable for small tables or when one table is much smaller than the other. Hash Join is more efficient for larger tables, while Merge Join is best for joining sorted tables. The PostgreSQL query planner automatically chooses the most appropriate join algorithm based on the table statistics and query conditions.
REF:
severalnines.com
The Internals of PostgreSQL