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.
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 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=10 (the default value, probably indicating that the table has not been analyzed) is displayed in the SEQ SCAN output of a table, run the ANALYZE statement for this table.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot