ANALYZE Table

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.

ANALYZE Table

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.

Update table statistics.

Do ANALYZE to the product_info table.

ANALYZE product_info;

ANALYZE

autoanalyze

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

NOTICE:

• 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.