Best practices for query plan management - Amazon Aurora

Best practices for query plan management

Consider using a plan management style that is either proactive or reactive. These plan management styles contrast in how and when new plans get approved for use.

Proactive plan management to help prevent performance regression

With proactive plan management, you manually approve new plans after you have verified that they are faster. Do this to prevent plan performance regressions. Follow these steps for proactive plan management:

  1. In a development environment, identify the SQL statements that have the greatest impact on performance or system throughput. Then capture the plans for these statements as described in Manually capturing plans for specific SQL statements and Automatically capturing plans.

  2. Export the captured plans from the development environment and import them into the production environment. For more information, see Exporting and importing plans.

  3. In production, run your application and enforce the use of approved managed plans. For more information, see Using managed plans. While the application runs, also add new plans as the optimizer discovers them. For more information, see Automatically capturing plans.

  4. Analyze the unapproved plans and approve those that perform well. For more information, see Evaluating plan performance.

  5. While your application continues to run, the optimizer begins to use the new plans as appropriate.

Reactive plan management to detect and repair performance regression

With reactive plan management, you monitor your application as it runs to detect plans that cause performance regressions. When you detect regressions, you manually reject or fix the bad plans. Follow these steps for reactive plan management:

  1. While your application runs, enforce the use of managed plans and automatically add newly discovered plans as unapproved. For more information, see Using managed plans and Automatically capturing plans.

  2. Monitor your running application for performance regressions.

  3. When you discover a plan regression, set the plan's status to rejected. The next time the optimizer runs the SQL statement, it automatically ignores the rejected plan and uses a different approved plan instead. For more information, see Rejecting or disabling slower plans.

    In some cases, you might prefer to fix a bad plan rather than reject, disable, or delete it. Use the pg_hint_plan extension to experiment with improving a plan. With pg_hint_plan, you use special comments to tell the optimizer to override how it normally creates a plan. For more information, see Fixing plans using pg_hint_plan.