How it works...

An explain plan is derived from the PE, steps, or so-called optimized plans given to AMPS to execute the query. Each highlighted part here represents an operation carried by the optimizer: 

• Estimations: Big estimated times and row counts. This is usually due to missing/stale statistics.
• Row Retrieval: Check for partition elimination on large tables (most fact tables):
  • Mismatched data types and character sets (Unicode versus Latin) will prevent partition elimination
  • Using a function on a partitioning column may prevent partition elimination
  • An all-rows scan on big tables
  • Primary index retrieval by way of the primary index
• Joins: Steps with a translate in joins:
  • Mismatched data type or character set on join columns—rows hash to different AMPs causing costly joins
  • Product joins with a high number of estimated rows
  • Sliding window-based join, joining PPI and non-PPI tables
• Statistics: Steps with estimated row count = number of AMPs:
  • Prevents the optimizer from using statistics to create an optimal execution plan