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 analyze operation as part of their own post-processing:
  • Bulk load operations with ybload
  • 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
  • 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.