Product Documentation

Appearance

Auto-Analyzing Tables ​

The ANALYZE command generates updated statistics for the number of distinct values in table columns. This information is useful to the query planner and needs to be kept up-to-date, especially for tables with columns that appear in the WHERE clause of your queries.

The following operations always include an automatic and immediate analyze operation as part of their own post-processing:

  • Bulk load operations with ybload
  • INSERT INTO...SELECT
  • CREATE TABLE AS statements (CTAS)
  • Flush operations

When these operations complete, their target tables are guaranteed to have up-to-date statistics.

The following operations do not include automatic analyze operations:

  • INSERT INTO...VALUES
  • UPDATE
  • DELETE

When these operations complete, the statistics in the target tables may need to be updated. In that case, they wait for the next auto-analyze operation that is scheduled for the system.

By default, all tables are analyzed automatically on a regular schedule. The system checks tables every 5 minutes (300 seconds) and determines which tables require an ANALYZE operation. If at least 10% of a table's rows have changed, a background ANALYZE operation is run.

Tip: To find out when a table was last analyzed, either query the sys.log_analyze view or use the SMC. In the SMC, go to Databases, select the database, double-click the row for the table you want to check, and click Analyze Activity.

Disabling Immediate Analyze Operations ​

If your workload requires frequent small INSERT INTO...SELECT operations into a large fact table and you want to optimize performance, you can disable the immediate ANALYZE operations that happen after each INSERT. To disable these ANALYZE operations, set the ybd_analyze_after_writes configuration parameter to OFF. This parameter defaults to ON.

After changing this parameter, you have to restart the database before the new behavior takes effect. To set the parameter globally, use an ALTER SYSTEM command:

yellowbrick=# alter system set ybd_analyze_after_writes to off;

To set the parameter at the session level, you can use ALTER ROLE. For example:

premdb=# create role insert_select;
CREATE ROLE
premdb=# alter role insert_select set ybd_analyze_after_writes to off;
ALTER ROLE

After a database restart, immediate ANALYZE operations will be disabled for all sessions run by the insert_select user. The advantage of disabling the parameter in this way is that it will remain enabled for other write operations. In general, the default ANALYZE behavior is optimal and is likely not to be the best choice for all workloads.

Parent topic:Managing Tables and Views