Reviewing the Plan Change Scenario

The optimizer generates the optimal execution plan based on the cost model and statistics of related tables. Therefore, changes in statistics of related tables or parameter modifications of the cost model affect generation of the optimal plan. In addition, many operations of production services may cause the plan cache to become invalid and rebuild, such as operations related to statistics update (VACUUM, ANALYZE, AUTOVACUUM, and AUTOANALYZE), DDL operations such as index rebuilding, SQL patch application, and other scenarios that cause the cache to become invalid. If the optimal plan generated based on the cost during the period is different from the previous one, a plan change occurs. Therefore, plan change is a common phenomenon in production services.

Generally, positive plan changes do not need to be concerned. Pay special attention to the scenarios where the performance deteriorates and the CPU usage increases due to plan changes. This is usually accompanied by database environment variable changes, such as statistics update, table structure change, or database version upgrade.

In GaussDB, plan changes may occur in the following scenarios. Therefore, you need to review the scenarios and apply precise optimization methods (such as hints).

• Generic plan rebuilding: When the service is running stably, the generic plan is usually executed, indicating that the plan is stable and the optimizer does not need to optimize the SQL statement. In some cases, the generic plan may be invalid and needs to be rebuilt.
  • If you modify GUC parameters, the generic plan will be rebuilt. If you modify optimizer parameters, such as query_dop, plan changes will occur. Do not modify optimizer parameters unless there are special requirements for stable services.
  • DDL operations are performed on the base table involved in the query. The DDL operations on the table cause the rebuilding of the generic plan, and the addition, deletion, and modification of indexes affect the plan selection. To optimize an index, ensure that the index is valid and update the index statistics in a timely manner to select a better plan.
  • Perform IUD operations on tables and manually or automatically collect statistics. Changes in statistics cause generic plan rebuilding. Generally, if statistics are collected in a timely manner, performance deterioration will not occur even if the plan changes. A common service scenario is that a large number of IUD operations are performed on the base table involved in the query but the statistics are not updated in a timely manner. In this case, you need to manually collect statistics or review the triggering of automatic statistics collection.
• Intelligent optimizer: The generic plan cannot meet service requirements in some scenarios. For example, in data skew scenarios, the optimal plan varies depending on data. The intelligent optimizer generates multiple alternative generic plans and selects the optimal plan for execution in real time based on the query feedback. The intelligent optimizer is related to the service execution sequence. If the intelligent optimizer cannot meet the requirements, you are advised to use hints or SQL patches to optimize key service statements.