Analyzing a Table

Updated on 2024-05-07 GMT+08:00

View PDF

The execution plan generator needs to use table statistics to generate the most effective query execution plan to improve query performance. After data is imported, you are advised to run the ANALYZE statement to update table statistics. The statistics are stored in the PG_STATISTIC system catalog.

Analyzing a Table

ANALYZE supports row-store, column-store, HDFS, and OBS tables in ORC or CARBONDATA format. ANALYZE can also collect statistics about specified columns of a local table.

Do ANALYZE to the product_info table.

     
        ANALYZE product_info;

Automatically Analyzing a Table

GaussDB(DWS) provides automatic table analysis for the following two scenarios.

If ANALYZE is triggered because a query contains a table that has no statistics or a table whose amount of data modification reaches the threshold, and the execution plan does not use Fast Query Shipping (FQS), the GUC parameter autoanalyze is used to control the automatic collection of table statistics. In this case, a better execution plan is generated based on the collected statistics.

If autovacuum is set to on, the system periodically starts the autovacuum thread and automatically collects statistics on the tables whose amount of data modification reaches the threshold for triggering ANALYZE in the background.

**Table 1** Automatically Analyzing a Table
Trigger Mode	Trigger Condition	Scaling Frequency	Control Parameter	Remarks
Synchronization	Statistics are completely missing.	At each query execution	autoanalyze	Statistics are cleared when the primary table is truncated.
Synchronization	The amount of modified data reaches the ANALYZE threshold.	At each query execution	autoanalyze	ANALYZE is triggered before the optimal plan is determined.
Asynchronization	The amount of modified data reaches the ANALYZE threshold.	Autovacuum thread polling check	autovacuum_mode, autovacuum_naptime	The lock times out in 2 seconds, and the execution times out in 5 minutes.

NOTICE:

The autoanalyze function supports only the default sampling mode and not the percentage sampling mode.
The autoanalyze function does not collect multi-column statistics, which only supports percentage sampling.
AUTOANALYZE is triggered because a query contains a table that has no statistics or a table whose amount of data modification reaches the threshold. In this case, AUTOANALYZE cannot be triggered for foreign tables or temporary tables with the ON COMMIT [DELETE ROWS | DROP] option.
If the amount of data modification reaches the threshold for triggering ANALYZE, the amount of data modification exceeds autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples. reltuples indicates the estimated number of rows in the table recorded in pg_class.
The autoanalyze function triggered by a scheduled autovacuum thread supports only row-store and column-store tables. It does not support foreign tables, HDFS tables, OBS foreign tables, temporary tables, unlogged tables, or toast tables.
When ANALYZE is triggered during a query, a level-4 lock is added to all partitions in the partitioned table. The lock is released only after the transaction containing the query is committed. The level-4 lock does not block adding, deletion, modification, and query operations, but blocks partition modification operations such as TRUNCATE. You can set object_mtime_record_mode to disable_partition to release the partition locks in advance.
The autovacuum function also depends on the following two GUC parameters in addition to autovacuum:
- track_counts must be set to on to enable statistics collection about the database.
- autovacuum_max_workers must be set to a value greater than 0 to specify the maximum number of concurrent autovacuum threads.

Parent topic: Other Operations

Previous topic: Checking for Data Skew

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot