With join optimization, the following join plan is generated when the collection of join columns (color, size, and options) makes up a unique secondary index of the large table:
|
Operation |
Joined Tables |
Total Processing Time (seconds) |
|
Spool 3: Product Join |
duplicated color, direct options |
0.31 |
|
Spool 5: Product Join |
duplicated size, direct 3 |
1.62 |
|
Spool 6: Nested Join |
hashed 5, index widgets |
2.71 |
|
Spool 1: rowID Join |
hashed 6, index widgets |
5.65 |
The total estimated time is 10.07 seconds.
The estimated performance improvement factor is 667.
The EXPLAIN output for this optimized join plan is as follows:
Explanation
--------------------------------------------------------------------
1) First, we lock TEST.Color for read, we lock TEST.Options for read,
we lock TEST.Size for read, and we lock TEST.Widgets for read.
2) Next, we do an all-AMPs RETRIEVE step from TEST.Color by way of an
all-rows scan with no residual conditions into Spool 2, which is
duplicated on all AMPs. The size of Spool 2 is estimated to be 4
rows. The estimated time for this step is 0.08 seconds.
3) We execute the following steps in parallel.
a) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to TEST.Options. Spool 2 and
TEST.Options are joined using a product join. The result goes
into Spool 3, which is built locally on the AMPs. The size of
Spool 3 is estimated to be 20 rows. The estimated time for this
step is 0.23 seconds.
b) We do an all-AMPs RETRIEVE step from TEST.Size by way of an
all-rows scan with no residual conditions into Spool 4, which is
duplicated on all AMPs. The size of Spool 4 is estimated to be 20
rows. The estimated time for this step is 0.24 seconds.
4) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
all-rows scan, which is joined to Spool 4 (Last Use). Spool 3 and
Spool 4 are joined using a product join. The result goes into Spool
5, which is redistributed by hash code to all AMPs. Then we do a
SORT to order Spool 5 by row hash. The size of Spool 5 is estimated
to be 200 rows. The estimated time for this step is 1.38 seconds.
5) We do a all-AMP JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to TEST.Widgets by way of unique
index # 4 extracting row ids only. Spool 5 and TEST.Widgets are
joined using a nested join. The result goes into Spool 6, which is
redistributed by hash code to all AMPs. Then we do a SORT to order
Spool 6 by row hash. The size of Spool 6 is estimated to be 200
rows. The estimated time for this step is 2.71 seconds.
6) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
all-rows scan, which is joined to TEST.Widgets. Spool 6 and
TEST.Widgets are joined using a row id join. The result goes into
Spool 1, which is built locally on the AMPs. The size of Spool 1 is
estimated to be 200 rows. The estimated time for this step is 5.65
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved in
processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 10.07 seconds.