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.

MogDB=# 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.

MogDB=# 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.

MogDB=# 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.

MogDB=# DROP TABLE customer_info;
MogDB=# DROP TABLE customer_par;

Helpful Links

ANALYZE