Help Center/ GaussDB/ Developer Guide(Distributed_2.x)/ Importing Data/ Analyzing Tables
Updated on 2024-10-14 GMT+08:00
View PDF
Share

Analyzing Tables

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 system catalog PG_STATISTIC.

Analyzing Tables

ANALYZE supports row-store and column-store tables. ANALYZE can also collect statistics on specified columns of a local table. For details on ANALYZE, see ANALYZE | ANALYSE.

  1. Update table statistics.

    Do ANALYZE to the product_info table. 
    1
    		 
    openGauss=# ANALYZE product_info;

    ANALYZE

Automatically Analyzing a Table

GaussDB provides the GUC parameter autovacuum to specify whether to enable the autovacuum function of the database.

If autovacuum is set to on, the system will start the autovacuum thread to automatically analyze tables when the data volume in the table reaches the threshold. This is the autoanalyze function.

  • For an empty table, when the number of rows inserted to it is greater than 50, ANALYZE is automatically triggered.
  • For a table containing data, the threshold is 50 + 10% x reltuples, where reltuples indicates the total number of rows in the table.

The autovacuum function also depends on the following two GUC parameters in addition to autovacuum:

  • track_counts: This parameter must be set to on to enable statistics collection about the database.
  • autovacuum_max_workers: This parameter must be set to a value greater than 0 to specify the maximum number of concurrent autovacuum threads.
  • The autoanalyze function supports the default sampling mode but not percentage sampling.
  • The autoanalyze function does not collect multi-column statistics, which only supports percentage sampling. (The current feature is a lab feature. Contact Huawei technical support before using it.)
  • The autoanalyze function supports row-store and column-store tables but does not support the following: foreign tables, OBS tables (The current feature is a lab feature. Contact Huawei technical support before using it.), temporary tables, unlogged tables, and TOAST tables.
Parent topic: Importing Data