The following EXPLAIN report shows how the Optimizer may use the join index in a query when join indexed rows are used to join with another base table.
EXPLAIN SELECT o_orderdate, c_name, c_phone, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders, customer WHERE l_orderkey = o_orderkey AND o_custkey = c_custkey;
Explanation
--------------------------------------------------------------------------
1) First, we lock LOUISB.order_join_line for read on a
reserved Row Hash to prevent a global deadlock.
2) Next, we lock LOUISB.customer for read.
3) We do an all-AMPs RETRIEVE step from join index table
LOUISB.order_join_line by way of an all-rows scan with a condition
of ("NOT (LOUISB.order_join_line.o_orderdate IS NULL)") into Spool 2,
which is redistributed by hash code to all AMPs. Then we do a
SORT to order Spool 2 by row hash. The size of Spool 2 is
estimated to be 1,000,000 rows. The estimated time for this step
is 1 minute and 53 seconds.
4) We do an all-AMPs JOIN step from LOUISB.customer by way of a
Row Hash match scan with no residual conditions, which is joined to
Spool 2 (Last Use). LOUISB.customer and Spool 2 are joined using
a merge join, with a join condition of ("Spool_2.o_custkey =
LOUISB.customer.c_custkey"). The result goes into Spool 1, which
is built locally on the AMPs. The size of Spool 1 is estimated to
be 1,000,000 rows. The estimated time for this step is 32.14
seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.