Updated at: Dec 30, 2020 GMT+08:00
You can analyze slow SQL statements to optimize them.
- Collect all table statistics associated with the SQL statements. In a database, statistics indicate the source data of a plan generated by a planner. If statistics are unavailable or out of date, the execution plan may seriously deteriorate, leading to low performance. According to past experience, about 10% performance problem occurred because no statistics are collected. For details, see Updating Statistics.
- View the execution plan to find out the cause. If the SQL statements have been running for a long period of time and not ended, run the EXPLAIN command to view the execution plan and then locate the fault. If the SQL statement has been executed, run the EXPLAIN ANALYZE or EXPLAIN PERFORMANCE command to check the execution plan and actual running situation and then accurately locate the fault. For details about the execution plan, see SQL Execution Plan.
- Reviewing and Modifying a Table Definition.
- For details about EXPLAIN or EXPLAIN PERFORMANCE, the reason why SQL statements are slowly located, and how to solve this problem, see Typical SQL Optimization Methods.
- Generally, some SQL statements can be converted to its equivalent statements in all or certain scenarios by rewriting queries. SQL statements are simpler after they are rewritten. Some execution steps can be simplified to improve the performance. The query rewriting method is universal in all databases. Excellent Practices for SQL Queries describes several common methods for tuning by rewriting SQL statements.
- Specify a join order; join, stream, or scan operations; number of rows in a result; or redistribution skew information to tune an execution plan, improving query performance. For details, see Hint-based Tuning.
- Routinely maintain tables and rebuild indexes.
- (Optional) Improve performance by using operators if resources are sufficient in GaussDB(DWS). For details, see Recommended Suggestions for SMP.