Sign In to the Console
Amazon Aurora
User Guide for Aurora (API Version 2014-10-31)

AWS Documentation » Amazon Relational Database Service (RDS) » User Guide for Aurora » Working with Amazon Aurora PostgreSQL » Managing Query Execution Plans for Aurora PostgreSQL » Parameter Reference for Query Plan Management

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:

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

You must set apg_plan_mgmt.max_databases at the cluster or DB instance level and it requires a DB instance restart for a new value to take effect.

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

You must set apg_plan_mgmt.max_plans at the cluster or DB instance level and it requires a DB instance restart for a new value to take effect.

Value Default Description
integer 1000 A positive integer value greater or equal to 10.

apg_plan_mgmt.pgss_min_calls

Sets the minimum number of pg_stat_statements calls that are eligible for plan capture. 

SET apg_plan_mgmt.pgss_min_calls = integer-value;

Value Default Description
Positive integer 2 A positive integer value greater or equal to 2.

Usage Notes

Requires installation of the pg_stat_statements extension. For more information, see the PostgreSQL pg_stats_statements documentation.

apg_plan_mgmt.pgss_min_mean_time_ms

Minimum value of the pg_stat_statements mean_time to be eligible for plan capture. 

SET apg_plan_mgmt.pgss_min_mean_time_ms = double-value;

Value Default Description
Positive number 0.0 A positive number value greater or equal to 0.0.

Usage Notes

Requires installation of the pg_stat_statements extension. For more information, see the PostgreSQL pg_stats_statements documentation.

apg_plan_mgmt.pgss_min_stddev_time_ms

Minimum value of the pg_stat_statements stddev_time to be eligible for plan capture. 

SET apg_plan_mgmt.pgss_min_stddev_time_ms = double-value;

Value Default Description
Positive number 0.0 A positive number value greater or equal to 0.0.

Usage Notes

Requires installation of the pg_stat_statements extension. For more information, see the PostgreSQL pg_stats_statements documentation.

apg_plan_mgmt.pgss_min_total_time_ms

Minimum value of the pg_stat_statements total_time to be eligible for plan capture. 

SET apg_plan_mgmt.pgss_min_total_time_ms = double-value;

Value Default Description
Positive number 0.0 A positive number value greater or equal to 0.0.

Usage Notes

Requires installation of the pg_stat_statements extension. For more information, see the PostgreSQL pg_stats_statements documentation.

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. 

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_hint_plan.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.

Example

With this example, the optimizer will run 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_hint_plan.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.

  1. The minimum-cost preferred plan that is valid and enabled.

  2. The minimum cost approved plan that is valid and enabled.

  3. 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.

  4. The optimizer's generated minimum-cost plan.
false (Default) Do not use managed plans. The optimizer will use 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.