Using Aurora PostgreSQL managed plans

To get the optimizer to use captured plans for your managed statements, set the parameter apg_plan_mgmt.use_plan_baselines to true. The following is a local instance example.

SET apg_plan_mgmt.use_plan_baselines = true;

While the application runs, this setting causes the optimizer to use the minimum-cost, preferred, or approved plan that is valid and enabled for each managed statement.

Analyzing the optimizer's chosen plan

When the apg_plan_mgmt.use_plan_baselines parameter is set to true, you can use EXPLAIN ANALYZE SQL statements to cause the optimizer to show the plan it would use if it were to run the statement. The following is an example.

EXPLAIN ANALYZE EXECUTE rangeQuery (1,10000);

                                                    QUERY PLAN           
--------------------------------------------------------------------------
 Aggregate  (cost=393.29..393.30 rows=1 width=8) (actual time=7.251..7.251 rows=1 loops=1)
   ->  Index Only Scan using t1_pkey on t1 t  (cost=0.29..368.29 rows=10000 width=0) (actual time=0.061..4.859 rows=10000 loops=1)
Index Cond: ((id >= 1) AND (id <= 10000))         
         Heap Fetches: 10000
 Planning time: 1.408 ms
 Execution time: 7.291 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1984047223, Plan Hash: 512153379

The output shows the Approved plan from the baseline that would run. However, the output also shows that it found a lower-cost plan. In this case, you capture this new minimum cost plan by turning on automatic plan capture as described in Automatically capturing plans.

New plans are always captured by the optimizer as Unapproved. Use the apg_plan_mgmt.evolve_plan_baselines function to compare plans and change them to approved, rejected, or disabled. For more information, see Evaluating plan performance.

How the optimizer chooses which plan to run

The cost of an execution plan is an estimate that the optimizer makes to compare different plans. When calculating a plan's cost, the optimizer includes factors such as CPU and I/O operations required by that plan. To learn more about PostgreSQL query planner cost estimates, see Query Planning in the PostgreSQL documentation.

The following image shows how a plan is chosen for a given SQL statement when query plan management is active, and when it's not.

The flow is as follows:

1. The optimizer generates a minimum-cost plan for the SQL statement.

2. If query plan management isn't active, the optimizer's plan is run immediately (A. Run Optimizer's plan). Query plan management is inactive when the apg_plan_mgmt.capture_plan_baselines and the apg_plan_mgmt.use_plan_baselines parameters are both at their default settings ("off" and "false," respectively).

   Otherwise, query plan management is active. In this case, the SQL statement and the optimizer's plan for it are further assessed before a plan is chosen.

   Tip

   Database users with the apg_plan_mgmt role can pro-actively compare plans, change status of plans, and force the use of specific plans as needed. For more information, see Maintaining Aurora PostgreSQL execution plans.

3. The SQL statement might already have plans that were stored by query plan management in the past. Plans are stored in the apg_plan_mgmt.dba_plans, along with information about the SQL statements that were used to create them. Information about a plan includes its status. A plan's status can determine whether it's used or not, as follows.

   1. If the plan isn't among the stored plans for the SQL statement, it means that it's the first time this particular plan was generated by the optimizer for the given SQL statement. The plan is sent to Capture Plan Processing (4).

   2. If the plan is among the stored plans and its status is Approved or Preferred, the plan is run (A. Run Optimizer's plan).

      If the plan is among the stored plans but it's neither Approved nor Preferred, the plan is sent to Capture Plan Processing (4).

4. When a plan is captured for the first time for a given SQL statement, the plan's status is always set to Approved (P1). If the optimizer subsequently generates the same plan for the same SQL statement, the status of that plan is changed to Unapproved (P1+n).

   With the plan captured and its status updated, the evaluation continues at the next step (5).

5. A plan's baseline consists of the history of the SQL statement and its plans at various states. Query plan management can take the baseline into account when choosing a plan, depending on whether the use plan baselines option is turned on or not, as follows.

   • Use plan baselines is "off" when the apg_plan_mgmt.use_plan_baselines parameter is set to its default value (false). The plan isn't compared to the baseline before it's run (A. Run Optimizer's plan).

   • Use plan baselines is "on" when the apg_plan_mgmt.use_plan_baselines parameter is set to true. The plan is further assessed using the baseline (6).

6. The plan is compared to other plans for the statement in the baseline.

   1. If the optimizer's plan is among the plans in the baseline, its status is checked (7a).

   2. If the optimizer's plan isn't among plans in the baseline, the plan is added to the plans for the statement as a new Unapproved plan.

7. The plan's status is checked to determine only if it's Unapproved.

   1. If the plan's status is Unapproved, the plan's estimated cost is compared to the cost estimate specified for the unapproved execution plan threshold.

      • If the plan's estimated cost is below the threshold, the optimizer uses it even though it's an Unapproved plan (A. Run Optimizer's plan). Generally, the optimizer won't run an Unapproved plan. However, when the apg_plan_mgmt.unapproved_plan_execution_threshold parameter specifies a cost threshold value, the optimizer compares the Unapproved plan's cost to the threshold. If the estimated cost is less than the threshold, the optimizer runs the plan. For more information, see apg_plan_mgmt.unapproved_plan_execution_threshold.

      • If the plan's estimated cost isn't below the threshold, the plan's other attributes are checked (8a).

   2. If the plan's status is anything other than Unapproved, its other attributes are checked (8a).

8. The optimizer won't use a plan that's disabled. That is, the plan that has its enable attribute set to 'f' (false). The optimizer also won't use a plan that has a status of Rejected.

   The optimizer can't use any plans that aren't valid. Plans can become invalid over time when the objects that they depend on, such as indexes and table partitions, are removed or deleted.

   1. If the statement has any enabled and valid Preferred plans, the optimizer chooses the minimum-cost plan from among the Preferred plans stored for this SQL statement. The optimizer then runs the minimum-cost Preferred plan.

   2. If the statement doesn't have any enabled and valid Preferred plans, it's assessed in the next step (9).

9. If the statement has any enabled and valid Approved plans, the optimizer chooses the minimum-cost plan from among the Approved plans stored for this SQL statement. The optimizer then runs the minimum-cost Approved plan.

   If the statement doesn't have any valid and enabled Approved plans, the optimizer uses the minimum cost plan (A. Run Optimizer's plan).