You might know how the Netezza generate statistics works and how to generate the stats on the tables. Read my other post Netezza generate statistics: Best Practices. In this post, we will discuss about the Netezza Just in Time (JIT) statistics.
Netezza Just in Time (JIT) statistics
The Netezza system automatically generates Just in Time (JIT) statistics on user tables to help the optimizer refine planning when you fire the query to Netezza system.
Netezza JIT statistics comes handy when there is a skew in the table, it improves the selectivity estimations. It is also very useful to the query optimizer when there is complex column and joins. JIT statistics are not run on system tables, external tables, or virtual tables (CTE).
Read:
- Netezza Generate Statistics and Best Practices
- nzload Command and its Usage
- Groom in Netezza Tables and Databases with Aginity
- Netezza Skew and How to Avoid it
- Netezza system Tables and Views
- Working with Netezza Zone maps and Best Practices
The system also uses JIT statistics to avoid broadcasting large tables in case if those are estimated to be small based on available statistics in the table.
Information Collected by Netezza Just in Time (JIT) statistics
JIT statistics use sample scan and zone map information to collect several pieces of information:
- The number of rows that are scanned for the target table
- Number of extents that are scanned for the target table
- The number of maximum extents that are scanned for the target table on the data slices with the greatest skew
- Number of rows that are scanned for the target table that apply to each join
- The number of unique values for any target table column that is used in subsequent join or group by processing
When the Netezza system runs the Just in Time (JIT) statistics?
- Tables that contain more than 5,000,000 records.
- Queries that contain at least one column restriction.
- Tables that participate in a join or have an associated materialized view. JIT statistics are integrated with materialized views to ensure that the exact number of extents is scanned.