Netezza has 3 internal planner
• Fact Relationship
Planner (factrel_planner)
• Snowflake Planner
• Star Planner
In the course of a query plan, the
optimizer may use multiple planners in a particular plan, based on least cost
Fact Relationship Planner
The fact relationship or factrel
planner should be enabled for most star or snowflake schemas. It is enabled in
/nz/data/postgresql.conf
by
adding the following line:
enable_factrel_planner
= true
By default, the factrel planner will
classify any table over 100,000,000 rows as a FACT table. The optimizer will
try and avoid broadcasting or moving
FACT tables as it is almost always much less expensive to broadcast a
smaller dimension table. Depending on
the size of the tables involved, the default setting may be inappropriate.
The default threshold can be adjusted
by setting an override in postgresql.conf. In the example below, we set it
to 50 million rows, which would cause
the optimizer to avoid broadcasting tables of more than 50 million rows.
factrel_size_threshold
= 50000000
Short
Topics in Netezza Administration Common Optimizer Parameters
Snowflake Planner
The snowflake planner is enabled in /nz/data/postgresql.conf by adding the following line:
enable_snowflake_planner
= true
For the snowflake planner to classify
a table as a FACT table, by default it must be at least 1 million rows and at
least 10 times larger than the next
smallest table. In most Netezza environments, the default value of 1 million is
too low, and should be adjusted
higher, e.g.:
snowflake_min_fact_size
= 40000000
The snowflake_min_ratio
parameter
controls the ratio required for a table to be classified as a FACT table.
Star Planner
The star planner is enabled in /nz/data/postgresql.conf by adding the following line:
enable_star_planner
= true
In most cases, no further adjustments
are necessary. In some cases, adjusting the parameter
num_star_planner_rels
from
its default value of 8 can be beneficial. Adjusting this number up slightly
will
cause the optimizer to search more
deeply for a lower cost plan which can help with some queries that are
running out of system resources.
Netezza does not recommend setting this value above 10; in most cases the
default value of 8 is sufficient.
Should a higher value be desired, it can be set as follows:
num_star_planner_rels
= 10
0 comments: