Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

ANALYZE

Updates table statistics for use by the query planner.

Syntax

Copy
ANALYZE [ VERBOSE ] [ [ table_name ] [ ( column_name [, ...] ) ] ]

Parameters

VERBOSE

Returns progress information messages about the ANALYZE operation. This option is useful when you don't specify a table.

table_name

You can analyze specific tables, including temporary tables. You can qualify the table with its schema name. You can optionally specify a table_name to analyze a single table. You cannot specify more than one table_name with a single ANALYZE table_name statement. If you don't specify a table_name, all of the tables in the currently connected database are analyzed, including the persistent tables in the system catalog. Amazon Redshift skips analyzing a table if the percentage of rows that have changed since the last ANALYZE is lower than the analyze threshold. For more information, see Analyze Threshold.

You don't need to analyze Amazon Redshift system tables (STL and STV tables).

column_name

If you specify a table_name, you can also specify one or more columns in the table (as a column-separated list within parentheses).

Usage Notes

Amazon Redshift automatically runs ANALYZE on tables that you create with the following commands:

  • CREATE TABLE AS

  • CREATE TEMP TABLE AS

  • SELECT INTO

You do not need to run the ANALYZE command on these tables when they are first created. If you modify them, you should analyze them in the same way as other tables.

Analyze Threshold

To reduce processing time and improve overall system performance, Amazon Redshift skips analyzing a table if the percentage of rows that have changed since the last ANALYZE command run is lower than the analyze threshold specified by the analyze_threshold_percent parameter. By default, analyze_threshold_percent is 10. To change analyze_threshold_percent for the current session, execute the SET command. The following example changes analyze_threshold_percent to 20 percent.

Copy
set analyze_threshold_percent to 20;

To analyze tables when only a small number of rows have changed, set analyze_threshold_percent to an arbitralily small number. For example, if you set analyze_threshold_percent to 0.01, then a table with 100,000,000 rows will not be skipped if at least 10,000 rows have changed.

Copy
set analyze_threshold_percent to 0.01;

If ANALYZE skips a table because it doesn't meet the analyze threshold, Amazon Redshift returns the following message.

Copy
ANALYZE SKIP

To analyze all tables even if no rows have changed, set analyze_threshold_percent to 0.

To view the results of ANALYZE operations, query the STL_ANALYZE system table.

For more information about analyzing tables, see Analyzing Tables.

Examples

Analyze all of the tables in the TICKIT database and return progress information.

Copy
analyze verbose;

Analyze the LISTING table only.

Copy
analyze listing;

Analyze the VENUEID and VENUENAME columns in the VENUE table.

Copy
analyze venue(venueid, venuename);