Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Optimization/ Updating Statistics
Updated on 2024-05-21 GMT+08:00
View PDF
Share

Updating Statistics

In a database, statistics indicate the source data of a plan generated by an optimizer. If no statistics are available or out of date, the execution plan may seriously deteriorate, leading to low performance.

Context

The ANALYZE statement collects statistic about table contents in databases, which will be stored in the PG_STATISTIC system catalog. Then, the query optimizer uses the statistics to work out the most efficient execution plan.

After executing batch insertions and deletions, you are advised to execute the ANALYZE statement on the table or the entire library to update statistics. By default, 30,000 rows of statistics are sampled. That is, the default value of the GUC parameter default_statistics_target is 100. If the total number of rows in the table exceeds 1,600,000, you are advised to set default_statistics_target to -2, indicating that 2% of the statistics are collected.

For an intermediate table generated during the execution of a batch script or stored procedure, you also need to run the ANALYZE statement.

If there are multiple inter-related columns in a table and the conditions or grouping operations based on these columns are involved in the query, collect statistics about these columns so that the query optimizer can accurately estimate the number of rows and generate an effective execution plan.

If the table has a GSI, perform ANALYZE on the base table first and then on the GSI.

Procedure

Run the following commands to update the statistics about a table or the entire database:

1
2
 
ANALYZE tablename;                        -- Update statistics about a table.
ANALYZE;                                  -- Update statistics about the entire database.

Run the following command to update statistics about the GSI on the table:

1
 
ANALYZE GLOBAL INDEX indexname FOR TABLE tablename;

To update the statistics about the GSI, perform ANALYZE on the base table first.

Run the following statements to perform statistics-related operations on multiple columns:

1
2
3
4
5
6
 
ANALYZE tablename ((column_1, column_2));                       -- Collect statistics about column_1 and column_2 of tablename.

ALTER TABLE tablename ADD STATISTICS ((column_1, column_2));    -- Declare statistics about column_1 and column_2 of tablename.
ANALYZE tablename;                                               -- Collect statistics about one or more columns.

ALTER TABLE tablename DELETE STATISTICS ((column_1, column_2)); --Delete statistics about column_1 and column_2 of tablename or their statistics declaration.

After the statistics are declared for multiple columns by executing the ALTER TABLE tablename ADD STATISTICS statement, the system collects the statistics about these columns next time ANALYZE is performed on the table or the entire database.

To collect the statistics, run the ANALYZE command.

Use EXPLAIN to show the execution plan of each SQL statement. If rows is set to 10 (the default value, probably indicating that the table has not been analyzed) is displayed in the SEQ SCAN output of a table, execute the ANALYZE statement for this table.

Parent topic: SQL Optimization