For each type of query, two summaries of the join plans and estimated execution times are provided—one with and the other without star join optimization.
To be consistent with the EXPLAIN output, if the input table of a join is the result of a previous join, the cost of preparing the input table for the join is included in the cost of performing the previous join. Otherwise, the preparation cost is included into the cost of performing a join.
The total estimated cost for each query is taken directly from the EXPLAIN outputs, which take into account the parallelism of steps.
Costs are relative, and vary depending on the number of AMPs in the configuration. The example costs given are for a system with two AMPs.
The estimated percentage of performance improvement is provided for each example. Remember that these percentages are achieved only when the same join examples are performed under the identical conditions.
Other queries may achieve more or less performance improvement, depending on the join conditions and table statistics, but the general trends are consistently in the same direction.
The following table definitions are used in the examples:
| Dimension (Small) Table Definitions | ||
|---|---|---|
| Table name | Columns | Primary index |
| color | code, description | description |
| size | code, description | description |
| options | code, description | description |
| Fact (Large) Table Definition | ||
|---|---|---|
| Table name | Columns | Cardinality |
| widgets | color, size, options, units, period | 1,000,000 rows |