Netezza Performance Server optimizer and query plans

Netezza Performance Server for Cloud Pak for Data and Netezza Performance Server for Cloud Pak for Data System 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.)

Note: Review the plan if you want to know whether the planner redistributed or broadcast your data. The EXPLAIN VERBOSE command displays the text: download (distribute or broadcast).

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.