Best practices for Aurora PostgreSQL query plan management - Amazon Aurora

Best practices for Aurora PostgreSQL query plan management

Query plan management lets you control how and when query execution plans change. As a DBA, your main goals when using QPM include preventing regressions when there are changes to your database, and controlling whether to allow the optimizer to use a new plan. In the following, you can find some recommended best practices for using query plan management. Proactive and reactive plan management approaches differ in how and when new plans get approved for use.

Proactive plan management to help prevent performance regression

To prevent plan performance regressions from occurring, you evolve plan baselines by running a procedure that compares the performance of newly discovered plans to the performance of the existing baseline of Approved plans, and then automatically approves the fastest set of plans as the new baseline. In this way, the baseline of plans improves over time as faster plans are discovered.

  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 Aurora PostgreSQL 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.

Ensuring plan stability after a major version upgrade

Each major version of PostgreSQL includes enhancements and changes to the query optimizer that are designed to improve performance. However, your workload may include queries that result in a worse performing plan in the new version. You can use the query plan manager to ensure plan stability after a major version upgrade.

The optimizer always uses the minimum cost plan, even if more than one approved plans exist. That means that you can have multiple approved plans for each statement in your workload prior to upgrading. After the upgrade, the optimizer will use only one of the approved plans, even if the changes in the new major version might lead to a different plan being created.

After upgrading, you can use the evolve_plan_baselines function to compare plan performance before and after the upgrade using your query parameter bindings. The following steps assume that you have been using approved managed plans in your production environment, as detailed in Using Aurora PostgreSQL managed plans.

  1. Before upgrading, run your application with the query plan manager running. While the application runs, add new plans as the optimizer discovers them. For more information, see Automatically capturing plans.

  2. Evaluate each plan's performance. For more information, see Evaluating plan performance.

  3. After upgrading, analyze your approved plans again using the evolve_plan_baselines function. Compare performance before and after using your query parameter bindings. If the new plan is fast, you can add it to your approved plans. If it's faster than another plan for the same parameter bindings, then you can mark the slower plan as Rejected.

    For more information, see Approving better plans. For reference information about this function, see apg_plan_mgmt.evolve_plan_baselines.

For more information, see Ensuring consistent performance after major version upgrades with Amazon Aurora PostgreSQL-Compatible Edition Query Plan Management.

Reactive plan management to detect and repair performance regressions

By monitoring your application as it runs, you can detect plans that cause performance regressions. When you detect regressions, you manually reject or fix the bad plans by following these steps:

  1. While your application runs, enforce the use of managed plans and automatically add newly discovered plans as unapproved. For more information, see Using Aurora PostgreSQL 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.