# Managing query execution plans for Aurora PostgreSQL
Aurora PostgreSQL query plan management is an optional feature that you can use with your Amazon Aurora PostgreSQL-Compatible Edition DB cluster. This feature is packaged as the `apg_plan_mgmt` extension that you can install in your Aurora PostgreSQL DB cluster. Query plan management allows you to manage the query execution plans generated by the optimizer for your SQL applications. The `apg_plan_mgmt` AWS extension builds on the native query processing functionality of the PostgreSQL database engine.
Following, you can find information about Aurora PostgreSQL query plan management features, how to set it up, and how to use it with your Aurora PostgreSQL DB cluster. Before you get started, we recommend that you review any release notes for the specific version of the `apg_plan_mgmt` extension available for your Aurora PostgreSQL version. For more information, see [Aurora PostgreSQL apg\$1plan\$1mgmt extension versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) in the *Release Notes for Aurora PostgreSQL*.
**Topics**
+ [Overview of Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.overview.md)
+ [Best practices for Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.BestPractice.md)
+ [Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.Start.md)
+ [Capturing Aurora PostgreSQL execution plans](AuroraPostgreSQL.Optimize.CapturePlans.md)
+ [Using Aurora PostgreSQL managed plans](AuroraPostgreSQL.Optimize.UsePlans.md)
+ [Examining Aurora PostgreSQL query plans in the dba\$1plans view](AuroraPostgreSQL.Optimize.ViewPlans.md)
+ [Improving Aurora PostgreSQL query plans](AuroraPostgreSQL.Optimize.Maintenance.md)
+ [Deleting Aurora PostgreSQL query plans](AuroraPostgreSQL.Optimize.Deleting.md)
+ [Exporting and importing managed plans for Aurora PostgreSQL](AuroraPostgreSQL.Optimize.Maintenance.ExportingImporting.md)
+ [Parameter reference for Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.Parameters.md)
+ [Function reference for Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.Functions.md)
+ [Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition](AuroraPostgreSQL.Optimize.dba_plans_view_Reference.md)
+ [Advanced features in Query Plan Management](AuroraPostgreSQL.QPM.Advanced.md)
# 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](AuroraPostgreSQL.Optimize.BestPractice.md).
**Topics**
+ [Supported SQL statements](#AuroraPostgreSQL.Optimize.overview.features)
+ [Query plan management limitations](#AuroraPostgreSQL.Optimize.overview.limitations)
+ [Query plan management terminology](#AuroraPostgreSQL.Optimize.Start-terminology)
+ [Aurora PostgreSQL query plan management versions](#AuroraPostgreSQL.Optimize.overview.versions)
+ [Turning on Aurora PostgreSQL query plan management](#AuroraPostgreSQL.Optimize.Enable)
+ [Upgrading Aurora PostgreSQL query plan management](#AuroraPostgreSQL.Optimize.Upgrade)
+ [Turning off Aurora PostgreSQL query plan management](#AuroraPostgreSQL.Optimize.Enable.turnoff)
## 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](https://www.postgresql.org/docs/14/sql-prepare.html) in the PostgreSQL documentation.
+ Dynamic statements, including those run in immediate-mode. For more information, see [Dynamic SQL](https://www.postgresql.org/docs/current/ecpg-dynamic.html) and [EXECUTE IMMEDIATE](https://www.postgresql.org/docs/current/ecpg-sql-execute-immediate.html) in PostgreSQL documentation.
+ Embedded SQL commands and statements. For more information, see [Embedded SQL Commands](https://www.postgresql.org/docs/current/ecpg-sql-commands.html) in the PostgreSQL documentation.
+ Statements inside named functions. For more information, see [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html) 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](AuroraPostgreSQL.Optimize.UsePlans.md#AuroraPostgreSQL.Optimize.UsePlans.AnalyzePlans). To learn more about query plan management's modes (manual, automatic), see [Capturing Aurora PostgreSQL execution plans](AuroraPostgreSQL.Optimize.CapturePlans.md).
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](https://www.postgresql.org/docs/current/ddl-partitioning.html), [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), and [WITH Queries (Common Table Expressions)](https://www.postgresql.org/docs/current/queries-with.html) and other topics in the PostgreSQL documentation.
For information about different versions of the Aurora PostgreSQL query plan management feature, see [Aurora PostgreSQL apg\$1plan\$1mgmt extension versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) 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 of `max_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](Concepts.DBInstanceClass.SupportAurora.md).
+ **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` to `force_custom_plan` in the DB cluster parameter group.
+ `queryid` in `apg_plan_mgmt.dba_plans` and `pg_stat_statements` can diverge when:
+ Objects are dropped and recreated after storing in apg\$1plan\$1mgmt.dba\$1plans.
+ `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\$1plan\$1mgmt extension versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) 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 the `dba_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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html) 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\$1plan\$1mgmt extension versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.apg_plan_mgmt) 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 \$1dx to list extensions as shown following.
```
labdb=> \dx
List 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](AuroraPostgreSQL.Updates.md).
To learn how to upgrade the `apg_plan_mgmt` extension, see [Upgrading Aurora PostgreSQL query plan management](#AuroraPostgreSQL.Optimize.Upgrade).
## 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](AuroraPostgreSQL.Optimize.CapturePlans.md).
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. 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](USER_WorkingWithParamGroups.Creating.md).
1. Open the custom DB cluster parameter group and set the `rds.enable_plan_management` parameter to `1`, as shown in the following image.
![\[Image of the DB cluster parameter group.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/aurora-qpm-custom-db-cluster-param-change-1.png)
For more information, see [Modifying parameters in a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md).
1. 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 groupin Amazon Aurora](USER_WorkingWithParamGroups.CreatingCluster.md).
1. 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](Aurora.Modifying.md#Aurora.Modifying.Instance).
1. 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](Aurora.Modifying.md#Aurora.Modifying.Cluster).
1. Reboot your DB instance to enable the custom parameter group settings.
1. Connect to your Aurora PostgreSQL DB cluster's DB instance endpoint using `psql` or `pgAdmin`. The following example uses the default `postgres` account for the `rds_superuser` role.
```
psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
```
1. 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 named `template1`. To learn more, see [Template Databases](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html) in the PostgreSQL documentation.
1. Change the `apg_plan_mgmt.capture_plan_baselines` parameter to `automatic`. 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](AuroraPostgreSQL.Optimize.CapturePlans.md).
1. 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](AuroraPostgreSQL.Optimize.UsePlans.md).
**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.
1. 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 as `postgres` This example shows how to use `psql`, but you can also use pgAdmin if you prefer.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. Run the following query to upgrade the extension.
```
ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';
```
1. Use the [apg\$1plan\$1mgmt.validate\$1plans](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.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](AuroraPostgreSQL.Optimize.Deleting.md#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans).
1. Use the [apg\$1plan\$1mgmt.reload](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.reload) function to refresh any plans in the shared memory with the validated plans from the dba\$1plans 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](AuroraPostgreSQL.Optimize.Functions.md).
## 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;
```
# 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.
**Contents**
+ [Proactive plan management to help prevent performance regression](#AuroraPostgreSQL.Optimize.BestPractice.Proactive)
+ [Ensuring plan stability after a major version upgrade](#AuroraPostgreSQL.Optimize.BestPractice.MajorVersionUpgrade)
+ [Reactive plan management to detect and repair performance regressions](#AuroraPostgreSQL.Optimize.BestPractice.Reactive)
## 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](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Manual) and [Automatically capturing plans](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Automatic).
1. Export the captured plans from the development environment and import them into the production environment. For more information, see [Exporting and importing managed plans for Aurora PostgreSQL](AuroraPostgreSQL.Optimize.Maintenance.ExportingImporting.md).
1. In production, run your application and enforce the use of approved managed plans. For more information, see [Using Aurora PostgreSQL managed plans](AuroraPostgreSQL.Optimize.UsePlans.md). While the application runs, also add new plans as the optimizer discovers them. For more information, see [Automatically capturing plans](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Automatic).
1. Analyze the unapproved plans and approve those that perform well. For more information, see [Evaluating plan performance](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance).
1. 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, query execution plans generated by the optimizer in earlier versions might cause performance regressions in newer upgraded versions. You can use query plan management to resolve these performance issues and to ensure plan stability after a major version upgrade.
The optimizer always uses a minimum-cost Approved plan, even if more than one Approved plan for the same statement exists. After an upgrade the optimizer might discover new plans but they will be saved as Unapproved plans. These plans are performed only if approved using the reactive style of plan management with the unapproved\$1plan\$1execution\$1threshold parameter. You can maximize plan stability using the proactive style of plan management with the evolve\$1plan\$1baselines parameter. This compares the performance of the new plans to the old plans and approves or rejects plans that are at least 10% faster than the next best plan.
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](AuroraPostgreSQL.Optimize.UsePlans.md).
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](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Automatic).
1. Evaluate each plan's performance. For more information, see [Evaluating plan performance](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance).
1. 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](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance.Approving). For reference information about this function, see [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines).
For more information, see [Ensuring consistent performance after major version upgrades with Amazon Aurora PostgreSQL-Compatible Edition Query Plan Management](https://aws.amazon.com/blogs/database/ensuring-consistent-performance-after-major-version-upgrades-with-amazon-aurora-postgresql-query-plan-management/).
**Note**
When you perform a major version upgrade using logical replication or AWS DMS, make sure that you replicate the `apg_plan_mgmt` schema to ensure existing plans are copied to the upgraded instance. For more information on logical replication, see [Using logical replication to perform a major version upgrade for Aurora PostgreSQL](AuroraPostgreSQL.MajorVersionUpgrade.md).
## 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](AuroraPostgreSQL.Optimize.UsePlans.md) and [Automatically capturing plans](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Automatic).
1. Monitor your running application for performance regressions.
1. 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](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance.Rejecting).
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\$1hint\$1plan](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.pg_hint_plan).
# Aurora PostgreSQL query plan management
With query plan management turned on for your Aurora PostgreSQL DB cluster, the optimizer generates and stores query execution plans for any SQL statement that it processes more than once. The optimizer always sets the status of a managed statement's first generated plan to `Approved`, and stores it in the `dba_plans` view.
The set of approved plans saved for a managed statement is known as its *plan baseline*. As your application runs, the optimizer might generate additional plans for managed statements. The optimizer sets additional captured plans to a status of `Unapproved`.
Later, you can decide if the `Unapproved` plans perform well and change them to `Approved`, `Rejected`, or `Preferred`. To do so, you use the `apg_plan_mgmt.evolve_plan_baselines` function or the `apg_plan_mgmt.set_plan_status` function.
When the optimizer generates a plan for a SQL statement, query plan management saves the plan in the `apg_plan_mgmt.plans` table. Database users that have been granted the `apg_plan_mgmt` role can see the plan details by querying the `apg_plan_mgmt.dba_plans` view. For example, the following query lists details for plans currently in the view for a non-production Aurora PostgreSQL DB cluster.
+ `sql_hash` – An identifier for the SQL statement that's the hash value for the normalized text of the SQL statement.
+ `plan_hash` – A unique identifier for the plan that's a combination of the `sql_hash` and a hash of the plan.
+ `status` – The status of the plan. The optimizer can run an approved plan.
+ `enabled` – Indicates whether the plan is ready to use (true) or not (false).
+ `plan_outline` – A representation of the plan that's used to recreate the actual execution plan. Operators in the tree structure map to operators in EXPLAIN output.
The `apg_plan_mgmt.dba_plans` view has many more columns that contain all details of the plan, such as when the plan was last used. For complete details, see [Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition](AuroraPostgreSQL.Optimize.dba_plans_view_Reference.md).
## Normalization and the SQL hash
In the `apg_plan_mgmt.dba_plans` view, you can identify a managed statement by its SQL hash value. The SQL hash is calculated on a normalized representation of the SQL statement that removes some differences, such as literal values.
The *normalization* process for each SQL statement preserves space and case, so that you can still read and understand the gist of the SQL statement. Normalization removes or replaces the following items.
+ Leading block comments
+ The EXPLAIN keyword and EXPLAIN options, and EXPLAIN ANALYZE
+ Trailing spaces
+ All literals
As an example, take the following statement.
```
/*Leading comment*/ EXPLAIN SELECT /* Query 1 */ * FROM t WHERE x > 7 AND y = 1;
```
The query plan management normalizes this statement as shown:
```
SELECT /* Query 1 */ * FROM t WHERE x > CONST AND y = CONST;
```
Normalization allows the same SQL hash to be used for similar SQL statements that might differ only in their literal or parameter values. In other words, multiple plans for the same SQL hash can exist, with a different plan that's optimal under different conditions.
**Note**
A single SQL statement that's used with different schemas has different plans because it's bound to the specific schema at runtime. The planner uses the statistics for schema binding to choose the optimal plan.
To learn more about how the optimizer chooses a plan, see [Using Aurora PostgreSQL managed plans](AuroraPostgreSQL.Optimize.UsePlans.md). In that section, you can learn how to use `EXPLAIN` and `EXPLAIN ANALYZE` to preview a plan before it's actually used. For details, see [Analyzing the optimizer's chosen plan](AuroraPostgreSQL.Optimize.UsePlans.md#AuroraPostgreSQL.Optimize.UsePlans.AnalyzePlans). For an image that outlines the process for choosing a plan, see [How the optimizer chooses which plan to run](AuroraPostgreSQL.Optimize.UsePlans.md#AuroraPostgreSQL.Optimize.UsePlans.ChoosePlans).
# Capturing Aurora PostgreSQL execution plans
Aurora PostgreSQL query plan management offers two different modes for capturing query execution plans, automatic or manual. You choose the mode by setting the value of the `apg_plan_mgmt.capture_plans_baselines` to `automatic` or to `manual`. You can capture execution plans for specific SQL statements by using manual plan capture. Alternatively, you can capture all (or the slowest) plans that are executed two or more times as your application runs by using automatic plan capture.
When capturing plans, the optimizer sets the status of a managed statement's first captured plan to `approved`. The optimizer sets the status of any additional plans captured for a managed statement to `unapproved`. However, more than one plan might occasionally be saved with the `approved` status. This can happen when multiple plans are created for a statement in parallel and before the first plan for the statement is committed.
To control the maximum number of plans that can be captured and stored in the `dba_plans` view, set the `apg_plan_mgmt.max_plans` parameter in your DB instance-level parameter group. A change to the `apg_plan_mgmt.max_plans` parameter requires a DB instance reboot for a new value to take effect. For more information, see the [apg\$1plan\$1mgmt.max\$1plans](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.max_plans) parameter.
## Manually capturing plans for specific SQL statements
If you have a known set of SQL statements to manage, put the statements into a SQL script file and then manually capture plans. The following shows a psql example of how to capture query plans manually for a set of SQL statements.
```
psql> SET apg_plan_mgmt.capture_plan_baselines = manual;
psql> \i my-statements.sql
psql> SET apg_plan_mgmt.capture_plan_baselines = off;
```
After capturing a plan for each SQL statement, the optimizer adds a new row to the `apg_plan_mgmt.dba_plans` view.
We recommend that you use either EXPLAIN or EXPLAIN EXECUTE statements in the SQL script file. Make sure that you include enough variations in parameter values to capture all the plans of interest.
If you know of a better plan than the optimizer's minimum cost plan, you might be able to force the optimizer to use the better plan. To do so, specify one or more optimizer hints. For more information, see [Fixing plans using pg\$1hint\$1plan](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.pg_hint_plan). To compare the performance of the `unapproved` and `approved` plans and approve, reject, or delete them, see [Evaluating plan performance](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance).
## Automatically capturing plans
Use automatic plan capture for situations such as the following:
+ You don't know the specific SQL statements that you want to manage.
+ You have hundreds or thousands of SQL statements to manage.
+ Your application uses a client API. For example, JDBC uses unnamed prepared statements or bulk-mode statements that can't be expressed in psql.
**To capture plans automatically**
1. Turn on automatic plan capture by setting `apg_plan_mgmt.capture_plan_baselines` to `automatic` in the DB instance-level parameter group. For more information, see [Modifying parameters in a DB parameter group in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md).
1. As the application runs, the optimizer captures plans for each SQL statement that runs at least twice.
As the application runs with default query plan management parameter settings, the optimizer captures plans for each SQL statement that runs at least twice. Capturing all plans while using the defaults has very little run-time overhead and can be enabled in production.
**To turn off automatic plan capture**
+ Set the `apg_plan_mgmt.capture_plan_baselines` parameter to `off` from the DB instance-level parameter group.
To measure the performance of the unapproved plans and approve, reject, or delete them, see [Evaluating plan performance](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance).
# Using Aurora PostgreSQL managed plans
To get the optimizer to use captured plans for your managed statements, set the parameter `apg_plan_mgmt.use_plan_baselines` to `true`. The following is a local instance example.
```
SET apg_plan_mgmt.use_plan_baselines = true;
```
While the application runs, this setting causes the optimizer to use the minimum-cost, preferred, or approved plan that is valid and enabled for each managed statement.
## Analyzing the optimizer's chosen plan
When the `apg_plan_mgmt.use_plan_baselines` parameter is set to `true`, you can use EXPLAIN ANALYZE SQL statements to cause the optimizer to show the plan it would use if it were to run the statement. The following is an example.
```
EXPLAIN ANALYZE EXECUTE rangeQuery (1,10000);
```
```
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=393.29..393.30 rows=1 width=8) (actual time=7.251..7.251 rows=1 loops=1)
-> Index Only Scan using t1_pkey on t1 t (cost=0.29..368.29 rows=10000 width=0) (actual time=0.061..4.859 rows=10000 loops=1)
Index Cond: ((id >= 1) AND (id <= 10000))
Heap Fetches: 10000
Planning time: 1.408 ms
Execution time: 7.291 ms
Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1984047223, Plan Hash: 512153379
```
The output shows the Approved plan from the baseline that would run. However, the output also shows that it found a lower-cost plan. In this case, you capture this new minimum cost plan by turning on automatic plan capture as described in [Automatically capturing plans](AuroraPostgreSQL.Optimize.CapturePlans.md#AuroraPostgreSQL.Optimize.CapturePlans.Automatic).
New plans are always captured by the optimizer as `Unapproved`. Use the `apg_plan_mgmt.evolve_plan_baselines` function to compare plans and change them to approved, rejected, or disabled. For more information, see [Evaluating plan performance](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance).
## How the optimizer chooses which plan to run
The cost of an execution plan is an estimate that the optimizer makes to compare different plans. When calculating a plan's cost, the optimizer includes factors such as CPU and I/O operations required by that plan. To learn more about PostgreSQL query planner cost estimates, see [Query Planning](https://www.postgresql.org/docs/current/runtime-config-query.html) in the PostgreSQL documentation.
The following image shows how a plan is chosen for a given SQL statement when query plan management is active, and when it's not.
![\[Aurora PostgreSQL query plan management workflow\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/aurora-query-plan-mgmt_processing-flow.png)
The flow is as follows:
1. The optimizer generates a minimum-cost plan for the SQL statement.
1. If query plan management isn't active, the optimizer's plan is run immediately (A. Run Optimizer's plan). Query plan management is inactive when the `apg_plan_mgmt.capture_plan_baselines` and the `apg_plan_mgmt.use_plan_baselines` parameters are both at their default settings ("off" and "false," respectively).
Otherwise, query plan management is active. In this case, the SQL statement and the optimizer's plan for it are further assessed before a plan is chosen.
**Tip**
Database users with the `apg_plan_mgmt` role can pro-actively compare plans, change status of plans, and force the use of specific plans as needed. For more information, see [Improving Aurora PostgreSQL query plans](AuroraPostgreSQL.Optimize.Maintenance.md).
1. The SQL statement might already have plans that were stored by query plan management in the past. Plans are stored in the `apg_plan_mgmt.dba_plans`, along with information about the SQL statements that were used to create them. Information about a plan includes its status. A plan's status can determine whether it's used or not, as follows.
1. If the plan isn't among the stored plans for the SQL statement, it means that it's the first time this particular plan was generated by the optimizer for the given SQL statement. The plan is sent to Capture Plan Processing (4).
1. If the plan is among the stored plans and its status is Approved or Preferred, the plan is run (A. Run Optimizer's plan).
If the plan is among the stored plans but it's neither Approved nor Preferred, the plan is sent to Capture Plan Processing (4).
1. When a plan is captured for the first time for a given SQL statement, the plan's status is always set to Approved (P1). If the optimizer subsequently generates the same plan for the same SQL statement, the status of that plan is changed to Unapproved (P1\$1n).
With the plan captured and its status updated, the evaluation continues at the next step (5).
1. A plan's *baseline* consists of the history of the SQL statement and its plans at various states. Query plan management can take the baseline into account when choosing a plan, depending on whether the use plan baselines option is turned on or not, as follows.
+ Use plan baselines is "off" when the `apg_plan_mgmt.use_plan_baselines` parameter is set to its default value (`false`). The plan isn't compared to the baseline before it's run (A. Run Optimizer's plan).
+ Use plan baselines is "on" when the `apg_plan_mgmt.use_plan_baselines` parameter is set to `true`. The plan is further assessed using the baseline (6).
1. The plan is compared to other plans for the statement in the baseline.
1. If the optimizer's plan is among the plans in the baseline, its status is checked (7a).
1. If the optimizer's plan isn't among plans in the baseline, the plan is added to the plans for the statement as a new `Unapproved` plan.
1. The plan's status is checked to determine only if it's Unapproved.
1. If the plan's status is Unapproved, the plan's estimated cost is compared to the cost estimate specified for the unapproved execution plan threshold.
+ If the plan's estimated cost is below the threshold, the optimizer uses it even though it's an Unapproved plan (A. Run Optimizer's plan). Generally, the optimizer won't run an Unapproved plan. However, when the `apg_plan_mgmt.unapproved_plan_execution_threshold` parameter specifies a cost threshold value, the optimizer compares the Unapproved plan's cost to the threshold. If the estimated cost is less than the threshold, the optimizer runs the plan. For more information, see [apg\$1plan\$1mgmt.unapproved\$1plan\$1execution\$1threshold](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.unapproved_plan_execution_threshold).
+ If the plan's estimated cost isn't below the threshold, the plan's other attributes are checked (8a).
1. If the plan's status is anything other than Unapproved, its other attributes are checked (8a).
1. The optimizer won't use a plan that's disabled. That is, the plan that has its `enable` attribute set to 'f' (false). The optimizer also won't use a plan that has a status of Rejected.
The optimizer can't use any plans that aren't valid. Plans can become invalid over time when the objects that they depend on, such as indexes and table partitions, are removed or deleted.
1. If the statement has any enabled and valid Preferred plans, the optimizer chooses the minimum-cost plan from among the Preferred plans stored for this SQL statement. The optimizer then runs the minimum-cost Preferred plan.
1. If the statement doesn't have any enabled and valid Preferred plans, it's assessed in the next step (9).
1. If the statement has any enabled and valid Approved plans, the optimizer chooses the minimum-cost plan from among the Approved plans stored for this SQL statement. The optimizer then runs the minimum-cost Approved plan.
If the statement doesn't have any valid and enabled Approved plans, the optimizer uses the minimum cost plan (A. Run Optimizer's plan).
# Examining Aurora PostgreSQL query plans in the dba\$1plans view
Database users and administrators that have been granted the `apg_plan_mgmt` role can view and manage the plans stored in the `apg_plan_mgmt.dba_plans`. An Aurora PostgreSQL DB cluster's administrator (someone with `rds_superuser` permissions) must explicitly grant this role to the database users who need to work with query plan management.
The `apg_plan_mgmt` view contains the plan history for all managed SQL statements for every database on the writer instance of the Aurora PostgreSQL DB cluster. This view lets you examine plans, their state, when last used, and all other relevant details.
As discussed in [Normalization and the SQL hash](AuroraPostgreSQL.Optimize.Start.md#AuroraPostgreSQL.Optimize.Start.hash-and-normalization), each managed plan is identified by the combined SQL hash value and a plan hash value. With these identifiers, you can use tools such as Amazon RDS Performance Insights to track individual plan performance. For more information about Performance Insights, see [Using Amazon RDS performance insights]( https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html).
## Listing managed plans
To list the managed plans, use a SELECT statement on the `apg_plan_mgmt.dba_plans` view. The following example displays some columns in the `dba_plans` view such as the `status`, which identifies the approved and unapproved plans.
```
SELECT sql_hash, plan_hash, status, enabled, stmt_name
FROM apg_plan_mgmt.dba_plans;
sql_hash | plan_hash | status | enabled | stmt_name
------------+-----------+------------+---------+------------
1984047223 | 512153379 | Approved | t | rangequery
1984047223 | 512284451 | Unapproved | t | rangequery
(2 rows)
```
For readability, the query and the output shown list just a few of the columns from the `dba_plans` view. For complete information, see [Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition](AuroraPostgreSQL.Optimize.dba_plans_view_Reference.md).
# Improving Aurora PostgreSQL query plans
Improve query plan management by evaluating plan performance and fixing plans. For more information about improving your query plans, see the following topics.
**Topics**
+ [Evaluating plan performance](#AuroraPostgreSQL.Optimize.Maintenance.EvaluatingPerformance)
+ [Fixing plans using pg\$1hint\$1plan](#AuroraPostgreSQL.Optimize.Maintenance.pg_hint_plan)
## Evaluating plan performance
After the optimizer captures plans as unapproved, use the `apg_plan_mgmt.evolve_plan_baselines` function to compare plans based on their actual performance. Depending on the outcome of your performance experiments, you can change a plan's status from unapproved to either approved or rejected. You can instead decide to use the `apg_plan_mgmt.evolve_plan_baselines` function to temporarily disable a plan if it does not meet your requirements.
### Approving better plans
The following example demonstrates how to change the status of managed plans to approved using the `apg_plan_mgmt.evolve_plan_baselines` function.
```
SELECT apg_plan_mgmt.evolve_plan_baselines (
sql_hash,
plan_hash,
min_speedup_factor := 1.0,
action := 'approve'
)
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';
```
```
NOTICE: rangequery (1,10000)
NOTICE: Baseline [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE: Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE: Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE: Unapproved -> Approved
evolve_plan_baselines
-----------------------
0
(1 row)
```
The output shows a performance report for the `rangequery` statement with parameter bindings of 1 and 10,000. The new unapproved plan (`Baseline+1`) is better than the best previously approved plan (`Baseline`). To confirm that the new plan is now `Approved`, check the `apg_plan_mgmt.dba_plans` view.
```
SELECT sql_hash, plan_hash, status, enabled, stmt_name
FROM apg_plan_mgmt.dba_plans;
```
```
sql_hash | plan_hash | status | enabled | stmt_name
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t | rangequery
1984047223 | 512284451 | Approved | t | rangequery
(2 rows)
```
The managed plan now includes two approved plans that are the statement's plan baseline. You can also call the `apg_plan_mgmt.set_plan_status` function to directly set a plan's status field to `'Approved'`, `'Rejected'`, `'Unapproved'`, or `'Preferred'`.
### Rejecting or disabling slower plans
To reject or disable plans, pass `'reject'` or `'disable' `as the action parameter to the `apg_plan_mgmt.evolve_plan_baselines` function. This example disables any captured `Unapproved` plan that is slower by at least 10 percent than the best `Approved` plan for the statement.
```
SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash, -- The managed statement ID
plan_hash, -- The plan ID
1.1, -- number of times faster the plan must be
'disable' -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND -- plan is Unapproved
origin = 'Automatic'; -- plan was auto-captured
```
You can also directly set a plan to rejected or disabled. To directly set a plan's enabled field to `true` or `false`, call the `apg_plan_mgmt.set_plan_enabled` function. To directly set a plan's status field to `'Approved'`, `'Rejected'`, `'Unapproved'`, or `'Preferred'`, call the `apg_plan_mgmt.set_plan_status` function.
To delete plans that aren't valid and that you expect to remain invalid, use the `apg_plan_mgmt.validate_plans` function. This function lets you delete or disable invalid plans. For more information, see [Validating plans](AuroraPostgreSQL.Optimize.Deleting.md#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans).
## Fixing plans using pg\$1hint\$1plan
The query optimizer is well-designed to find an optimal plan for all statements, and in most cases the optimizer finds a good plan. However, occasionally you might know that a much better plan exists than that generated by the optimizer. Two recommended ways to get the optimizer to generate a desired plan include using the `pg_hint_plan` extension or setting Grand Unified Configuration (GUC) variables in PostgreSQL:
+ `pg_hint_plan` extension – Specify a "hint" to modify how the planner works by using PostgreSQL's `pg_hint_plan` extension. To install and learn more about how to use the `pg_hint_plan` extension, see the [pg\$1hint\$1plan documentation](https://github.com/ossc-db/pg_hint_plan).
+ GUC variables – Override one or more cost model parameters or other optimizer parameters, such as the `from_collapse_limit` or `GEQO_threshold`.
When you use one of these techniques to force the query optimizer to use a plan, you can also use query plan management to capture and enforce use of the new plan.
You can use the `pg_hint_plan` extension to change the join order, the join methods, or the access paths for a SQL statement. You use a SQL comment with special `pg_hint_plan` syntax to modify how the optimizer creates a plan. For example, assume the problem SQL statement has a two-way join.
```
SELECT *
FROM t1, t2
WHERE t1.id = t2.id;
```
Then suppose that the optimizer chooses the join order (t1, t2), but you know that the join order (t2, t1) is faster. The following hint forces the optimizer to use the faster join order, (t2, t1). Include EXPLAIN so that the optimizer generates a plan for the SQL statement but without running the statement. (Output not shown.)
```
/*+ Leading ((t2 t1)) */ EXPLAIN SELECT *
FROM t1, t2
WHERE t1.id = t2.id;
```
The following steps show how to use `pg_hint_plan`.
**To modify the optimizer's generated plan and capture the plan using pg\$1hint\$1plan**
1. Turn on the manual capture mode.
```
SET apg_plan_mgmt.capture_plan_baselines = manual;
```
1. Specify a hint for the SQL statement of interest.
```
/*+ Leading ((t2 t1)) */ EXPLAIN SELECT *
FROM t1, t2
WHERE t1.id = t2.id;
```
After this runs, the optimizer captures the plan in the `apg_plan_mgmt.dba_plans` view. The captured plan doesn't include the special `pg_hint_plan` comment syntax because query plan management normalizes the statement by removing leading comments.
1. View the managed plans by using the `apg_plan_mgmt.dba_plans` view.
```
SELECT sql_hash, plan_hash, status, sql_text, plan_outline
FROM apg_plan_mgmt.dba_plans;
```
1. Set the status of the plan to `Preferred`. Doing so makes sure that the optimizer chooses to run it, instead of selecting from the set of approved plans, when the minimum-cost plan isn't already `Approved` or `Preferred`.
```
SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );
```
1. Turn off manual plan capture and enforce the use of managed plans.
```
SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;
```
Now, when the original SQL statement runs, the optimizer chooses either an `Approved` or `Preferred` plan. If the minimum-cost plan isn't `Approved` or `Preferred`, then the optimizer chooses the `Preferred` plan.
# Deleting Aurora PostgreSQL query plans
Delete execution plans that you aren't using or plans that aren't valid. For more information about deleting plans, see the following sections.
**Topics**
+ [Deleting plans](#AuroraPostgreSQL.Optimize.Maintenance.DeletingPlans)
+ [Validating plans](#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans)
## Deleting plans
Plans are automatically deleted if they haven't been used in over a month, specifically, 32 days. That's the default setting for the `apg_plan_mgmt.plan_retention_period` parameter. You can change the plan retention period to a longer period of time, or to a shorter period of time starting from the value of 1. Determining the number of days since a plan was last used is calculated by subtracting the `last_used` date from the current date. The `last_used` date is the most recent date that the optimizer chose the plan as the minimum cost plan or that the plan was run. The date is stored for the plan in the `apg_plan_mgmt.dba_plans` view.
We recommend that you delete plans that haven't been used for a long time or that aren't useful. Every plan has a `last_used` date that the optimizer updates each time it executes a plan or chooses the plan as the minimum-cost plan for a statement. Check the last `last_used` dates to identify the plans that you can safely delete.
The following query returns a three column table with the count on the total number of plans, plans failed to delete, and the plans successfully deleted. It has a nested query that is an example of how to use the `apg_plan_mgmt.delete_plan` function to delete all plans that haven't been chosen as the minimum-cost plan in the last 31 days and its status is not `Rejected`.
```
SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
COUNT(*) FILTER (WHERE result = 0) successfully_deleted
FROM (
SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '31 days')
AND status <> 'Rejected'
) as dba_plans ;
```
```
total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
3 | 0 | 2
```
For more information, see [apg\$1plan\$1mgmt.delete\$1plan](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.delete_plan).
To delete plans that aren't valid and that you expect to remain invalid, use the `apg_plan_mgmt.validate_plans` function. This function lets you delete or disable invalid plans. For more information, see [Validating plans](#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans).
**Important**
If you don't delete extraneous plans, you might eventually run out of shared memory that's set aside for query plan management. To control how much memory is available for managed plans, use the `apg_plan_mgmt.max_plans` parameter. Set this parameter in your custom DB parameter group and reboot your DB instance for changes to take effect. For more information, see the [apg\$1plan\$1mgmt.max\$1plans](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.max_plans) parameter.
## Validating plans
Use the `apg_plan_mgmt.validate_plans` function to delete or disable plans that are invalid.
Plans can become invalid or stale when objects that they depend on are removed, such as an index or a table. However, a plan might be invalid only temporarily if the removed object gets recreated. If an invalid plan can become valid later, you might prefer to disable an invalid plan or do nothing rather than delete it.
To find and delete all plans that are invalid and haven't been used in the past week, use the `apg_plan_mgmt.validate_plans `function as follows.
```
SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete')
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');
```
To enable or disabled a plan directly, use the `apg_plan_mgmt.set_plan_enabled` function.
# Exporting and importing managed plans for Aurora PostgreSQL
You can export your managed plans and import them into another DB instance.
**To export managed plans**
An authorized user can copy any subset of the `apg_plan_mgmt.plans` table to another table, and then save it using the `pg_dump` command. The following is an example.
```
CREATE TABLE plans_copy AS SELECT *
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;
```
```
% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar
```
```
DROP TABLE apg_plan_mgmt.plans_copy;
```
**To import managed plans**
1. Copy the .tar file of the exported managed plans to the system where the plans are to be restored.
1. Use the `pg_restore` command to copy the tar file into a new table.
```
% pg_restore --dbname mytargetdatabase -Ft plans_copy.tar
```
1. Merge the `plans_copy` table with the `apg_plan_mgmt.plans` table, as shown in the following example.
**Note**
In some cases, you might dump from one version of the `apg_plan_mgmt` extension and restore into a different version. In these cases, the columns in the plans table might be different. If so, name the columns explicitly instead of using SELECT \$1.
```
INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
ON CONFLICT ON CONSTRAINT plans_pkey
DO UPDATE SET
status = EXCLUDED.status,
enabled = EXCLUDED.enabled,
-- Save the most recent last_used date
--
last_used = CASE WHEN EXCLUDED.last_used > plans.last_used
THEN EXCLUDED.last_used ELSE plans.last_used END,
-- Save statistics gathered by evolve_plan_baselines, if it ran:
--
estimated_startup_cost = EXCLUDED.estimated_startup_cost,
estimated_total_cost = EXCLUDED.estimated_total_cost,
planning_time_ms = EXCLUDED.planning_time_ms,
execution_time_ms = EXCLUDED.execution_time_ms,
total_time_benefit_ms = EXCLUDED.total_time_benefit_ms,
execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;
```
1. Reload the managed plans into shared memory and remove the temporary plans table.
```
SELECT apg_plan_mgmt.reload(); -- refresh shared memory
DROP TABLE plans_copy;
```
# Parameter reference for Aurora PostgreSQL query plan management
You can set your preferences for the `apg_plan_mgmt` extension by using the parameters listed in this section. These are available in the custom DB cluster parameter and the DB parameter group associated with your Aurora PostgreSQL DB cluster. These parameters control the behavior of the query plan management feature and how it affects the optimizer. For information about setting up query plan management, see [Turning on Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.overview.md#AuroraPostgreSQL.Optimize.Enable). Changing the parameters following has no effect if the `apg_plan_mgmt` extension isn't set up as detailed in that section. For information about modifying parameters, see [Modifying parameters in a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md) and [DB parameter groups for Amazon Aurora DB instances](USER_WorkingWithDBInstanceParamGroups.md).
**Topics**
+ [apg\$1plan\$1mgmt.capture\$1plan\$1baselines](#AuroraPostgreSQL.Optimize.Parameters.capture_plan_baselines)
+ [apg\$1plan\$1mgmt.plan\$1capture\$1threshold](#AuroraPostgreSQL.Optimize.Parameters.plan_capture_threshold)
+ [apg\$1plan\$1mgmt.explain\$1hashes](#AuroraPostgreSQL.Optimize.Parameters.explain_hashes)
+ [apg\$1plan\$1mgmt.log\$1plan\$1enforcement\$1result](#AuroraPostgreSQL.Optimize.Parameters.log_plan_enforcement_result)
+ [apg\$1plan\$1mgmt.max\$1databases](#AuroraPostgreSQL.Optimize.Parameters.max_databases)
+ [apg\$1plan\$1mgmt.max\$1plans](#AuroraPostgreSQL.Optimize.Parameters.max_plans)
+ [apg\$1plan\$1mgmt.plan\$1hash\$1version](#AuroraPostgreSQL.Optimize.Parameters.plan_hash_version)
+ [apg\$1plan\$1mgmt.plan\$1retention\$1period](#AuroraPostgreSQL.Optimize.Parameters.plan_retention_period)
+ [apg\$1plan\$1mgmt.unapproved\$1plan\$1execution\$1threshold](#AuroraPostgreSQL.Optimize.Parameters.unapproved_plan_execution_threshold)
+ [apg\$1plan\$1mgmt.use\$1plan\$1baselines](#AuroraPostgreSQL.Optimize.Parameters.use_plan_baselines)
+ [auto\$1explain.hashes](#AuroraPostgreSQL.Optimize.Parameters.auto_explain.hashes)
## apg\$1plan\$1mgmt.capture\$1plan\$1baselines
Captures query execution plans generated by the optimizer for each SQL statement and stores them in the `dba_plans` view. By default, the maximum number of plans that can be stored is 10,000 as specified by the `apg_plan_mgmt.max_plans` parameter. For reference information, see [apg\$1plan\$1mgmt.max\$1plans](#AuroraPostgreSQL.Optimize.Parameters.max_plans).
You can set this parameter in the custom DB cluster parameter group or in the custom DB parameter group. Changing the value of this parameter doesn't require a reboot.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
For more information, see [Capturing Aurora PostgreSQL execution plans](AuroraPostgreSQL.Optimize.CapturePlans.md).
## apg\$1plan\$1mgmt.plan\$1capture\$1threshold
Specifies a threshold so that if the total cost of the query execution plan is below the threshold, the plan won’t be captured in the `apg_plan_mgmt.dba_plans` view.
Changing the value of this parameter doesn't require a reboot.
| Default | Allowed values | Description |
| --- | --- | --- |
| 0 | 0 - 1.79769e\$1308 | Sets the threshold of the `apg_plan_mgmt` query plan total execution cost for capturing plans. |
For more information, see [Examining Aurora PostgreSQL query plans in the dba\$1plans view](AuroraPostgreSQL.Optimize.ViewPlans.md).
## apg\$1plan\$1mgmt.explain\$1hashes
Specifies if the `EXPLAIN [ANALYZE]` shows `sql_hash` and `plan_hash` at the end of its output. Changing the value of this parameter doesn't require a reboot.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
## apg\$1plan\$1mgmt.log\$1plan\$1enforcement\$1result
Specifies if the results has to be recorded to see if the QPM managed plans are used properly. When a stored generic plan is used, there will be no records written in the log files. Changing the value of this parameter doesn't require a reboot.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
## apg\$1plan\$1mgmt.max\$1databases
Specifies the maximum number of databases on your Aurora PostgreSQL DB cluster's Writer instance that can use query plan management. By default, up to 10 databases can use query plan management. If you have more than 10 databases on the instance, you can change the value of this setting. To find out how many databases are on a given instance, connect to the instance using `psql`. Then, use the psql metacommand, `\l`, to list the databases.
Changing the value of this parameter requires that you reboot the instance for the setting to take effect.
| Default | Allowed values | Description |
| --- | --- | --- |
| 10 | 10-2147483647 | Maximum number of databases that can use query plan management on the instance. |
You can set this parameter in the custom DB cluster parameter group or in the custom DB parameter group.
## apg\$1plan\$1mgmt.max\$1plans
Sets the maximum number of SQL statements that the query plan manager can maintain in the `apg_plan_mgmt.dba_plans` view. We recommend setting this parameter to `10000` or higher for all Aurora PostgreSQL versions.
You can set this parameter in the custom DB cluster parameter group or in the custom DB parameter group. Changing the value of this parameter requires that you reboot the instance for the setting to take effect.
| Default | Allowed values | Description |
| --- | --- | --- |
| 10000 | 10-2147483647 | Maximum number of plans that can be stored in the `apg_plan_mgmt.dba_plans` view. Default for Aurora PostgreSQL version 10 and older versions is 1000. |
For more information, see [Examining Aurora PostgreSQL query plans in the dba\$1plans view](AuroraPostgreSQL.Optimize.ViewPlans.md).
## apg\$1plan\$1mgmt.plan\$1hash\$1version
Specifies the use cases that the plan\$1hash calculation is designed to cover. A higher version of `apg_plan_mgmt.plan_hash_version` covers all the functionality of the lower version. For example, version 3 covers the use cases supported by version 2.
Changing the value of this parameter must be followed by a call to `apg_plan_mgmt.validate_plans('update_plan_hash')`. It updates the plan\$1hash values in each database with apg\$1plan\$1mgmt installed and entries in the plans table. For more information, see [Validating plans](AuroraPostgreSQL.Optimize.Deleting.md#AuroraPostgreSQL.Optimize.Maintenance.ValidatingPlans)
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
## apg\$1plan\$1mgmt.plan\$1retention\$1period
Specifies the number of days to keep plans in the `apg_plan_mgmt.dba_plans` view, after which they're automatically deleted. By default, a plan is deleted when 32 days have elapsed since the plan was last used (the `last_used` column in the `apg_plan_mgmt.dba_plans` view). You can change this setting to any number, 1 and over.
Changing the value of this parameter requires that you reboot the instance for the setting to take effect.
| Default | Allowed values | Description |
| --- | --- | --- |
| 32 | 1-2147483647 | Maximum number of days since a plan was last used before it's deleted. |
For more information, see [Examining Aurora PostgreSQL query plans in the dba\$1plans view](AuroraPostgreSQL.Optimize.ViewPlans.md).
## apg\$1plan\$1mgmt.unapproved\$1plan\$1execution\$1threshold
Specifies a cost threshold below which an Unapproved plan can be used by the optimizer. By default the threshold is 0, so the optimizer doesn't run Unapproved plans. Setting this parameter to a trivially low cost threshold such as 100 avoids plan enforcement overhead on trivial plans. You can also set this parameter to an extremely large value like 10000000 using the reactive style of plan management. This allows the optimizer to use all chosen plans with no plan enforcement overhead. But, when a bad plan is found, you can manually mark it as "rejected" so that it is not used next time.
The value of this parameter represents a cost estimate for running a given plan. If an Unapproved plan is below that estimated cost, the optimizer uses it for the SQL statement. You can see captured plans and their status (Approved, Unapproved) in the `dba_plans` view. To learn more, see [Examining Aurora PostgreSQL query plans in the dba\$1plans view](AuroraPostgreSQL.Optimize.ViewPlans.md).
Changing the value of this parameter doesn't require a reboot.
| Default | Allowed values | Description |
| --- | --- | --- |
| 0 | 0-2147483647 | Estimated plan cost below which an Unapproved plan is used. |
For more information, see [Using Aurora PostgreSQL managed plans](AuroraPostgreSQL.Optimize.UsePlans.md).
## apg\$1plan\$1mgmt.use\$1plan\$1baselines
Specifies that the optimizer should use one of the Approved plans captured and stored in the `apg_plan_mgmt.dba_plans` view. By default, this parameter is off (false), causing the optimizer to use the minimum-cost plan that it generates without any further assessment. Turning this parameter on (setting it to true) forces the optimizer to choose a query execution plan for the statement from its plan baseline. For more information, see [Using Aurora PostgreSQL managed plans](AuroraPostgreSQL.Optimize.UsePlans.md). To find an image detailing this process, see [How the optimizer chooses which plan to run](AuroraPostgreSQL.Optimize.UsePlans.md#AuroraPostgreSQL.Optimize.UsePlans.ChoosePlans).
You can set this parameter in the custom DB cluster parameter group or in the custom DB parameter group. Changing the value of this parameter doesn't require a reboot.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
You can evaluate response times of different captured plans and change plan status, as needed. For more information, see [Improving Aurora PostgreSQL query plans](AuroraPostgreSQL.Optimize.Maintenance.md).
## auto\$1explain.hashes
Specifies if the auto\$1explain output shows sql\$1hash and plan\$1hash. Changing the value of this parameter doesn't require a reboot.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Parameters.html)
# Function reference for Aurora PostgreSQL query plan management
The `apg_plan_mgmt` extension provides the following functions.
**Topics**
+ [apg\$1plan\$1mgmt.copy\$1outline](#AuroraPostgreSQL.Optimize.Functions.copy_outline)
+ [apg\$1plan\$1mgmt.delete\$1plan](#AuroraPostgreSQL.Optimize.Functions.delete_plan)
+ [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines)
+ [apg\$1plan\$1mgmt.get\$1explain\$1plan](#AuroraPostgreSQL.Optimize.Functions.get_explain_plan)
+ [apg\$1plan\$1mgmt.plan\$1last\$1used](#AuroraPostgreSQL.Optimize.Functions.plan_last_used)
+ [apg\$1plan\$1mgmt.reload](#AuroraPostgreSQL.Optimize.Functions.reload)
+ [apg\$1plan\$1mgmt.set\$1plan\$1enabled](#AuroraPostgreSQL.Optimize.Functions.set_plan_enabled)
+ [apg\$1plan\$1mgmt.set\$1plan\$1status](#AuroraPostgreSQL.Optimize.Functions.set_plan_status)
+ [apg\$1plan\$1mgmt.update\$1plans\$1last\$1used](#AuroraPostgreSQL.Optimize.Functions.update_plans_last_used)
+ [apg\$1plan\$1mgmt.validate\$1plans](#AuroraPostgreSQL.Optimize.Functions.validate_plans)
## apg\$1plan\$1mgmt.copy\$1outline
Copy a given SQL plan hash and plan outline to a target SQL plan hash and outline, thereby overwriting the target's plan hash and outline. This function is available in `apg_plan_mgmt` 2.3 and higher releases.
**Syntax**
```
apg_plan_mgmt.copy_outline(
source_sql_hash,
source_plan_hash,
target_sql_hash,
target_plan_hash,
force_update_target_plan_hash
)
```
**Return value**
Returns 0 when the copy is successful. Raises exceptions for invalid inputs.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| source\$1sql\$1hash | The sql\$1hash ID associated with the plan\$1hash to copy to the target query. |
| source\$1plan\$1hash | The plan\$1hash ID to copy to the target query. |
| target\$1sql\$1hash | The sql\$1hash ID of the query to update with the source plan hash and outline. |
| target\$1plan\$1hash | The plan\$1hash ID of the query to update with the source plan hash and outline. |
| force\$1update\$1target\$1plan\$1hash | (Optional) The target\$1plan\$1hash ID of the query is updated even if the source plan isn't reproducible for the target\$1sql\$1hash. When set to true, the function can be used to copy plans across schemas where relation names and columns are consistent. |
**Usage notes**
This function allows you to copy a plan hash and plan outline that uses hints to other, similar statements, and thus saves you from needing to use in-line hint statements at every occurrence in the target statements. If the updated target query results in an invalid plan, this function raises an error and rolls back the attempted update.
## apg\$1plan\$1mgmt.delete\$1plan
Delete a managed plan.
**Syntax**
```
apg_plan_mgmt.delete_plan(
sql_hash,
plan_hash
)
```
**Return value**
Returns 0 if the delete was successful or -1 if the delete failed.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. |
## apg\$1plan\$1mgmt.evolve\$1plan\$1baselines
Verifies whether an already approved plan is faster or whether a plan identified by the query optimizer as a minimum cost plan is faster.
**Syntax**
```
apg_plan_mgmt.evolve_plan_baselines(
sql_hash,
plan_hash,
min_speedup_factor,
action
)
```
**Return value**
The number of plans that were not faster than the best approved plan.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. Use NULL to mean all plans that have the same sql\$1hash ID value. |
| min\$1speedup\$1factor | The *minimum speedup factor* can be the number of times faster that a plan must be than the best of the already approved plans to approve it. Alternatively, this factor can be the number of times slower that a plan must be to reject or disable it. This is a positive float value. |
| action | The action the function is to perform. Valid values include the following. Case does not matter. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) |
** Usage notes**
Set specified plans to approved, rejected, or disabled based on whether the planning plus execution time is faster than the best approved plan by a factor that you can set. The action parameter might be set to `'approve'` or `'reject'` to automatically approve or reject a plan that meets the performance criteria. Alternatively, it might be set to '' (empty string) to do the performance experiment and produce a report, but take no action.
You can avoid pointlessly rerunning of the `apg_plan_mgmt.evolve_plan_baselines` function for a plan on which it was recently run. To do so, restrict the plans to just the recently created unapproved plans. Alternatively, you can avoid running the `apg_plan_mgmt.evolve_plan_baselines` function on any approved plan that has a recent `last_verified` timestamp.
Conduct a performance experiment to compare the planning plus execution time of each plan relative to the other plans in the baseline. In some cases, there is only one plan for a statement and the plan is approved. In such a case, compare the planning plus execution time of the plan to the planning plus execution time of using no plan.
The incremental benefit (or disadvantage) of each plan is recorded in the `apg_plan_mgmt.dba_plans` view in the `total_time_benefit_ms` column. When this value is positive, there is a measurable performance advantage to including this plan in the baseline.
In addition to collecting the planning and execution time of each candidate plan, the `last_verified` column of the `apg_plan_mgmt.dba_plans` view is updated with the `current_timestamp`. The `last_verified` timestamp might be used to avoid running this function again on a plan that recently had its performance verified.
## apg\$1plan\$1mgmt.get\$1explain\$1plan
Generates the text of an `EXPLAIN` statement for the specified SQL statement.
**Syntax**
```
apg_plan_mgmt.get_explain_plan(
sql_hash,
plan_hash,
[explainOptionList]
)
```
**Return value**
Returns runtime statistics for the specified SQL statements. Use without `explainOptionList` to return a simple `EXPLAIN` plan.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. |
| explainOptionList | A comma-separated list of explain options. Valid values include `'analyze'`, `'verbose'`, `'buffers'`, `'hashes'`, and `'format json'`. If the `explainOptionList` is NULL or an empty string (''), this function generates an `EXPLAIN` statement, without any statistics. |
**Usage notes**
For the `explainOptionList`, you can use any of the same options that you would use with an `EXPLAIN` statement. The Aurora PostgreSQL optimizer concatenates the list of options that you provide to the `EXPLAIN` statement.
## apg\$1plan\$1mgmt.plan\$1last\$1used
Returns the `last_used` date of the specified plan from shared memory.
**Note**
The value in shared memory is always current on the primary DB instance in the DB cluster. The value is only periodically flushed to the `last_used` column of the `apg_plan_mgmt.dba_plans` view.
**Syntax**
```
apg_plan_mgmt.plan_last_used(
sql_hash,
plan_hash
)
```
**Return value**
Returns the `last_used` date.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. |
## apg\$1plan\$1mgmt.reload
Reload plans into shared memory from the `apg_plan_mgmt.dba_plans` view.
**Syntax**
```
apg_plan_mgmt.reload()
```
**Return value**
None.
**Parameters**
None.
** Usage notes**
Call `reload` for the following situations:
+ Use it to refresh the shared memory of a read-only replica immediately, rather than wait for new plans to propagate to the replica.
+ Use it after importing managed plans.
## apg\$1plan\$1mgmt.set\$1plan\$1enabled
Enable or disable a managed plan.
**Syntax**
```
apg_plan_mgmt.set_plan_enabled(
sql_hash,
plan_hash,
[true | false]
)
```
**Return value**
Returns 0 if the setting was successful or -1 if the setting failed.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. |
| enabled | Boolean value of true or false: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) |
## apg\$1plan\$1mgmt.set\$1plan\$1status
Set a managed plan's status to `Approved`, `Unapproved`, `Rejected`, or `Preferred`.
**Syntax**
```
apg_plan_mgmt.set_plan_status(
sql_hash,
plan_hash,
status
)
```
**Return value**
Returns 0 if the setting was successful or -1 if the setting failed.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. |
| status | A string with one of the following values: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) The case you use does not matter, however the status value is set to initial uppercase in the `apg_plan_mgmt.dba_plans` view. For more information about these values, see `status` in [Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition](AuroraPostgreSQL.Optimize.dba_plans_view_Reference.md). |
## apg\$1plan\$1mgmt.update\$1plans\$1last\$1used
Immediately updates the plans table with the `last_used` date stored in shared memory.
**Syntax**
```
apg_plan_mgmt.update_plans_last_used()
```
**Return value**
None.
**Parameters**
None.
** Usage notes**
Call `update_plans_last_used` to make sure queries against the `dba_plans.last_used` column use the most current information. If the `last_used` date isn't updated immediately, a background process updates the plans table with the `last_used` date once every hour (by default).
For example, if a statement with a certain `sql_hash` begins to run slowly, you can determine which plans for that statement were executed since the performance regression began. To do that, first flush the data in shared memory to disk so that the `last_used` dates are current, and then query for all plans of the `sql_hash` of the statement with the performance regression. In the query, make sure the `last_used` date is greater than or equal to the date on which the performance regression began. The query identifies the plan or set of plans that might be responsible for the performance regression. You can use `apg_plan_mgmt.get_explain_plan` with `explainOptionList` set to `verbose, hashes`. You can also use `apg_plan_mgmt.evolve_plan_baselines` to analyze the plan and any alternative plans that might perform better.
The `update_plans_last_used` function has an effect only on the primary DB instance of the DB cluster.
## apg\$1plan\$1mgmt.validate\$1plans
Validate that the optimizer can still recreate plans. The optimizer validates `Approved`, `Unapproved`, and `Preferred` plans, whether the plan is enabled or disabled. `Rejected` plans are not validated. Optionally, you can use the `apg_plan_mgmt.validate_plans` function to delete or disable invalid plans.
**Syntax**
```
apg_plan_mgmt.validate_plans(
sql_hash,
plan_hash,
action)
apg_plan_mgmt.validate_plans(
action)
```
**Return value**
The number of invalid plans.
**Parameters**
****
| Parameter | Description |
| --- | --- |
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. |
| plan\$1hash | The managed plan's plan\$1hash ID. Use NULL to mean all plans for the same sql\$1hash ID value. |
| action | The action the function is to perform for invalid plans. Valid string values include the following. Case does not matter. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) Any other value is treated like the empty string. |
**Usage notes**
Use the form `validate_plans(action)` to validate all the managed plans for all the managed statements in the entire `apg_plan_mgmt.dba_plans` view.
Use the form `validate_plans(sql_hash, plan_hash, action)` to validate a managed plan specified with `plan_hash`, for a managed statement specified with `sql_hash`.
Use the form `validate_plans(sql_hash, NULL, action)` to validate all the managed plans for the managed statement specified with `sql_hash`.
# Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition
The columns of plan information in the `apg_plan_mgmt.dba_plans` view include the following.
| dba\$1plans column | Description |
| --- | --- |
| cardinality\$1error | A measure of the error between the estimated cardinality versus the actual cardinality. *Cardinality* is the number of table rows that the plan is to process. If the cardinality error is large, then it increases the likelihood that the plan isn't optimal. This column is populated by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| compatibility\$1level | This parameter shows when a query plan was last validated. In Aurora PostgreSQL versions 12.19, 13.15, 14.12, 15.7, 16.3, and later, it displays the Aurora version number. For earlier versions, it displays a feature specific version number. Keep this parameter value at its default setting. Aurora PostgreSQL automatically sets and updates this value. |
| created\$1by | The authenticated user (session\$1user) who created the plan. |
| enabled | An indicator of whether the plan is enabled or disabled. All plans are enabled by default. You can disable plans to prevent them from being used by the optimizer. To modify this value, use the [apg\$1plan\$1mgmt.set\$1plan\$1enabled](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.set_plan_enabled) function. |
| environment\$1variables | The PostgreSQL Grand Unified Configuration (GUC) parameters and values that the optimizer has overridden at the time the plan was captured. |
| estimated\$1startup\$1cost | The estimated optimizer setup cost before the optimizer delivers rows of a table. |
| estimated\$1total\$1cost | The estimated optimizer cost to deliver the final table row. |
| execution\$1time\$1benefit\$1ms | The execution time benefit in milliseconds of enabling the plan. This column is populated by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| execution\$1time\$1ms | The estimated time in milliseconds that the plan would run. This column is populated by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| has\$1side\$1effects | A value that indicates that the SQL statement is a data manipulation language (DML) statement or a SELECT statement that contains a VOLATILE function. |
| last\$1used | This value is updated to the current date whenever the plan is either executed or when the plan is the query optimizer's minimum-cost plan. This value is stored in shared memory and periodically flushed to disk. To get the most up-to-date value, read the date from shared memory by calling the function apg\$1plan\$1mgmt.plan\$1last\$1used(sql\$1hash, plan\$1hash) instead of reading the last\$1used value. For additional information, see the [apg\$1plan\$1mgmt.plan\$1retention\$1period](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.plan_retention_period) parameter. |
| last\$1validated | The most recent date and time when it was verified that the plan could be recreated by either the [apg\$1plan\$1mgmt.validate\$1plans](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.validate_plans) function or the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| last\$1verified | The most recent date and time when a plan was verified to be the best-performing plan for the specified parameters by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| origin | How the plan was captured with the [apg\$1plan\$1mgmt.capture\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.capture_plan_baselines) parameter. Valid values include the following: `M` – The plan was captured with manual plan capture. `A` – The plan was captured with automatic plan capture. |
| param\$1list | The parameter values that were passed to the statement if this is a prepared statement. |
| plan\$1created | The date and time the plan that was created. |
| plan\$1hash | The plan identifier. The combination of plan\$1hash and sql\$1hash uniquely identifies a specific plan. |
| plan\$1outline | A representation of the plan that is used to recreate the actual execution plan, and that is database-independent. Operators in the tree correspond to operators that appear in the EXPLAIN output. |
| planning\$1time\$1ms | The actual time to run the planner, in milliseconds. This column is populated by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
| queryId | A statement hash, as calculated by the pg\$1stat\$1statements extension. This isn't a stable or database-independent identifier because it depends on object identifiers (OIDs). The value will be 0 if compute\$1query\$1id is off when capturing the query plan. |
| sql\$1hash | A hash value of the SQL statement text, normalized with literals removed. |
| sql\$1text | The full text of the SQL statement. |
| status | A plan's status, which determines how the optimizer uses a plan. Valid values include the following. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.dba_plans_view_Reference.html) |
| stmt\$1name | The name of the SQL statement within a PREPARE statement. This value is an empty string for an unnamed prepared statement. This value is NULL for a nonprepared statement. |
| total\$1time\$1benefit\$1ms | The total time benefit in milliseconds of enabling this plan. This value considers both planning time and execution time. If this value is negative, there is a disadvantage to enabling this plan. This column is populated by the [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Functions.md#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines) function. |
# Advanced features in Query Plan Management
Following you can find information about the advanced Aurora PostgreSQL Query Plan Management (QPM) features:
**Topics**
+ [Capturing Aurora PostgreSQL execution plans in Replicas](AuroraPostgreSQL.QPM.Plancapturereplicas.md)
+ [Supporting table partition](AuroraPostgreSQL.QPM.Partitiontable.md)
# Capturing Aurora PostgreSQL execution plans in Replicas
QPM (Query Plan Management) allows you to capture the query plans generated by Aurora Replicas and stores them on the primary DB instance of the Aurora DB cluster. You can collect the query plans from all the Aurora Replicas, and maintain a set of optimal plans in a central persistent table on the primary instance. You can then apply these plans on other Replicas when needed. This helps you to maintain the stability of execution plans and improve performance of the queries across the DB clusters and engine versions.
**Topics**
+ [Prerequisites](#AuroraPostgreSQL.QPM.Plancapturereplicas.Prereq)
+ [Managing plan capture for Aurora Replicas](#AuroraPostgreSQL.QPM.Plancapturereplicas.managing)
+ [Troubleshooting](#AuroraPostgreSQL.QPM.Plancapturereplicas.Troubleshooting)
## Prerequisites
**Turn on `capture_plan_baselines parameter` in Aurora Replica** - Set `capture_plan_baselines` parameter to automatic or manual to capture plans in Aurora Replicas. For more information, see [apg\$1plan\$1mgmt.capture\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.capture_plan_baselines).
**Install postgres\$1fdw extension** - You must install `postgres_fdw` foreign data wrapper extension to capture plans in Aurora Replicas. Run the following command in each database, to install the extension.
```
postgres=> CREATE EXTENSION IF NOT EXISTS postgres_fdw;
```
## Managing plan capture for Aurora Replicas
**Turn on plan capture for Aurora Replicas**
You must have `rds_superuser` privileges to create or remove Plan Capture in Aurora Replicas. For more information on user roles and permissions, see [Understanding PostgreSQL roles and permissions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.html).
To capture plans, call the function apg\$1plan\$1mgmt.create\$1replica\$1plan\$1capture in the writer DB instance, as shown in the following:
```
postgres=> CALL apg_plan_mgmt.create_replica_plan_capture('endpoint', 'password');
```
+ endpoint - Aurora Global Database writer endpoint or cluster\$1endpoint provides failover support for Plan Capture in Aurora Replicas.
For more information on the Aurora Global Database writer endpoint, see [Viewing the endpoints of an Amazon Aurora global database](aurora-global-database-connecting.md#viewing-endpoints).
For more information about cluster endpoints, see [Cluster endpoints for Amazon Aurora](Aurora.Endpoints.Cluster.md).
+ password - We recommend you to follow the below guidelines while creating the password to enhance the security:
+ It must contain at least 8 characters.
+ It must contain at least one uppercase, one lowercase letter, and one number.
+ It must have at least one special character (`?`, `!`, `#`, `<`, `>`, `*`, and so on).
**Note**
If you change the endpoint, password, or port number, you must run `apg_plan_mgmt.create_replica_plan_capture()` again with the endpoint and password to re-initialize the plan capture. If not, capturing plans from Aurora Replicas will fail.
**Turn off plan capture for Aurora Replicas**
You can turn off `capture_plan_baselines` parameter in Aurora Replica by setting its value to `off` in the Parameter group.
**Remove plan capture for Aurora Replicas**
You can completely remove Plan Capture in Aurora Replicas but make sure before you do. To remove plan capture, call `apg_plan_mgmt.remove_replica_plan_capture` as shown:
```
postgres=> CALL apg_plan_mgmt.remove_replica_plan_capture();
```
You must call apg\$1plan\$1mgmt.create\$1replica\$1plan\$1capture() again to turn on plan capture in Aurora Replicas with the endpoint and password.
## Troubleshooting
Following, you can find troubleshooting ideas and workarounds if the plan is not captured in Aurora Replicas as expected.
+ **Parameter settings** - Check if the `capture_plan_baselines` parameter is set to proper value to turn on plan capture.
+ **`postgres_fdw` extension is installed** - Use the following query to check if `postgres_fdw` is installed.
```
postgres=> SELECT * FROM pg_extension WHERE extname = 'postgres_fdw'
```
+ **create\$1replica\$1plan\$1capture() is called** - Use the following command to check if the user mapping exits. Otherwise, call `create_replica_plan_capture()` to initialize the feature.
```
postgres=> SELECT * FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';
```
+ **Endpoint and port number** - Check if the endpoint and port number are appropriate. There won't be any error message displayed if these values are incorrect.
Use the following command to verify if the endpoint used in the create() and to check which database it resides in:
```
postgres=> SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';
```
+ **reload()** - You must call apg\$1plan\$1mgmt.reload() after calling apg\$1plan\$1mgmt.delete\$1plan() in Aurora Replicas to make the delete function effective. This ensures that the change has been successfully implemented.
+ **Password** - You must enter password in create\$1replica\$1plan\$1capture() as per the guidelines mentioned. Otherwise, you will receive an error message. For more information,see [Managing plan capture for Aurora Replicas](#AuroraPostgreSQL.QPM.Plancapturereplicas.managing). Use another password that aligns with the requirements.
+ **Cross-Region connection** - Plan capture in Aurora Replicas is also supported in Aurora global database, where writer instance and Aurora Replicas can be in different regions. Ensure that you use the Aurora Global Database writer endpoint to maintain connectivity after failover or switchover events. For more information on the Aurora Global Database endpoints, see [Viewing the endpoints of an Amazon Aurora Global Database](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-connecting.html#viewing-endpoints). The writer instance and cross-Region Replica must be able to communicate, using VPC Peering. For more information, see [VPC peering](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html). If a cross-Region failover happens, you must reconfigure the endpoint to new primary DB cluster endpoint.
**Note**
When using a cluster endpoint instead of an Aurora Global Database writer endpoint, you'll need to update the cluster endpoint after performing either a global failover or switchover operation.
# Supporting table partition
Aurora PostgreSQL Query Plan Management (QPM) supports declarative table partitioning in the following versions:
+ 15.3 and higher 15 versions
+ 14.8 and higher 14 versions
+ 13.11 and higher 13 versions
For more information, see [Table Partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html).
**Topics**
+ [Setting up table partition](#AuroraPostgreSQL.QPM.Partitiontable.setup)
+ [Capturing plans for table partition](#AuroraPostgreSQL.QPM.Partitiontable.capture)
+ [Enforcing a table partition plan](#AuroraPostgreSQL.QPM.Partitiontable.enforcement)
+ [Naming Convention](#AuroraPostgreSQL.QPM.Partitiontable.naming.convention)
## Setting up table partition
To set up table partition in Aurora PostgreSQL QPM, do as follows:
1. Set `apg_plan_mgmt.plan_hash_version` to 3 or more in the DB cluster parameter group.
1. Navigate to a database that uses Query Plan Management and has entries in `apg_plan_mgmt.dba_plans` view.
1. Call `apg_plan_mgmt.validate_plans('update_plan_hash')` to update the `plan_hash` value in the plans table.
1. Repeat steps 2-3 for all databases with Query Plan Management enabled that have entries in `apg_plan_mgmt.dba_plans` view.
For more information on these parameters, see [Parameter reference for Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.Parameters.md).
## Capturing plans for table partition
In QPM, different plans are distinguished by their `plan_hash` value. To understand how the `plan_hash` changes, you must first understand similar kind of plans.
The combination of access methods, digit-stripped index names and digit-stripped partition names, accumulated at the Append node level must be constant for plans to be considered the same. The specific partitions accessed in the plans are not significant. In the following example, a table `tbl_a` is created with 4 partitions.
```
postgres=>create table tbl_a(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table tbl_a1 partition of tbl_a for values from (0) to (1000);
CREATE TABLE
postgres=>create table tbl_a2 partition of tbl_a for values from (1001) to (2000);
CREATE TABLE
postgres=>create table tbl_a3 partition of tbl_a for values from (2001) to (3000);
CREATE TABLE
postgres=>create table tbl_a4 partition of tbl_a for values from (3001) to (4000);
CREATE TABLE
postgres=>create index t_i on tbl_a using btree (i);
CREATE INDEX
postgres=>create index t_j on tbl_a using btree (j);
CREATE INDEX
postgres=>create index t_k on tbl_a using btree (k);
CREATE INDEX
```
The following plans are considered the same because a single scan method is being used to scan `tbl_a` irrespective of the number of partitions that the query looks up.
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 999 and j < 9910 and k > 50;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on tbl_a1 tbl_a
Filter: ((i >= 990) AND (i <= 999) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(3 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
QUERY PLAN
-------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(6 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a3 tbl_a_3
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(8 rows)
```
The following 3 plans are also considered the same because at the parent level, the access methods, digit-stripped index names and digit-stripped partition names are `SeqScan tbl_a`, `IndexScan (i_idx) tbl_a`.
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a2_i_idx on tbl_a2 tbl_a_2
Index Cond: ((i >= 990) AND (i <= 1100))
Filter: ((j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -993736942
(7 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
Index Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a3 tbl_a_3
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -993736942
(11 rows)
```
Irrespective of different order and number of occurrences in child partitions, the access methods, digit-stripped index names and digit-stripped partition names are constant at the parent level for each of the above plans.
However, the plans would be considered different if any of the following conditions are met:
+ Any additional access methods are used in the plan.
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Bitmap Heap Scan on tbl_a3 tbl_a_3
Recheck Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
-> Bitmap Index Scan on tbl_a3_i_idx
Index Cond: ((i >= 990) AND (i <= 2100))
SQL Hash: 1553185667, Plan Hash: 1134525070
(11 rows)
```
+ Any of the access methods in the plan are not used anymore.
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(6 rows)
```
+ The index associated with an index method is changed.
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Seq Scan on tbl_a1 tbl_a_1
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a2_j_idx on tbl_a2 tbl_a_2
Index Cond: (j < 9910)
Filter: ((i >= 990) AND (i <= 1100) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -993343726
(7 rows)
```
## Enforcing a table partition plan
Approved plans for partitioned tables are enforced with positional correspondence. The plans are not specific to the partitions, and can be enforced on partitions other than the plans referenced in the original query. Plans also have the capability of being enforced for queries accessing a different number of partitions than the original approved outline.
For example, if the approved outline is for the following plan:
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
Index Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)
```
Then, this plan can be enforced on SQL queries referencing 2, 4, or more partitions as well. The possible plans that could arise from these scenarios for 2 and 4 partition access are:
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
QUERY PLAN
----------------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 1100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(8 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a4 tbl_a_4
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(12 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
QUERY PLAN
----------------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
-> Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
-> Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
Index Cond: ((i >= 990) AND (i <= 3100))
Filter: ((j < 9910) AND (k > 50))
Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(14 rows)
```
Consider another approved plan with different access methods for each partition:
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
-> Bitmap Heap Scan on tbl_a3 tbl_a_3
Recheck Cond: ((i >= 990) AND (i <= 2100))
Filter: ((j < 9910) AND (k > 50))
-> Bitmap Index Scan on tbl_a3_i_idx
Index Cond: ((i >= 990) AND (i <= 2100))
SQL Hash: 1553185667, Plan Hash: 2032136998
(12 rows)
```
In this case, any plan that reads from two partitions would fail to be enforced. Unless all of the (access method, index name) combinations from the approved plan are usable, the plan cannot be enforced. For example, the following plans have different plan hashes and the approved plan can't be enforced in these cases:
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
QUERY PLAN
-------------------------------------------------------------------------
Append
-> Bitmap Heap Scan on tbl_a1 tbl_a_1
Recheck Cond: ((i >= 990) AND (i <= 1900))
Filter: ((j < 9910) AND (k > 50))
-> Bitmap Index Scan on tbl_a1_i_idx
Index Cond: ((i >= 990) AND (i <= 1900))
-> Bitmap Heap Scan on tbl_a2 tbl_a_2
Recheck Cond: ((i >= 990) AND (i <= 1900))
Filter: ((j < 9910) AND (k > 50))
-> Bitmap Index Scan on tbl_a2_i_idx
Index Cond: ((i >= 990) AND (i <= 1900))
Note: This is not an Approved plan. No usable Approved plan was found.
SQL Hash: 1553185667, Plan Hash: -568647260
(13 rows)
```
```
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
QUERY PLAN
--------------------------------------------------------------------------
Append
-> Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
Index Cond: ((i >= 990) AND (i <= 1900))
Filter: ((j < 9910) AND (k > 50))
-> Seq Scan on tbl_a2 tbl_a_2
Filter: ((i >= 990) AND (i <= 1900) AND (j < 9910) AND (k > 50))
Note: This is not an Approved plan. No usable Approved plan was found.
SQL Hash: 1553185667, Plan Hash: -496793743
(8 rows)
```
## Naming Convention
For QPM to enforce a plan with declarative partitioned tables, you must follow specific naming rules for parent tables, table partitions, and indexes:
+ **Parent table names** – These names must differ by alphabets or special characters, and not by just digits. For example, tA, tB, and tC are acceptable names for separate parent tables while t1, t2, and t3 are not.
+ **Individual partition table names** – Partitions of the same parent should differ from one another by digits only. For example, acceptable partition names of tA could be tA1, tA2 or t1A, t2A or even multiple digits.
Any other differences (letters, special characters) will not guarantee plan enforcement.
+ **Index names** – In partition table hierarchy, make sure that all indexes have unique names. This means that the non-numeric parts of the names must be different. For example, if you have a partitioned table named `tA` with an index named `tA_col1_idx1`, you can't have another index named `tA_col1_idx2`. However, you can have an index called `tA_a_col1_idx2` because the non-numeric part of the name is unique. This rule applies to indexes created on both the parent table and individual partition tables.
Failure to adhere to the above naming conventions may result in failure of approved plans enforcement. The following example illustrates such a failed enforcement:
```
postgres=>create table t1(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table t1a partition of t1 for values from (0) to (1000);
CREATE TABLE
postgres=>create table t1b partition of t1 for values from (1001) to (2000);
CREATE TABLE
postgres=>SET apg_plan_mgmt.capture_plan_baselines TO 'manual';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 0;
QUERY PLAN
--------------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on t1a t1_1
Filter: (i > 0)
-> Seq Scan on t1b t1_2
Filter: (i > 0)
SQL Hash: -1720232281, Plan Hash: -1010664377
(7 rows)
```
```
postgres=>SET apg_plan_mgmt.use_plan_baselines TO 'on';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 1000;
QUERY PLAN
-------------------------------------------------------------------------
Aggregate
-> Seq Scan on t1b t1
Filter: (i > 1000)
Note: This is not an Approved plan. No usable Approved plan was found.
SQL Hash: -1720232281, Plan Hash: 335531806
(5 rows)
```
Even though the two plans might appear identical, their `Plan Hash` values are different due to the names of the child tables. The table names vary by alpha characters instead of just digits leading to an enforcement failure.