Query plan management

Changes to statistics, constraints, environment settings, query parameter bindings, and upgrades to the PostgreSQL database engine can all cause query plan regression. Query plan regression is when the optimizer chooses a less optimal plan than it did before a given change to the database environment.

In Amazon Aurora PostgreSQL-Compatible Edition, the query plan management (QPM) feature is designed to ensure plan adaptability and stability, regardless of database environment changes that might cause query plan regression. QPM provides some control over the optimizer. Using QPM, you can manage the query execution plan generated by the optimizer for your SQL queries. The query execution plan forces the optimizer to choose from your approved plans for critical queries, to optimize their performance.

Enterprises commonly deploy applications and databases globally or maintain several environments for each application database, such as development, QA, staging, preproduction, testing, and production. Maintaining the query execution plans for each database, in each environment, and across all AWS Regions can be complex and time-consuming. QPM can export and import Amazon Aurora PostgreSQL-Compatible managed plans from one database to another. This helps you manage the query execution plan centrally and deploy databases globally. You can use this feature to investigate a set of plans in a preproduction database, verify that they perform well, and then load them into production environment.

QPM also provides several other benefits. For example, you can use QPM to improve execution plans that can't be changed in applications or when hints can't be added to the statement. QPM also automatically detects new, minimum-cost plans that the optimizer discovers, so you can continue to optimize costs in addition to performance.

We recommend that you enable QPM. When QPM is enabled, the optimizer uses the minimum-cost plan that you have approved. This helps prevent regression and reduces the time required to manage and fix suboptimal plans.

There are two different approaches for using the QPM feature: proactive and reactive. The proactive approach is designed to help prevent performance regression from occurring, and the reactive approach is designed to detect and repair performance regressions after they occur. You can select your approach on a per-query basis. For complex queries that might be prone to regression or for business-critical queries, you can use a proactive approach and approve the optimal plans for those queries. If other queries experience query plan regression during runtime, you can use a reactive approach. When you detect the regression, change the status of that plan to rejected so that the optimizer chooses a different, approved plan. For more information, see Best practices for Aurora PostgreSQL query plan management (Aurora documentation).

How does query plan management work?

Plans are assigned one of the following statuses: approved, unapproved, preferred, or rejected. The optimizer sets the first generated plan for each managed statement to approved and then sets the status of additional plans to unapproved. Later, you can assess the unapproved plans and change their status to approved, preferred, or rejected. For more information, see Understanding Aurora PostgreSQL query plan management (Aurora documentation).

Managed plans can be captured either manually or automatically. The most common approach is to automatically capture plans for all statements that run two or more times. However, you can also manually capture plans for a specific set of statements. For more information, see Capturing Aurora PostgreSQL execution plans (Aurora documentation).

After you have set up a managed plan, the optimizer uses the minimum-cost preferred or approved plan that is valid and enabled for each managed statement. For detailed information, see How the optimizer chooses which plan to run (Aurora documentation).

For instructions about configure the QPM feature in Amazon Aurora PostgreSQL-Compatible, see Managing query execution plans for Aurora PostgreSQL (Aurora documentation).

Limitations

To use QPM, you must make sure that you meet the requirements for supported SQL statements, your statements don't reference system relations, and your DB instance class has sufficient vCPUs. For more information, see Supported SQL statements and Query plan management limitations (Aurora documentation).

Use cases for query plan management