Parallel Awareness
Among the demographics and statistics available for the Optimizer to use are information about the number of AMPs per node in the system, table cardinalities, and how many of those rows are likely to be touched for a particular operation. From this, the Optimizer knows the number of rows each AMP needs to manage, which it can use to manage file system I/Os most effectively.
The Optimizer determines an AMP‑to‑CPU ratio that permits it to compare the number of AMPs per node as a function of available CPU power to build the most efficient query plan possible. For example, operations that are more CPU‑intensive, like a product join, can be avoided and less CPU‑intensive operations substituted for them when possible. This means that the Optimizer will select a product join less frequently in a configuration that has less powerful or fewer CPUs than for a system that has a larger AMP‑to‑CPU ratio or more powerful CPUs.
Another example of the parallel awareness of the Optimizer is deciding whether to redistribute rows or not for a join operation. The Optimizer selects row redistribution less often in a configuration with many AMPs than it does for a system with few AMPs because row redistribution is an activity that impinges heavily on BYNET traffic as well as being a CPU‑intensive operation.