Product Documentation

Appearance

All platforms

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 Yellowbrick Manager. In Yellowbrick Manager, go to Databases and select the specific database, schema, and table. On the Details screen, go to Analyze Activity.

In general, the default ANALYZE behavior is optimal and is likely to be the best choice for most workloads. However, the following sections describe two distinct use cases that may require immediate ANALYZE operations to be disabled.

Disabling Immediate ANALYZE Operations for INSERTs ​

If your workload requires frequent small INSERT INTO...SELECT operations into a large fact table and you want to optimize performance, see the ybd_analyze_after_writes configuration parameter.

Disabling Immediate ANALYZE Operations for Bulk Loads ​

If your workload consists of a large number of continuous trickle-feed loads using ybload and you want to optimize performance, see the ybd_analyze_after_loads configuration parameter.