Understanding the GENERATE STATISTICS statement

Answer

There has been a change in the way the Netezza system collects the statistics it uses with the cost-based optimizer, the tool that calculates the most efficient plan for a query. Prior to 4.6, the GENERATE STATISTICS statement would generate full statistics on the target object, regardless of size. As of Netezza 4.6, the system will review the table's size and determine which type of statistics need to be collected--full, express, or dispersion. The system gathers full statistics on small tables and express statistics for medium tables. The JIT (just in time) dispersion feature is used on very large tables. Determining which size category a table belongs to is described below.

Understanding Statistic Gathering Techniques Based on Table Size

Release 4.6 introduced the JIT dispersion feature for large tables. JIT dispersion allows the optimizer to collect real-time information about column cardinality using sample scans rather than dispersion values calculated at some fixed interval. These sample scans (dispersion statistics) are far more accurate, as they take into account any restrictions applied to the table.

JIT dispersion applies only to large tables, those exceeding a user-configurable size. If a table is larger than the value configured with the Postgres setting JIT_DISP_MIN_ROWS, JIT dispersion is enacted. The default value for the setting is 500 million rows. You can review the current setting on your system by issuing the following command:

Medium and Small Tables

Full statistics are now only collected for small tables. The system collects full statistics on a table whose rows number fewer than 10 times the total number of SPUs or less than the Postgres settingSAMPLE_STATS_MIN_ROWS (which defaults to 0). To modify this, you can set it at the session level:

set sample_stats_min_Rows = 1000000;

If you would prefer to make this a system-wide setting, add the following line to/nz/data/postgresql.conf:

Understanding Statistic Types

New functionality aside, there are differences between full, dispersion, and express statistics, both in the way they are collected and the effect on the system. For example, express statistics collection is about four times faster than full. This is due primarily to the manner in which the cardinality of columns gets calculated. Dispersion statistics are not as accurate as full statistics, but are close approximations. 

The table below summarizes the GENERATE STATISTICS behavior for each statistics type.