The IBM PureData System for Analytics, powered by IBM Netezza, is a data warehouse appliance that features a purpose-built analytics engine. Netezza generates a query plan for all queries that run on the system. The query plan describes the optimal execution query path as determined by the query optimizer component, which relies on statistics that are available about the database objects that are involved in the query.
The Netezza query optimizer is a cost-based optimizer; that is, it determines the cost for the various execution alternatives and chooses the path with the least cost as the optimal execution path for a particular query. The execution plan with the lowest cost among all candidate plans is considered the most efficient. However, in certain situations the Netezza optimizer might not provide the best query plan that leads to the best query performance.
In general, the default optimizer settings are adequate for most operations. However, in some cases you might have information that is unknown to the optimizer or must tune the optimizer for a specific type of statement or workload. Similarly, the optimizer might select different execution plans for some SQL statements when the data volume increases. In such cases, influencing the optimizer might provide better performance if performance problems arise.
You can influence the optimizer by using Postgres configuration parameters at the session level to address single query performance. You also can set the parameters globally by modifying the postgresql.conf file.
Figure 1 shows an example of the use of Postgres configuration parameters at the session level, which has precedence over the global settings in the postgresql.conf file. In this example, the client uses the ENABLE_FACTREL_PLANNER parameter to influence the optimizer.
Figure 1. Use of Postgres configuration parameters at the session level to influence the optimizer
To share my experience working with clients and helping them improve their query performance, I wrote the IBM Redbooks Analytics Support Web Doc
IBM PureData System for Analytics: Improving Query Performance by Tuning Netezza Optimizer Through Postgres Configuration, TIPS 1341.
This document describes some Postgres parameters that can be used to influence the Netezza query optimizer and improve the query performance.
Sanjit Chakraborty is a Software Engineer in the IBM PureData Analytics organization; he is a member of the Advanced Problem Diagnostics (APD) team. Sanjit's current assignment is on IBM Netezza technical support and he is responsible for handling client critical situations and developing support tools for use by the technical support organization. Sanjit has over 20 years of experience in the IT industry. He has extensive experience in various database products.