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 in Amazon Aurora and DB parameter groups for
Amazon Aurora DB instances.
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 | Apply this setting at either the session level or in a parameter group to capture plans that are used two or more times. |
| manual | Apply this setting at either the session level or in a parameter group to capture plans that are used one or more times. | |
| off | Turns off plan capture. |
For more information, see Capturing Aurora PostgreSQL execution plans.
apg_plan_mgmt.plan_capture_threshold
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+308 | Sets the threshold of the |
For more information, see Examining Aurora PostgreSQL query plans in the dba_plans view.
apg_plan_mgmt.explain_hashes
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.
| Default | Allowed values | Description |
|---|---|---|
| 0 | 0 (off) | EXPLAIN does not show sql_hash and plan_hash without hashes true option. |
| 1 (on) | EXPLAIN shows sql_hash and plan_hash without hashes true option. |
apg_plan_mgmt.log_plan_enforcement_result
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.
| Default | Allowed values | Description |
|---|---|---|
| none | none | Does not show any plan enforcement result in log files. |
| on_error | Only shows plan enforcement result in log files when QPM fails to use managed plans. | |
| all | Shows all plan enforcement results in log files including both successes and failures. |
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_hash_version
Specifies the use cases that the plan_hash 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_hash values in each database with apg_plan_mgmt installed and entries in the
plans table. For more information, see Validating
plans
| Default | Allowed values | Description |
|---|---|---|
| 1 | 1 | Default plan_hash calculation. |
| 2 | plan_hash calculation modified for multi-schema support. | |
| 3 | plan_hash calculation modified for multi-schema support and partitioned table support. | |
| 4 | plan_hash calculation modified for parallel operators and to support materialize nodes. |
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, 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_plans view.
apg_plan_mgmt.unapproved_plan_execution_threshold
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_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 Improving Aurora PostgreSQL query plans.
auto_explain.hashes
Specifies if the auto_explain output shows sql_hash and plan_hash. Changing the value of this parameter doesn't require a reboot.
| Default | Allowed values | Description |
|---|---|---|
| 0(off) | 0(off) | auto_explain result does not show
sql_hash and plan_hash. |
| 1(on) | auto_explain result shows sql_hash and
plan_hash . |
