Help Center> GaussDB> Centralized_2.x> Importing Data> Analyzing Tables
Updated on 2023-10-23 GMT+08:00
View PDF

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 about specified columns of a local table. For details on ANALYZE, see ANALYZE | ANALYSE.

  1. Update table statistics.

    Take the product_info table as an example. The ANALYZE command is as follows: 
    1
    		 
    openGauss=# ANALYZE product_info;

    ANALYZE

Automatically Analyzing Tables

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 autoanalyze function supports row-store and column-store tables and does not support foreign tables, temporary tables, unlogged tables, and TOAST tables.
Parent topic: Importing Data