Poor Query Performance Due to the Lack of Statistics
Symptom
The SQL query performance is poor. Warning information is displayed when EXPLAIN VERBOSE is executed.
Possible Causes
Statistics about the tables or columns involved in the query are not collected. Without statistics, the execution plan generated by the optimizer will be ineffective, and various performance issues may occur, such as nested loop for equi-join, large table broadcast, and continuous increase of cluster CPU usage.
Cause Analysis
- Run explain verbose/explain performance to print the execution plan of a statement.
The alarm indicating that statistics are not collected for some statements exists in the execution plan, and the estimated value of E-rows is small.
- In the preceding example, a warning message is displayed in the printed execution plan, indicating which columns that are used in the execution plan do not contain statistics.
Similar warning messages are displayed in the pg_log log of the CN, and the value of E-rows is much smaller than the actual value.
Handling Procedure
You are advised to periodically execute ANALYZE or execute it immediately after most of the table data is updated.
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
