ANALYZE | ANALYSE

Function

This syntax gathers statistics on table content within a database. These statistics include both table-level and column-level details. Table-level data is kept in PG_CLASS, while column-level details are stored in PG_STATISTIC. The query planner relies on this information to choose the best execution strategy.

Its key purposes are:

• Optimizing query performance: supplies data distribution insights to the query optimizer for generating efficient plans.
• Enhancing plan accuracy: helps the optimizer better estimate the cost of various query paths.
• Selecting indexes: assists the optimizer in deciding if and which indexes to use.

Precautions

• If no parameters are specified, ANALYZE analyzes each table and partitioned table in the database. You can also specify table_name, column, and partition_name to limit the analysis to a specified table, column, or partitioned table.
• Users who can execute ANALYZE on a specific table include the owner of the table, owner of the database where the table is, users with the ANALYZE permission on the table, users who are granted the gs_role_analyze_any role, and users with the SYSADMIN attribute.
• To collect statistics using percentage sampling, you must have the ANALYZE and SELECT permissions.
• If the enable_analyze_partition parameter is enabled and the table-level incremental_analyze parameter is set for a partitioned table, the ANALYZE statement is executed on partitions lacking statistics or with data changes. The statistics of the partition main table are then generated by combining the partition statistics.

• Only cluster versions 8.1.1 and later support using anonymous blocks, transaction blocks, functions, or stored procedures to perform the ANALYZE operation on an unsharded table.
• However, for analyzing an entire database, the ANALYZE operation of each table is in different transactions. Therefore, the current version does not support the ANALYZE execution for the entire database in anonymous blocks, transaction blocks, functions, or stored procedures.
• Statistics updates of PG_CLASS related columns cannot be rolled back.
• The ANALYZE statistics of global temporary tables in each session are independent. Each session has its own statistics.
• ANALYZE and ANALYSE VERIFY are used to check whether data files of common tables (row-store and column-store tables) in a database are damaged. Currently, this function does not support HDFS tables.
• Most ANALYZE VERIFY operations are used for abnormal scenario detection, and require a release version. Remote read is not triggered in the ANALYZE VERIFY scenario. Therefore, the remote read parameter does not take effect. If the system detects that the page is damaged due to an error in the key system table, the system reports an error and stops the detection.

• If more than 10% of a table's records are added or modified at once, explicitly execute the ANALYZE operation.
• For more information about development and design proposal, see DWS Development and Design Proposal.

Syntax

• Collect statistics information about a table.

  1 2	{ ANALYZE | ANALYSE } [ { VERBOSE | LIGHT | FORCE | PREDICATE } ] [ table_name [ ( column_name [, ...] ) ] ];

• Collect statistics about a partition.

  1 2 3

  { ANALYZE | ANALYSE } [ { VERBOSE | LIGHT | FORCE } ] [ table_name [ ( column_name [, ...] ) ] ] PARTITION ( partition_name ) ;

  • Ordinary partitioned tables support the syntax for collecting statistics on a specific partition, but not the actual function of it. ANALYZE on a specified partition will cause a warning message.
  • Temporary sampling tables cannot be used to collect partition statistics.
  • Multi-column statistics and expression statistics on partitions are not supported.
• Collect statistics about a foreign table.

  1 2	{ ANALYZE | ANALYSE } [ VERBOSE ] { foreign_table_name | FOREIGN TABLES };

Parameter Description

Examples

• Do ANALYZE to update statistics in the customer_info table:

  1	ANALYZE customer_info;

• Do ANALYZE VERBOSE to update statistics and display table information in the customer_info table:

  1 2 3 4 5

  ANALYZE VERBOSE customer_info; INFO: analyzing "cstore.pg_delta_3394584009"(cn_5002 pid=53078) INFO: analyzing "public.customer_info"(cn_5002 pid=53078) INFO: analyzing "public.customer_info" inheritance tree(cn_5002 pid=53078) ANALYZE

• Update the statistics of the name and email columns in the customer_info table.

  1	ANALYZE customer_info (name, email);

• Update the statistics of the customer_info table in lightweight mode.

  1	ANALYZE (LIGHT) customer_info;

• Update the statistics of the customer_info table in forcible mode, even if the table is locked.

  1	ANALYZE (FORCE) customer_info;

• Update only the statistics of predicate columns. Only the predicate columns collected so far are sampled. The statistics are used after the query service is stable.

  1	ANALYZE (PREDICATE) customer_info;

  You can use the pg_stat_get_predicate_columns function to query the predicate columns of a table. The table name in the following example is customer_info. Replace it with the actual table name.

  1	SELECT pr.attnum,pa.attname FROM pg_catalog.pg_stat_get_predicate_columns('customer_info'::regclass) pr LEFT JOIN pg_attribute pa ON pa.attrelid='customer_info'::regclass AND pa.attnum = pr.attnum;