When you join two tables, you can use any of three plans to perform the join.
Nested loop join — A table is scanned once for every row in the other joined table.
Merge sort join — Each table is sorted on the join attributes before the join starts. The two tables are then scanned in parallel, and the matching rows are combined to form the join rows.
Hash join — A table is scanned and its join attributes are loaded into a hash table using its join attributes as hash keys. The other joined table is then scanned and its join attributes are used as hash keys to locate the matching rows from the first table.
List of optimizer hints for join plans
The following table lists the optimizer hints that you can use to influence the planner to use one type of join plan over another.
Use a hash join for table.
Don't use a hash join for table.
Use a merge sort join for table.
Don't use a merge sort join for table.
Use a nested loop join for table.
Don't use a nested loop join for table.
Example: Hash join
In this example, the USE_HASH hint is used for a join on the pgbench_branches and pgbench_accounts tables. The query plan shows that a hash join is used by creating a hash table from the join attribute of the pgbench_branches table:
Next, the NO_USE_HASH(a b) hint forces the planner to use an approach other than hash tables. The result is a merge join.
Finally, the USE_MERGE hint forces the planner to use a merge join:
Example: Three-table join
In this three-table join example, the planner first performs a hash join on the pgbench_branches and pgbench_history tables. Then it performs a hash join of the result with the pgbench_accounts table.
This plan is altered by using hints to force a combination of a merge sort join and a hash join: