Sign In to the Console
Amazon Aurora
User Guide for Aurora

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
The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

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

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.

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.

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.

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