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

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

   

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