ANALYZE | ANALYSE
Function
ANALYZE collects statistics on ordinary tables in a database, and stores the results in the PG_STATISTIC system catalog. The execution plan generator uses these statistics to determine which one is the most effective execution plan.
If no parameter is specified, ANALYZE analyzes each table and partitioned table in the current database. You can also specify table_name, column, and partition_name to limit the analysis to a specified table, column, or partitioned table.
ANALYZE|ANALYSE VERIFY is used to check whether data files of ordinary tables (row-store tables and column-store tables) in a database are damaged.
Precautions
NOTE:
For details about the precautions, see ANALYZE.
Syntax
Table Statistics
{ANALYZE | ANALYSE} [ VERBOSE ] [ NO_WRITE_TO_BINLOG | LOCAL ] TABLE { [schema.]table_name } [, ... ]
Parameter Description
NO_WRITE_TO_BINLOG | LOCAL
It is used only for syntax and has no actual purpose.
NOTE:
For details about the involved parameters, see ANALYZE.
Examples
— Create a table.
openGauss=# CREATE TABLE customer_info
(
WR_RETURNED_DATE_SK INTEGER ,
WR_RETURNED_TIME_SK INTEGER ,
WR_ITEM_SK INTEGER NOT NULL,
WR_REFUNDED_CUSTOMER_SK INTEGER
)
;
— Creates a partitioned table.
openGauss=# CREATE TABLE customer_par
(
WR_RETURNED_DATE_SK INTEGER ,
WR_RETURNED_TIME_SK INTEGER ,
WR_ITEM_SK INTEGER NOT NULL,
WR_REFUNDED_CUSTOMER_SK INTEGER
)
PARTITION BY RANGE(WR_RETURNED_DATE_SK)
(
PARTITION P1 VALUES LESS THAN(2452275),
PARTITION P2 VALUES LESS THAN(2452640),
PARTITION P3 VALUES LESS THAN(2453000),
PARTITION P4 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
— Run ANALYZE to update statistics.
openGauss=# ANALYZE TABLE customer_info, customer_par;
Table | Op | Msg_type | Msg_text
----------------------+---------+----------+----------
public.customer_info | analyze | status | OK
public.customer_par | analyze | status | OK
(2 row)
— Delete a table.
openGauss=# DROP TABLE customer_info;
openGauss=# DROP TABLE customer_par;