Updating Statistics¶
In a database, statistics indicate the source data of a plan generated by a planner. If statistics are unavailable 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 system table PG_STATISTIC. Then, the query optimizer uses the statistics to work out the most efficient execution plan.
After executing batch insertion and deletions, you are advised to run 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.
Generating Statistics¶
Run the following commands to update the statistics about a table or the entire database:
ANALYZE tablename; --Update statistics about a table.
ANALYZE; ---Update statistics about the entire database.
Run the following statements to perform statistics-related operations on multiple columns:
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.
Important
After the statistics are declared for multiple columns by running 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 statement.
Use EXPLAIN to show the execution plan of each SQL statement. If rows=10 (the default value, probably indicating the table has not been analyzed) is displayed in the SEQ SCAN output of a table, run the ANALYZE statement for this table.
Improving the Quality of Statistics¶
ANALYZE samples data from a table based on the random sampling algorithm and calculates table data features based on the samples. The number of samples can be specified by the default_statistics_target parameter. The value of default_statistics_target ranges from -100 to 10000, and the default value is 100.
If the value of default_statistics_target is greater than 0, the number of samples is 300 x default_statistics_target. This means a larger value of default_statistics_target indicates a larger number of samples, larger memory space occupied by samples, and longer time required for calculating statistics.
If the value of default_statistics_target is smaller than 0, the number of samples is default_statistics_target/100 x Total number of rows in the table. A smaller value of default_statistics_target indicates a larger number of samples. If the value of default_statistics_target is smaller than 0, the sampled data is written to the disk. In this case, the samples do not occupy memory. However, the calculation still takes a long time because the sample size is too large.
When default_statistics_target < 0, the actual number of samples is default_statistics_target/100 x Total number of rows in the table. Therefore, this sampling mode is also called percentage sampling.
Automatic Statistics Collection¶
When the parameter autoanalyze is enabled, if the query statement reaches the optimizer and finds that there are no statistics, statistics collection will be automatically triggered to meet the optimizer's requirements.
Note: Automatic statistics collection is triggered only for complex query SQL statements that are sensitive to statistics (such as multi-table association). Simple queries (such as single-point query and single-table aggregation) do not trigger automatic statistics collection.