Capturing Aurora PostgreSQL execution plans
Aurora PostgreSQL query plan management offers two different modes for capturing
query execution plans, automatic or manual. You choose the mode by setting the value of the
apg_plan_mgmt.capture_plans_baselines
to automatic
or to manual
.
You can capture execution plans for specific SQL statements by using manual plan
capture. Alternatively, you can capture all (or the slowest) plans
that are executed two or more times as your application runs by using automatic plan
capture.
When capturing plans, the optimizer sets the status of a managed statement's
first captured plan to approved
. The optimizer sets the status of any
additional plans captured for a managed statement to unapproved
. However,
more than one plan might occasionally be saved with the approved
status.
This can happen when multiple plans are created for a statement in parallel and before
the first plan for the statement is committed.
To control the maximum number of plans that can be captured and stored in the
dba_plans
view, set the apg_plan_mgmt.max_plans
parameter
in your DB instance-level parameter group. A change to the
apg_plan_mgmt.max_plans
parameter requires a DB instance reboot for a
new value to take effect. For more information, see the apg_plan_mgmt.max_plans parameter.
Manually capturing plans for specific SQL statements
If you have a known set of SQL statements to manage, put the statements into a SQL script file and then manually capture plans. The following shows a psql example of how to capture query plans manually for a set of SQL statements.
psql>
SET apg_plan_mgmt.capture_plan_baselines = manual;psql>
\i my-statements.sqlpsql>
SET apg_plan_mgmt.capture_plan_baselines = off;
After capturing a plan for each SQL statement, the optimizer adds a new row to the
apg_plan_mgmt.dba_plans
view.
We recommend that you use either EXPLAIN or EXPLAIN EXECUTE statements in the SQL script file. Make sure that you include enough variations in parameter values to capture all the plans of interest.
If you know of a better plan than the optimizer's minimum cost plan, you
might be able to force the optimizer to use the better plan. To do so, specify one
or more optimizer hints. For more information, see Fixing plans
using pg_hint_plan. To compare
the performance of the unapproved
and approved
plans and
approve, reject, or delete them, see Evaluating plan performance.
Automatically capturing plans
Use automatic plan capture for situations such as the following:
You don't know the specific SQL statements that you want to manage.
You have hundreds or thousands of SQL statements to manage.
Your application uses a client API. For example, JDBC uses unnamed prepared statements or bulk-mode statements that can't be expressed in psql.
To capture plans automatically
Turn on automatic plan capture by setting
apg_plan_mgmt.capture_plan_baselines
toautomatic
in the DB instance-level parameter group. For more information, see Modifying parameters in a DB parameter group in Amazon Aurora.Reboot your DB instance.
As the application runs, the optimizer captures plans for each SQL statement that runs at least twice.
As the application runs with default query plan management parameter settings, the optimizer captures plans for each SQL statement that runs at least twice. Capturing all plans while using the defaults has very little run-time overhead and can be enabled in production.
To turn off automatic plan capture
-
Set the
apg_plan_mgmt.capture_plan_baselines
parameter tooff
from the DB instance-level parameter group.
To measure the performance of the unapproved plans and approve, reject, or delete them, see Evaluating plan performance.