Updated on 2025-02-27 GMT+08:00

Updating Statistics

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

Background

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

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.

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.