Netezza planners are very important to improve the performance of the query that you fire onto Netezza performance server.
Types of Netezza Planners
There are different types planners that Netezza performance server uses:
- The Fact Relationship (factrel) planner
- Snowflake planner
- The Star planner
The Fact Relationship (factrel) planner
The Fact Relation Planner is a feature used to prevent large table re-distribution i.e. costly re-distribution or broadcasting of fact data when other alternatives may exist.
Read:
- Data warehouse Star schema and Design
- Analyze and Optimize Netezza Query Performance
- Netezza Best Performance to Improve Performance
- Improve the Query Performance with help of Query Plan
- Data warehouse Snowflake Schema
- Fact Constellation Schema and Design
You may also confirm if the planner is enabled. Execute the following command on your system to check details:
nzsql -c "show enable_factrel_planner"
The factrel planner feature compares the number of rows estimated to survive the RestrictNode operation against a postgres global variable setting, factrel_size_threshold. The default value for this variable is 100M.
You can verify the value of this variable setting on your system by executing below commend:
nzsql -c "show factrel_size_threshold"
You can also change the setting of this variable on your system. Execute following command to change the setting:
nzsql -c "set factrel_size_threshold=100000000"
The Snowflake planner
When the optimizer identifies (using the query construct that you run on the system) that a snowflake data model may be in use, this planner’s feedback is evaluated.
Just like factrel planner, the Snowflake Planner also designates relations as being fact but it uses relative size instead of row size alone. The factrel planner uses the number of rows.
Below are some of the initial setting for snowflake planner:
| Setting Name | Default Value |
| ENABLE_SNOWFLAKE_PLANNER | Yes |
| SNOWFLAKE_MIN_FACT_SIZE | 1M |
| SNOWFLAKE_MIN_RATIO | 10 |
If you are using a table in your nzsql query that is at least larger than the value associated with SNOWFLAKE_MIN_FACT_SIZE and more than x times larger (based on SNOWFLAKE_MIN_RATIO) than the next smallest table, then that relation is designated as being a fact table.
You can even set the minimum fact size using following command:
nzsql -c "set snowflake_min_fact_size=100000000"
The Star planner
When the Optimizer identifies (using the query construct that you run on the system) that a star data model may be in use, this planner’s feedback is evaluated while executing the queries.
You can enable the star planner by using following command:
nzsql -c "enable_star_planner = true”
In some cases, adjusting the parameter num_star_planner_rels from its default value of 8 can be beneficial.
Setting up this variable slightly higher number will improve the performance. But IBM does not recommend setting this value above 10. You can change the default value by using below command:
nzsql -c "num_star_planner_rels = 10”