Product Documentation

Appearance

All platforms

Plan Hinting: JOIN METHOD Hint ​

Overview ​

The Join Method hint allows you to specify which join method (Hash Join, Nested Loop Join) should be used in prioriy for the join in between all specified tables. You can also prevent the use of certain join methods.

Supported Methods: ​

  • HashJoin
  • NestLoop
  • NoHashJoin
  • NoNestLoop
sql
<Method> ( <table_name>, <table_name> [, <table_name> ...] )

Note: If the requested method would prevent a valid plan from being made, the planner will fall back to any other method that is available, based on its regular costing algorithm.

Examples: ​

Force a HASH join in between two tables ​

Here, the planner is instructed to use a Hash Join between orders and customers.

sql
yellowbrick=# EXPLAIN SETTING (HASHJOIN (orders, customers))
yellowbrick-# SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 id   rows_planned   workers   node
 13              1       all   SELECT
  1              1       all   INNER HASH JOIN ON (customers.id = orders.customer_id)
  4              5       all   |-SCAN orders
                               |   orders.customer_id = bloom(7) AND scan_constraints: min_max(orders.customer_id)
  7              1       all   |-BUILD
 14              1       all     DISTRIBUTE REPLICATE
 10              1       all     SCAN customers

Forbid usage of HASH join in between orders, customers and product tables ​

Let's now also tell the planner to avoid using a Hash Join when joining all three tables products, orders and customers.

sql
yellowbrick=# EXPLAIN SETTING (NOHASHJOIN (orders, products,  customers); HASHJOIN (orders, customers))
yellowbrick-# SELECT * FROM orders
yellowbrick-# JOIN customers ON orders.customer_id = customers.customer_id
yellowbrick-# JOIN products on orders.order_id = products.id;

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 id   rows_planned   workers   node
 20              1       all   SELECT
  1              1       all   INNER LOOP JOIN ON (orders.order_id = products.id)
  4              1       all   |-SCAN products
 22         100000       all   |-DISTRIBUTE REPLICATE
  8         100000       all     INNER HASH JOIN ON (customers.customer_id = orders.customer_id)
 11         100000       all     |-SCAN orders
                                 |   orders.customer_id = bloom(14) AND scan_constraints: min_max(orders.customer_id)
 14           1000       all     |-BUILD
 23           1000       all       DISTRIBUTE REPLICATE
 17           1000       all       SCAN customers