Analyzing a 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 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. For details about ANALYZE, see ANALYZE | ANALYSE.
- Update table statistics.Do ANALYZE to the product_info table.
Automatically Analyzing a Table
GaussDB(DWS) 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 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.
- The autoanalyze function supports row-store and column-store tables and does not support the following: foreign tables, HDFS tables, OBS tables, temporary tables, unlogged tables, and toast tables.