Parameter reference for query plan management

The apg_plan_mgmt extension provides the following parameters.

Set the query plan management parameters at the appropriate level:

• Set at the cluster-level to provide the same settings for all DB instances. For more information, see Modifying parameters in a DB cluster parameter group.

• Set at the DB instance level to isolate the settings to an individual DB instance. For more information, see Modifying parameters in a DB parameter group.

• Set in a specific client session such as in psql, to isolate the values to only that session.

You must be set the parameters apg_plan_mgmt.max_databases and apg_plan_mgmt.max_plans at the cluster or DB instance level.

apg_plan_mgmt.capture_plan_baselines

Enable execution plan capture for SQL statements.

SET apg_plan_mgmt.capture_plan_baselines = [off | automatic |manual];

Value	Description
off	Disable plan capture. This is the default.
automatic	Enable plan capture for subsequent SQL statements that satisfy the eligibility criteria.
manual	Enable plan capture for subsequent SQL statements.

apg_plan_mgmt.max_databases

Sets the maximum number of database objects that might use query plan management. A database object is what gets created with the CREATE DATABASE SQL statement.

Important

Set apg_plan_mgmt.max_databases at the cluster or DB instance level. It requires a DB instance restart for a new value to take effect.

SET apg_plan_mgmt.max_databases = integer-value;

Value	Default	Description
Positive integer	10	A positive integer value.

apg_plan_mgmt.max_plans

Sets the maximum number of plans that might be captured in the apg_plan_mgmt.dba_plans view.

Important

Set apg_plan_mgmt.max_plans at the cluster or DB instance level. It requires a DB instance restart for a new value to take effect. We recommend setting this parameter to 10000 or higher for all Aurora PostgreSQL versions.

SET apg_plan_mgmt.max_plans = integer-value;

Value	Default	Description
integer	10000 for Aurora PostgreSQL version 11 and higher 1000 for Aurora PostgreSQL version 10 and lower	A positive integer value greater or equal to 10.

apg_plan_mgmt.plan_retention_period

The number of days plans are kept in the apg_plan_mgmt.dba_plans view before being automatically deleted. A plan is deleted when the current date is this many days since the plan's last_used date. The last_used date is the most recent date that either the optimizer chose a plan as the minimum cost plan or that the plan was executed.

SET apg_plan_mgmt.plan_retention_period = integer-value;

Value	Default	Description
Positive integer	32	A positive integer value greater or equal to 32, representing days.

apg_plan_mgmt.unapproved_plan_execution_threshold

An estimated total plan cost threshold, below which the optimizer runs an unapproved plan. By default, the optimizer does not run unapproved plans. However, you can set an execution threshold for your fastest unapproved plans. With this setting, the optimizer bypasses the overhead of enforcing only approved plans.

SET apg_plan_mgmt.unapproved_plan_execution_threshold = integer-value;

Value	Default	Description
Positive integer	0	A positive integer value greater or equal to 0. A value of 0 means no unapproved plans run when use_plan_baselines is true.

With the following example, the optimizer runs an unapproved plan if the estimated cost is less than 550, even if use_plan_baselines is true.

SET apg_plan_mgmt.unapproved_plan_execution_threshold = 550;

apg_plan_mgmt.use_plan_baselines

Enforce the optimizer to use managed plans for managed statements.

SET apg_plan_mgmt.use_plan_baselines = [true | false];

Value	Description
true	Enforce the use of managed plans. When a SQL statement runs and it is a managed statement in the apg_plan_mgmt.dba_plans view, the optimizer chooses a managed plan in the following order. The minimum-cost preferred plan that is valid and enabled. The minimum cost approved plan that is valid and enabled. The minimum cost unapproved plan that is valid, enabled, and that meets the threshold, if set with the apg_plan_mgmt.unapproved_plan_execution_threshold parameter. The optimizer's generated minimum-cost plan.
false	(Default) Do not use managed plans. The optimizer uses its generated minimum-cost plan.

Usage notes

When use_plan_baselines is true, then the optimizer makes the following execution decisions:

1. If the estimated cost of the optimizer's plan is below the unapproved_plan_execution_threshold, then execute it, else

2. If the plan is approved or preferred, then execute it, else

3. Execute a minimum-cost preferred plan, if possible, else

4. Execute a minimum-cost approved plan, if possible, else

5. Execute the optimizer's minimum-cost plan.