# 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)