This topic describes Joining Relations Hint.

Three possible plans are available for you to join two tables:

  • Nested loops 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. Then, these two tables are scanned in parallel and the matched rows are combined to form the join rows.
  • Hash join: A table is scanned and its join attributes are loaded into a hash table. The join attributes of the table are used as hash keys. Then, the other joined table is scanned and its join attributes are used as hash keys to locate the matched rows from the first table.

The following table describes the optimizer hints that can be used to enable the planner to use a specified type of join plan.

Hint Description
USE_HASH(table [...]) Uses a hash join for the table.
NO_USE_HASH(table [...]) Does not use a hash join for the table.
USE_MERGE(table [...]) Uses a merge sort join for the table.
NO_USE_MERGE(table [...]) Does not use a merge sort join for the table.
USE_NL(table [...]) Uses a nested loops join for the table.
NO_USE_NL(table [...]) Does not use a nested loops join for the table.

Examples

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 table is created from the join attribute of the pgbench_branches table to enable a hash join.

EXPLAIN SELECT /*+ USE_HASH(b) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Join  (cost=21.45..81463.06 rows=2014215 width=12)
   Hash Cond: (a.bid = b.bid)
   ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
   ->  Hash  (cost=21.20..21.20 rows=20 width=4)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
(5 rows)

Then, the NO_USE_HASH(a b) hint forces the planner to use an approach other than hash tables. The result is a merge join.

EXPLAIN SELECT /*+ NO_USE_HASH(a b) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Merge Join  (cost=333526.08..368774.94 rows=2014215 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=21.63..21.68 rows=20 width=4)
         Sort Key: b.bid
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Materialize  (cost=333504.45..343575.53 rows=2014215 width=12)
         ->  Sort  (cost=333504.45..338539.99 rows=2014215 width=12)
               Sort Key: a.bid
               ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
(9 rows)

Finally, the USE_MERGE hint forces the planner to use a merge join.

EXPLAIN SELECT /*+ USE_MERGE(a) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Merge Join  (cost=333526.08..368774.94 rows=2014215 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=21.63..21.68 rows=20 width=4)
         Sort Key: b.bid
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Materialize  (cost=333504.45..343575.53 rows=2014215 width=12)
         ->  Sort  (cost=333504.45..338539.99 rows=2014215 width=12)
               Sort Key: a.bid
               ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
(9 rows)

In this three-table join example, the planner first performs a hash join on the pgbench_branches and pgbench_history tables. Then, the planner performs a hash join on the previous join result and the pgbench_accounts table.

EXPLAIN SELECT h.mtime, h.delta, b.bid, a.aid FROM pgbench_history h, pgbench_branches b, pgbench_accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=86814.29..123103.29 rows=500000 width=20)
   Hash Cond: (h.aid = a.aid)
   ->  Hash Join  (cost=21.45..15081.45 rows=500000 width=20)
         Hash Cond: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..8185.00 rows=500000 width=20)
         ->  Hash  (cost=21.20..21.20 rows=20 width=4)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Hash  (cost=53746.15..53746.15 rows=2014215 width=4)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=4)
(9 rows)

You can use the hints to force a combination of a merge sort join and a hash join and modify the plan.

EXPLAIN SELECT /*+ USE_MERGE(h b) USE_HASH(a) */ h.mtime, h.delta, b.bid, a.aid FROM pgbench_history h, pgbench_branches b, pgbench_accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Hash Join  (cost=152583.39..182562.49 rows=500000 width=20)
   Hash Cond: (h.aid = a.aid)
   ->  Merge Join  (cost=65790.55..74540.65 rows=500000 width=20)
         Merge Cond: (b.bid = h.bid)
         ->  Sort  (cost=21.63..21.68 rows=20 width=4)
               Sort Key: b.bid
               ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
         ->  Materialize  (cost=65768.92..68268.92 rows=500000 width=20)
               ->  Sort  (cost=65768.92..67018.92 rows=500000 width=20)
                     Sort Key: h.bid
                     ->  Seq Scan on pgbench_history h  (cost=0.00..8185.00 rows=500000 width=20)
   ->  Hash  (cost=53746.15..53746.15 rows=2014215 width=4)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=4)
(13 rows)