Use cases for query plan management in Amazon Aurora PostgreSQL

This blog post is the second in a series. The previous blog post talks about the need for the stable, consistent database performance amid changes that otherwise can cause regression on execution plans of the SQL statements. It also demonstrates how query plan management (QPM) for Amazon Aurora with PostgreSQL compatibility helps you overcome plan stability and plan adaptability issues.

In this post, we continue to discuss Aurora PostgreSQL QPM features. In particular, we discuss how these features help you achieve plan stability and adaptability for a variety of advanced use cases.

Use case #1: Helping ensure plan stability and adaptability with QPM manual capture

For the first use case, we walk through an example on how QPM helps ensure plan stability. Then, you put into place the changes that we discussed in the previous post, Introduction to Aurora PostgreSQL Query Plan Management. Without QPM, these changes can result in plan regression.

In most cases, you set up QPM to use automatic plan capture so that plans are captured for all statements that run two or more times. However, you can also capture plans for a specific set of statements that you specify manually. To do this, you set capture_plan_baselines = off by default. At the session level, capture_plan_baselines = manual at the session level. How to do it is described below.

Enable manual plan capture to instruct QPM to capture the execution plan of the desired SQL statements manually.
```
pg105db=> SET apg_plan_mgmt.capture_plan_baselines = manual;
SET
```
SQL

Run an explain plan for the query so that QPM can capture the plan of the query (the following output for the explain plan is truncated for brevity).

pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 
                      QUERY PLAN                                                  
----------------------------------------------------------------------
 Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))
………………………………………………………………………..
SQL Hash: 1561242727, Plan Hash: -1990695905

Disable manual capture of the plan after you capture the execution plan for the desired SQL statement.
```
pg105db=> SET apg_plan_mgmt.capture_plan_baselines = off;
SET
```
SQL

View the captured query plan for the query that ran previously. The plan_outline column in the table apg_plan_mgmt.dba_plans shows the entire plan for the SQL. For brevity, the plan_outline isn’t shown here. Instead, plan_hash_value from the explain plan preceding is compared with plan_hash from the output of the apg_plan_mgmt.dba_plans query.

pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

 sql_hash     |  plan_hash  |  status  | cost 	 |  sql_text                                                                                                         
                                                                      
--------------+-------------+----------+---------+-----------------------------------------------------------
 
1561242727	  -1990695905	 Approved 	 23228.14    select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);

To instruct the query optimizer to use the approved or preferred captured plans for your managed statements, set the parameter apg_plan_mgmt.use_plan_baselines to true.
```
pg105db=> SET apg_plan_mgmt.use_plan_baselines = true;
SET
```
SQL

View the explain plan output to see that the QPM approved plan is used by the query optimizer.

pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                      QUERY PLAN                                                  
----------------------------------------------------------------------
 Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))
………………………………………………………………………..
SQL Hash: 1561242727, Plan Hash: -1990695905

Create a new index on the pgbench_history table column “mtime” to change the planner configuration and force the query optimizer to generate a new plan.
```
pg105db=> create index pgbench_hist_mtime on pgbench_history(mtime);
CREATE INDEX
```
SQL

View the explain plan output to see that QPM detects a new plan but still uses the approved plan and maintains the plan stability.

pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                      QUERY PLAN                                                  
Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))

………………………………………………………………………..

Note: For this example, an approved plan was used instead of the minimum cost plan.
SQL Hash: 1561242727, Plan Hash: -1990695905, Minimum Cost Plan Hash: -794604077

Run the following SQL query to view the new plan and status of the plan. To ensure plan stability, QPM stores all the newly generated plans for a managed query in QPM as unapproved plans. The following output shows that there are two different execution plans stored for the same managed statement, as shown by the two different plan_hash values. Although the new execution plan has the minimum cost (lower than the approved plan), QPM continues to ignore the unapproved plans to maintain plan stability.The plan_outline column in the table apg_plan_mgmt.dba_plans shows the entire plan for the SQL. For the sake of brevity, the plan_outline is not shown here. Instead, plan_hash_value from the explain plan preceding is compared with plan_hash from the output of the apg_plan_mgmt.dba_plans query.

pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

  sql_hash 	|  plan_hash  |  status  | cost       |  sql_text                                                                                                         
                                                                      
------------+-------------+----------+------------+-----------------------------------------------------------
 
1561242727	-1990695905		Approved	23228.14   	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx); 
1561242727	-794604077	 	UnApproved 	 111.17    	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);

The following is an example of plan adaptability with QPM. This example evaluates the unapproved plan based on the minimum speedup factor. It approves any captured unapproved plan that is faster by at least 10 percent than the best approved plan for the statement. For additional details, see Evaluating Plan Performance in the Aurora documentation.

pg105db=> SELECT apg_plan_mgmt.Evolve_plan_baselines (sql_hash, plan_hash, 1.1,'approve')
FROM   apg_plan_mgmt.dba_plans
WHERE  status = 'Unapproved'; 

NOTICE: [Unapproved] SQL Hash: 1561242727, Plan Hash: 1944377599, SELECT sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where ...
NOTICE:      Baseline   [Planning time 0.693 ms, Execution time 316.644 ms]
NOTICE:      Baseline+1 [Planning time 0.695 ms, Execution time 213.919 ms]
NOTICE:      Total time benefit: 102.723 ms, Execution time benefit: 102.725 ms, Avg Log Cardinality Error: 3.53418, Cost = 111.16..111.17
NOTICE:      Unapproved -> Approved

After QPM evaluates the plan based on the speed factor, the plan status changes to approved. At this point, the optimizer can choose that plan for that managed statement now.

pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

  sql_hash  |  plan_hash   |  status   | cost 	     |  sql_text                                                                                                         
                                                                      
------------+-------------+------------+-------------+-----------------------------------------------------------
 
1561242727	 -1990695905	 Approved 	 23228.14     	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx); 
1561242727	 -794604077		 Approved 	 111.17     	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);

View the explain plan output to see whether the query is using the newly approved minimum cost plan.

pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=111.16..111.17 rows=1 width=16)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.46..0.47 rows=1 width=8)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.42..0.46 rows=1 width=8)
                   ->  Index Only Scan using pgbench_hist_mtime on pgbench_history mn  (cost=0.42..14882.41 rows=421449 width=8)
                         Index Cond: (mtime IS NOT NULL)
   InitPlan 4 (returns $3)
     ->  Result  (cost=0.46..0.47 rows=1 width=8)
           InitPlan 3 (returns $2)
             ->  Limit  (cost=0.42..0.46 rows=1 width=8)
                   ->  Index Only Scan Backward using pgbench_hist_mtime on pgbench_history mx  (cost=0.42..14882.41 rows=421449 width=8)
                         Index Cond: (mtime IS NOT NULL)
   ->  Hash Join  (cost=14.60..107.06 rows=632 width=8)
         Hash Cond: (h.bid = b.bid)
         ->  Index Scan using pgbench_hist_mtime on pgbench_history h  (cost=0.42..85.01 rows=2107 width=8)
               Index Cond: ((mtime >= $1) AND (mtime <= $3))
         ->  Hash  (cost=14.14..14.14 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))

   
 SQL Hash: 1561242727, Plan Hash: -794604077

Use case #2. Fixing plans with QPM using pg_hint_plan

In some cases, the query optimizer doesn’t generate the best execution plan for the query. One approach to fixing this problem is to put query hints into your application code, but this approach is widely discouraged because it makes applications more brittle and harder to maintain, and in some cases, you can’t hint the SQL because it is generated by a 3rd party application. What we will show is how to use hints to control the query optimizer, but then to remove the hints and allow QPM to enforce the desired plan, without adding hints to the application code.

For this purpose, PostgreSQL users can use the pg_hint_plan extension to provide directives such as “scan method,” “join method,” “join order,”, or “row number correction,” to the optimizer. The resulting plan will be saved by QPM, along with any GUC parameters you choose to override (such as work_mem). QPM remembers any GUC parameter overrides and uses them when it needs to recreate the plan. To install and learn more about how to use the pg_hint_plan extension, see the pg_hint_plan documentation.

QPM steps to fix the plan generated by using hints

Working with pg_hint_plan is incredibly useful for cases where the query can’t be modified to add hints. In this example, use a sample query to generate the execution plan that you want by adding hints to the managed statement. Then associate this execution plan with the original unmodified statement.

Here are the detailed steps:

Check if the plan capture is disabled.

pg105db=> show apg_plan_mgmt.capture_plan_baselines;
 apg_plan_mgmt.capture_plan_baselines 
--------------------------------------
 off
(1 row)

Run the query with the hint to use. In the following example, use the “HashJoin” hint, which is a directive for the optimizer to choose the join method as HashJoin. The original plan of the query without hints is as follows.

pg105db=> EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;                                                   

                                QUERY PLAN                                                    
----------------------------------------------------------------------
 Nested Loop  (cost=0.42..181906.82 rows=1000000 width=465)
   Join Filter: (b.bid = a.bid)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..44165.43 rows=1000000 width=97)
   ->  Materialize  (cost=0.00..14.15 rows=10 width=364)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 SQL Hash: 356104612, Plan Hash: -451962956

Enable pg_hint_plan and manual plan capture.

Pg105db=> SET pg_hint_plan.enable_hint = true;
SET
pg105db=> SET apg_plan_mgmt.capture_plan_baselines = manual;
SET

EXPLAIN the query with the hints you want to use. In the following example, use the HashJoin (a, b) hint, which is a directive for the optimizer to use a hash join algorithm to join from table a to table b. The plan that you want with a hash join is as follows.

pg105db=> /*+ HashJoin(a b) */  EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;

                            QUERY PLAN                                               
----------------------------------------------------------------------
 Gather Merge  (cost=240409.02..250138.04 rows=833334 width=465)
   Workers Planned: 2
   ->  Sort  (cost=239409.00..240450.67 rows=416667 width=465)
         Sort Key: a.aid
         ->  Hash Join  (cost=14.22..23920.19 rows=416667 width=465)
               Hash Cond: (a.bid = b.bid)
               ->  Parallel Seq Scan on pgbench_accounts a  (cost=0.00..22348.67 rows=416667 width=97)
               ->  Hash  (cost=14.10..14.10 rows=10 width=364)
                     ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 SQL Hash: 356104612, Plan Hash: 1139293728

Verify that plan 1139293728 was captured, and note the status of the plan. View the captured plan and the status of the plan.

pg105db=> 
SELECT sql_hash,
       plan_hash,
       status,
       enabled,
       sql_text
FROM   apg_plan_mgmt.dba_plans
 Where plan_hash=1139293728;

  sql_hash  | plan_hash  |  status  | enabled |         sql_text          
-----------+------------+----------+---------+---------------------------
 356104612 | 1139293728 | Approved | t       | SELECT                   +
           |            |          |         |    *                     +
           |            |          |         | FROM                     +
           |            |          |         |    pgbench_branches b    +
           |            |          |         |    JOIN                  +
           |            |          |         |       pgbench_accounts a +
           |            |          |         |       ON b.bid = a.bid   +
           |            |          |         | ORDER BY                 +
           |            |          |         |    a.aid;

If necessary, Approve the plan. In this case this is the first and only plan saved for statement 356104612, so it was saved as an Approved plan. If this statement already had a baseline of approved plans, then this plan would have been saved as an Unapproved plan. In general, to Reject all existing plans for a statement and then Approve one specific plan, you could call apg_plan_mgmt.set_plan_status twice, like this.
```
pg105db=> SELECT apg_plan_mgmt.set_plan_status (sql_hash, plan_hash, 'Rejected') from apg_plan_mgmt.dba_plans where sql_hash = 356104612;
SET
pg105db=> SELECT apg_plan_mgmt.set_plan_status (356104612, 1139293728, 'Approved');
SET
```
SQL

Remove the hint, turn off manual capture, turn on use_plan_baselines, and also verify that the desired plan is in use without the hint.

pg105db=> SET apg_plan_mgmt.capture_plan_baselines = off;
SET
pg105db=> SET apg_plan_mgmt.use_plan_baselines = true;
SET
pg105db=> EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;             
                             QUERY PLAN                                               
----------------------------------------------------------------------
 Gather Merge  (cost=240409.02..337638.11 rows=833334 width=465)
   Workers Planned: 2
   ->  Sort  (cost=239409.00..240450.67 rows=416667 width=465)
         Sort Key: a.aid
         ->  Hash Join  (cost=14.22..23920.19 rows=416667 width=465)
               Hash Cond: (a.bid = b.bid)
               ->  Parallel Seq Scan on pgbench_accounts a  (cost=0.00..22348.67 rows=416667 width=97)
               ->  Hash  (cost=14.10..14.10 rows=10 width=364)
                     ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 356104612, Plan Hash: 1139293728, Minimum Cost Plan Hash: -451962956

Use case 3: Deploying QPM-managed plans globally using export and import for QPM-managed plans

Large enterprise customers often have applications and databases deployed globally. They also often maintain several environments (Dev, QA, Staging, UAT, Preprod, and Prod) for each application database. However, managing the execution plans manually in each of the databases in specific AWS Regions and each of the database environments can be cumbersome and time-consuming.

QPM provides an option to export and import QPM-managed plans from one database to another database. With this option, you can manage the query execution centrally and deploy databases globally. This feature is useful for the scenarios where you investigate a set of plans on a preprod database, verify that they perform well, and then load them into a production database.

Here are the steps to migrate QPM-managed plans from one database to another. For additional details, see Exporting and Importing Plans in the Aurora documentation.

Export the QPM-managed plan from the source system.
To do this, from the source database with the preferred execution plan, an authorized DB user can copy any subset of the apg_plan_mgmt.plans table to another table. That user can then save it using the pg_dump command. For additional details on the pg_dump, see pg_dump in the PostgreSQL documentation.
Import the QPM-managed plan on the target system.
On the target system, copy any subset of the apg_plan_mgmt.plans table to another table, and then save it using the pg_dump command. This is an optional step to preserve any existing managed plans on the target system before importing new plans for the source system.
We have assumed that you have used pg_dump tar-format archive in step 1. Use the pg_restore command to copy the .tar file into a new table (plan_copy). For additional details about the pg_restore, see pg_restore in the PostgreSQL documentation.
Merge the new table with the apg_plan_mgmt.plans table.
Reload the managed plans into shared memory and remove the temporary plans table.
SELECT apg_plan_mgmt.reload(); — Refresh shared memory with new plans. DROP TABLE plans_copy; -- Drop the temporary plan table.

Summary

In this post, we have covered some of the advanced use cases for Aurora PostgreSQL QPM. One of these is how QPM integrates with pg_hint_plan, which allows the optimizer to generate an optimal execution plan using hints. The optimizer can do this even for SQL statements that can’t be modified (for example, statements that are part of commercial off-the-shelf applications).

Other use cases include working with QPM export and import features, which enable you to manage your execution plans centrally and deploy them globally. Central management and global deployment enable quick deployment of the good known execution plans across multiple databases spread across multiple environments and AWS Regions.

We have covered a few of the important use cases for QPM. In addition, you can implement other use cases using QPM that are equally important to achieving sustainable and optimal database performance. For additional details about QPM, see Managing Query Execution Plans for Aurora PostgreSQL in the Aurora documentation.

About the Authors

Sameer Malik is a principal database solutions architect at Amazon Web Services.

Jim Finnerty is a principal software engineer for Aurora PostgreSQL at Amazon Web Services.

Select your cookie preferences

AWS Database Blog