Here, "cost" is the CPU, the I/Os, the joins, the number of estimated rows, AMPs, and so on. The optimizer evaluates the costs of all reasonable execution plans, and the best choice is used.
The following is a list of what should be optimized:
- Kinds of joins
- Access paths
- Row relocations
- Ordering of joins
As in a maze, you only have one perfect path from entry to exit; the optimizer generates an execution plan for a query. The numbers of rows and other demographics of a table, and the column parts of the join conditions, help the optimizer decide which table in the join will be distributed or duplicated, what kind of join will be used, and, in the case of multi-table joins, the order of joins.
