Optimizer Uses Nested Loop Due to the Small Estimated Number of Rows and the Performance Deteriorates

Symptom

The query statement execution is slow and the query result cannot be returned. For SQL statements, the LEFT JOIN statement is used to query data from two or three tables and then the SELECT statement is used to query the result. The execution plan is as follows.

Possible Causes

When the optimizer selects an execution plan, the estimated number of result sets is small. As a result, nested loop is used and the performance deteriorates.

Cause Analysis

1. Check the I/O, memory, and CPU usage. The resource usage of these indicators is not high.
2. Check the thread waiting status of the slow SQL statements.

   According to the thread waiting status, not all threads are waiting for processing on the same DN. Therefore, the intermediate result sets are not skewed on the same DN.

   1	SELECT * FROM pg_thread_wait_status WHERE query_id='149181737656737395';

   

3. Contact O&M personnel to log in to the corresponding instance node and print the stack information about the threads whose waiting status is none.

   After the stack information is repeatedly printed, it is found that the stack changes and does not hang. The problem may be caused by slow performance. In addition, VecNestLoopRuntime exists in the stack. It is determined that the performance deteriorates because the execution plan uses nested loop. This occurs because the statistics are inaccurate and the number of result sets estimated by the optimizer is small.

   gstack 14104

   

   

4. The performance is not improved after ANALYZE is executed on the table.
5. After hints are added to SQL statements to disable the index function and the optimizer forcibly executes hash join, the hint function does not take effect because the hints cannot change the plan in the subquery.
6. After SET enable_indexscan is set to off, the execution plan is changed and HASH LEFT JOIN is used. The execution result of the slow SQL statement is displayed in about 3 seconds, meeting the customer's requirements.

   

Handling Procedure

Set enable_indexscan to off to disable the index function so that the execution plan generated by the optimizer uses hash join instead of nested loop.