EXPLAIN for Query With Base Table-Join Index Table Join
The following EXPLAIN shows how the join index might be used 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 a distinct LOUISB."pseudo table" for read on a
Row Hash to prevent global deadlock for LOUISB.order_join_line.
2) Next, we lock a distinct LOUISB."pseudo table" for read on a
Row Hash to prevent global deadlock for LOUISB.customer.
3) We lock LOUISB.order_join_line for read, and we lock LOUISB.customer
for read.
4) 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.
5) 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.
6) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.