Overview of Aurora PostgreSQL query plan management
Aurora PostgreSQL query plan management is designed to ensure plan stability regardless of changes to the database that might cause query plan regression. Query plan regression occurs when the optimizer chooses a sub-optimal plan for a given SQL statement after system or database changes. Changes to statistics, constraints, environment settings, query parameter bindings, and upgrades to the PostgreSQL database engine can all cause plan regression.
With Aurora PostgreSQL query plan management, you can control how and when query execution plans change. The benefits of Aurora PostgreSQL query plan management include the following.
Improve plan stability by forcing the optimizer to choose from a small number of known, good plans.
Optimize plans centrally and then distribute the best plans globally.
Identify indexes that aren't used and assess the impact of creating or dropping an index.
Automatically detect a new minimum-cost plan discovered by the optimizer.
Try new optimizer features with less risk, because you can choose to approve only the plan changes that improve performance.
You can use the tools provided by query plan management proactively, to specify the best plan for certain queries. Or you can use query plan management to react to changing circumstances and avoid plan regressions. For more information, see Best practices for Aurora PostgreSQL query plan management.
Topics
Supported SQL statements
Query plan management supports the following types of SQL statements.
Any SELECT, INSERT, UPDATE, or DELETE statement, regardless of complexity.
Prepared statements. For more information, see PREPARE
in the PostgreSQL documentation. Dynamic statements, including those run in immediate-mode. For more information, see Dynamic SQL
and EXECUTE IMMEDIATE in PostgreSQL documentation. Embedded SQL commands and statements. For more information, see Embedded SQL Commands
in the PostgreSQL documentation. Statements inside named functions. For more information, see CREATE FUNCTION
in the PostgreSQL documentation. Statements containing temp tables.
Statements inside procedures and DO-blocks.
You can use query plan management with EXPLAIN
in manual mode to
capture a plan without actually running it. For more information, see
Analyzing the optimizer's chosen plan. To learn
more about query plan management's modes (manual, automatic), see
Capturing Aurora PostgreSQL execution
plans.
Aurora PostgreSQL query plan management supports all PostgreSQL language features,
including partitioned tables, inheritance, row-level security, and recursive common
table expressions (CTEs). To learn more about these PostgreSQL language features,
see Table Partitioning
For information about different versions of the Aurora PostgreSQL query plan management feature, see Aurora PostgreSQL apg_plan_mgmt extension versions in the Release Notes for Aurora PostgreSQL.
Query plan management limitations
The current release of Aurora PostgreSQL query plan management has the following limitations.
Plans aren't captured for statements that reference system relations – Statements that reference system relations, such as
pg_class
, aren't captured. This is by design, to prevent a large number of system-generated plans that are used internally from being captured. This also applies to system tables inside views.Larger DB instance class might be needed for your Aurora PostgreSQL DB cluster – Depending on the workload, query plan management might need a DB instance class that has more than 2 vCPUs. The number of
max_worker_processes
is limited by the DB instance class size. The number ofmax_worker_processes
provided by a 2-vCPU DB instance class (db.t3.medium, for example) might not be sufficient for a given workload. We recommend that you choose a DB instance class with more than 2 vCPUs for your Aurora PostgreSQL DB cluster if you use query plan managment.When the DB instance class can't support the workload, query plan management raises an error message such as the following.
WARNING: could not register plan insert background process HINT: You may need to increase max_worker_processes.
In this case, you should scale up your Aurora PostgreSQL DB cluster to a DB instance class size with more memory. For more information, see Supported DB engines for DB instance classes.
-
Plans already stored in sessions aren't affected – Query plan management provides a way to influence query plans without changing the application code. However, when a generic plan is already stored in an existing session and if you want to change its query plan, then you must first set
plan_cache_mode
toforce_custom_plan
in the DB cluster parameter group. -
queryid
inapg_plan_mgmt.dba_plans
andpg_stat_statements
can diverge when:Objects are dropped and recreated after storing in apg_plan_mgmt.dba_plans.
apg_plan_mgmt.plans
table is imported from another cluster.
For information about different versions of the Aurora PostgreSQL query plan management feature, see Aurora PostgreSQL apg_plan_mgmt extension versions in the Release Notes for Aurora PostgreSQL.
Query plan management terminology
The following terms are used throughout this topic.
- managed statement
-
A SQL statement captured by the optimizer under query plan management. A managed statement has one or more query execution plans stored in the
apg_plan_mgmt.dba_plans
view. - plan baseline
-
The set of approved plans for a given managed statement. That is, all the plans for the managed statement that have "Approved" for their
status
column in thedba_plan
view. - plan history
-
The set of all captured plans for a given managed statement. The plan history contains all plans captured for the statement, regardless of status.
- query plan regression
-
The case when the optimizer chooses a less optimal plan than it did before a given change to the database environment, such as a new PostgreSQL version or changes to statistics.
Aurora PostgreSQL query plan management versions
Query plan management is supported by all currently available Aurora PostgreSQL releases. For more information, see the list of Amazon Aurora PostgreSQL updates in the Release Notes for Aurora PostgreSQL.
Query plan management functionality is added to your Aurora PostgreSQL DB cluster
when you install the apg_plan_mgmt
extension. Different versions of Aurora PostgreSQL
support different versions of the apg_plan_mgmt
extension.
We recommend that you upgrade the query plan management extension
to the latest release for your version of Aurora PostgreSQL.
Note
For release notes for each apg_plan_mgmt
extension versions, see Aurora PostgreSQL apg_plan_mgmt extension versions in the
Release Notes for Aurora PostgreSQL.
You can identify the version running on your cluster by connecting to an instance using psql
and using the
metacommand \dx to list extensions as shown following.
labdb=>
\dxList of installed extensions Name | Version | Schema | Description ---------------+---------+---------------+------------------------------------------------------------------- apg_plan_mgmt | 1.0 | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)
The output shows that this cluster is using 1.0 version of the extension. Only certain apg_plan_mgmt
versions are
available for a given Aurora PostgreSQL version. In some cases, you might need to upgrade the Aurora PostgreSQL DB cluster to a new minor release or apply a patch so that
you can upgrade to the most recent version of query plan management. The apg_plan_mgmt
version
1.0 shown in the output is from an Aurora PostgreSQL version 10.17 DB cluster, which doesn't have a newer version
of apg_plan_mgmt
available. In this case, the Aurora PostgreSQL DB cluster
should be upgraded to a more recent version of PostgreSQL.
For more information about upgrading your Aurora PostgreSQL DB cluster to a new version of PostgreSQL, see Database engine updates for Amazon Aurora PostgreSQL.
To learn how to upgrade the apg_plan_mgmt
extension,
see Upgrading Aurora PostgreSQL query plan management.
Turning on Aurora PostgreSQL query plan management
Setting up query plan management for your Aurora PostgreSQL DB cluster involves installing an extension
and changing several DB cluster parameter settings. You need rds_superuser
permissions
to install the apg_plan_mgmt
extension and to turn on the feature for the
Aurora PostgreSQL DB cluster.
Installing the extension creates a new role, apg_plan_mgmt
. This role allows database users
to view, manage, and maintain query plans. As an administrator with rds_superuser
privileges,
be sure to grant the apg_plan_mgmt
role to database users as needed.
Only users with the rds_superuser
role can complete the following procedure. The
rds_superuser
is required for creating the apg_plan_mgmt
extension and its
apg_plan_mgmt
role. Users must be granted the apg_plan_mgmt
role to administer the
apg_plan_mgmt
extension.
To turn on query plan management for your Aurora PostgreSQL DB cluster
The following steps turn on query plan management for all SQL statements that get submitted to the Aurora PostgreSQL DB cluster. This is known as automatic mode. To learn more about the difference between modes, see Capturing Aurora PostgreSQL execution plans.
Open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
Create a custom DB cluster parameter group for your Aurora PostgreSQL DB cluster. You need to change certain parameters to activate query plan management and to set its behavior. For more information, see Creating a DB parameter group in Amazon Aurora.
Open the custom DB cluster parameter group and set the
rds.enable_plan_management
parameter to1
, as shown in the following image.For more information, see Modifying parameters in a DB cluster parameter group in Amazon Aurora.
Create a custom DB parameter group that you can use to set query plan parameters at the instance level. For more information, see Creating a DB cluster parameter group in Amazon Aurora.
Modify the writer instance of the Aurora PostgreSQL DB cluster to use the custom DB parameter group. For more information, see Modifying a DB instance in a DB cluster.
Modify the Aurora PostgreSQL DB cluster to use the custom DB cluster parameter group. For more information, see Modifying the DB cluster by using the console, CLI, and API.
Reboot your DB instance to enable the custom parameter group settings.
Connect to your Aurora PostgreSQL DB cluster's DB instance endpoint using
psql
orpgAdmin
. The following example uses the defaultpostgres
account for therds_superuser
role.psql --host=
cluster-instance-1.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
Create the
apg_plan_mgmt
extension for your DB instance, as shown following.labdb=>
CREATE EXTENSION apg_plan_mgmt;CREATE EXTENSION
Tip
Install the
apg_plan_mgmt
extension in the template database for your application. The default template database is namedtemplate1
. To learn more, see Template Databasesin the PostgreSQL documentation. Change the
apg_plan_mgmt.capture_plan_baselines
parameter toautomatic
. This setting causes the optimizer to generate plans for every SQL statement that is either planned or executed two or more times.Note
Query plan management also has a manual mode that you can use for specific SQL statements. To learn more, see Capturing Aurora PostgreSQL execution plans.
-
Change the value of
apg_plan_mgmt.use_plan_baselines
parameter to "on." This parameter causes the optimizer to choose a plan for the statement from its plan baseline. To learn more, see Using Aurora PostgreSQL managed plans.Note
You can modify the value of either of these dynamic parameters for the session without needing to reboot the instance.
When your query plan management set up is complete, be sure to grant the apg_plan_mgmt
role
to any database users that need to view, manage, or maintain query plans.
Upgrading Aurora PostgreSQL query plan management
We recommend that you upgrade the query plan management extension to the latest release for your version of Aurora PostgreSQL.
Connect to the writer instance of your Aurora PostgreSQL DB cluster as a user that has
rds_superuser
privileges. If you kept the default name when you set up your instance, you connect aspostgres
This example shows how to usepsql
, but you can also use pgAdmin if you prefer.psql --host=
111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --passwordRun the following query to upgrade the extension.
ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';
Use the apg_plan_mgmt.validate_plans function to update the hashes of all plans. The optimizer validates all Approved, Unapproved, and Rejected plans to ensure that they's still viable plans for new version of the extension.
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
To learn more about using this function, see Validating plans.
Use the apg_plan_mgmt.reload function to refresh any plans in the shared memory with the validated plans from the dba_plans view.
SELECT apg_plan_mgmt.reload();
To learn more about all functions available for query plan management, see Function reference for Aurora PostgreSQL query plan management.
Turning off Aurora PostgreSQL query plan management
You can disable query plan management at any time by turning off the apg_plan_mgmt.use_plan_baselines
and apg_plan_mgmt.capture_plan_baselines
.
labdb=>
SET apg_plan_mgmt.use_plan_baselines = off;labdb=>
SET apg_plan_mgmt.capture_plan_baselines = off;