2.4.4 Joining Relations Hints

Table of Contents Previous Next


2 SQL Tutorial : 2.4 Optimizer Hints : 2.4.4 Joining Relations Hints

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.
USE_HASH(table [...])
NO_USE_HASH(table [...])
USE_MERGE(table [...])
NO_USE_MERGE(table [...])
USE_NL(table [...])
NO_USE_NL(table [...])
In the following 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.
In this three-table join example, the planner first performs a hash join on the pgbench_branches and pgbench_history tables, then finally performs a hash join of the result with the pgbench_accounts table.

2 SQL Tutorial : 2.4 Optimizer Hints : 2.4.4 Joining Relations Hints

Table of Contents Previous Next