SQL Tuning Process

Procedure

1. 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.
2. Review and modify the table definition.
3. 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. SQL Statement Rewriting Rules describes several optimization methods by rewriting SQL statements.
4. 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.
5. For details about EXPLAIN or EXPLAIN PERFORMANCE, the reason why SQL statements are slowly located, and how to solve this problem, see Advanced SQL Tuning.
6. Specify a join order; join, stream, or scan operations; number of rows in a result; or redistribution skew information to optimize an execution plan, improving query performance. For details, see Hint-based Tuning.
7. To maintain high database performance, you are advised to perform Routinely Maintaining Tables and Routinely Recreating an Index.
8. (Optional) Improve performance by using operators if resources are sufficient in GaussDB(DWS). For details, see SMP Parallel Execution.