The following EXPLAIN report shows how the Optimizer may use the join index in a query when an additional search condition is added on the join indexed rows.
EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders WHERE l_orderkey = o_orderkey AND o_orderdate > '2003-11-01';
Explanation
--------------------------------------------------------------------------
1) First, we lock LOUISB.order_join_line for read on a
reserved Row Hash to prevent a global deadlock.
2) Next, we do an all-AMPs RETRIEVE step from join index table
LOUISB.order_join_line with a range constraint of (
"LOUISB.order_join_line.Field_1026 > 971101") with a residual
condition of ("(NOT (LOUISB.order_join_line.o_orderdate IS NULL ))
AND (LOUISB.order_join_line.Field_1026 > 971101)") into Spool 1,
which is built locally on the AMPs. The input table is not
cached in memory, but it is eligible for synchronized scanning.
The size of Spool 1 is estimated to be 1000 rows. The estimated time
for this step is 0.32 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.