Netezza optimizer and query plans

IBM® Netezza® uses a cost-based optimizer to determine the best method for scan and join operations, join order, and data movement between SPUs (that is, redistribute or broadcast operations).

The system might redistribute data for joins, grouping aggregates, create tables, or when loading. Decisions about redistribution are made by the planner and are based on costs like expected table sizes. (The planner tries to avoid redistributing large tables because of the performance impact.)

The optimizer can also dynamically rewrite queries to improve query performance. Many data warehouses use BI applications that generate SQL that is designed to run on multiple vendors' databases. The portability of these applications is often at the expense of efficient SQL. The SQL that the application generates does not take advantage of the vendor-specific enhancements, capabilities, or strengths. Hence, the optimizer might rewrite these queries to improve query performance.