With join optimization, the following join plan is generated when the collection of join columns (color, size, and options) makes up a nonunique 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 | 4.43 |
| Spool 1: Nested Join | Duplicated 5, index widgets | 22.73 |
Completion Time
The total estimated execution time is 27.26 seconds.
The estimated performance improvement factor is 246.
EXPLAIN Output for Optimized Join Plan
Part of the EXPLAIN output for this optimized join plan follows.
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 duplicated on all AMPs. The size of Spool 5 is estimated to be 400 rows. The estimated time for this step is 4.19 seconds. 5) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to TEST.Widgets by way of index # 4. Spool 5 and TEST.Widgets are joined using a nested 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 22.73 seconds.