Updates table statistics for use by the query planner.
ANALYZE [ VERBOSE ] [ [ table_name ] [ ( column_name [, ...] ) ] ]
Returns progress information messages about the ANALYZE operation. This option is useful when you don't specify a table.
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).
If you specify a table_name, you can also specify one or more columns in the table (as a column-separated list within parentheses).
Amazon Redshift automatically runs ANALYZE on tables that you create with the following commands:
CREATE TABLE AS
CREATE TEMP TABLE AS
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.
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.
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.
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.
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.
Analyze all of the tables in the TICKIT database and return progress information.
Analyze the LISTING table only.
Analyze the VENUEID and VENUENAME columns in the VENUE table.
analyze venue(venueid, venuename);