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. 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 group and
Working with DB parameter groups.
Parameters
apg_plan_mgmt.capture_plan_baselines
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_plan_mgmt.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.
| Default | Allowed values | Description |
|---|---|---|
| off | automatic | Turns on plan capture for all databases on the DB instance. Collects a plan for each SQL statement that runs two or more times. Use this setting for large or evolving workloads to provide plan stability. |
| manual | Turns on plan capture for subsequent statements only, until you turn it off again. Using this setting lets you capture query execution plans for specific critical SQL statements only or for known problematic queries. | |
| off | Turns off plan capture. |
For more information, see Capturing Aurora PostgreSQL execution plans.
apg_plan_mgmt.max_databases
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_plan_mgmt.max_plans
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 Default for Aurora PostgreSQL version 10 and older versions is 1000. |
For more information, see Examining Aurora PostgreSQL query plans in the dba_plans view.
apg_plan_mgmt.plan_retention_period
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, 32 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 | 32-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_plans view.
apg_plan_mgmt.unapproved_plan_execution_threshold
Specifies a threshold below which an Unapproved plan can be used
by the optimizer. By default, the optimizer doesn't run Unapproved plans. If you have an
Unapproved plan that you suspect might be better than an Approved plan, you can use
this parameter to sidestep the optimizer's default behavior. 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_plans view.
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.
apg_plan_mgmt.use_plan_baselines
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. To find an image detailing this process, see
How the optimizer
chooses which plan to run.
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.
| Default | Allowed values | Description |
|---|---|---|
| false | true | Use an Approved, Preferred, or Unapproved plan from the apg_plan_mgmt.dba_plans. If none of those meet
all evaluation criterion for the optimizer, it can then use its own generated minimum-cost plan. For more information,
see How the optimizer
chooses which plan to run. |
| false | Use the minimum cost plan generated by the optimizer. |
You can evaluate response times of different captured plans and change plan status, as needed. For more information, see Maintaining Aurora PostgreSQL execution plans.