Monitoring query execution plans for Aurora PostgreSQL
You can monitor query execution plans in your Aurora PostgreSQL DB instance to detect the execution plans
contributing to current database load and to track performance statistics of execution plans over time
using aurora_compute_plan_id parameter. Whenever a query executes, the execution plan used
by the query is assigned an identifier and the same identifier is used by subsequent executions of the same plan.
The aurora_compute_plan_id is turned on by default in DB parameter group from Aurora PostgreSQL versions 14.10, 15.5, and higher versions.
Assignment of a plan identifier is default behavior and can be turned off by setting aurora_compute_plan_id to OFF in the parameter group.
This plan identifier is used in several utilities that serve a different purpose.
Topics
Accessing query execution plans using Aurora functions
With aurora_compute_plan_id, you can access the execution plans using the following functions:
aurora_stat_activity
aurora_stat_plans
For more information on these functions, see Aurora PostgreSQL functions reference.
Parameter reference for Aurora PostgreSQL query execution plans
You can monitor the query execution plans using the below parameters in a DB parameter group.
Parameters
Note
The configuration for aurora_stat_plans.with_* parameters takes effect only for newly captured plans.
aurora_compute_plan_id
Set to off to prevent a plan identifier from being assigned.
| Default | Allowed values | Description |
|---|---|---|
| on | 0(off) | Set to off to prevent a plan identifier from being assigned. |
| 1(on) | Set to on to assign a plan identifier. |
aurora_stat_plans.minutes_until_recapture
The number of minutes to pass before a plan is recaptured. Default is 0 which will disable recapturing
a plan. When the aurora_stat_plans.calls_until_recapture
threshold is passed, the plan will be recaptured.
| Default | Allowed values | Description |
|---|---|---|
| 0 | 0-1073741823 | Set the number of minutes to pass before a plan is recaptured. |
aurora_stat_plans.calls_until_recapture
The number of calls to a plan before it is recaptured. Default is 0 which will disable recapturing
a plan after a number of calls. When the aurora_stat_plans.minutes_until_recapture
threshold is passed, the plan will be recaptured.
| Default | Allowed values | Description |
|---|---|---|
| 0 | 0-1073741823 | Set the number of calls before a plan is recaptured. |
aurora_stat_plans.with_costs
Captures an EXPLAIN plan with estimated costs. The allowed values are on and off. The default is on.
| Default | Allowed values | Description |
|---|---|---|
| on | 0(off) | Doesn't show estimated cost and rows for each plan node. |
| 1(on) | Shows estimated cost and rows for each plan node. |
aurora_stat_plans.with_analyze
Controls the EXPLAIN plan with ANALYZE. This mode is only used the first time a plan is captured. The allowed values are on and off. The default is off.
| Default | Allowed values | Description |
|---|---|---|
| off | 0(off) | Doesn't include actual run time statistics for the plan. |
| 1(on) | Includes actual run time statistics for the plan. |
aurora_stat_plans.with_timing
Plan timing will be captured in the explain when ANALYZE is used. The default is on.
| Default | Allowed values | Description |
|---|---|---|
| on | 0(off) | Doesn't include actual start up time and time spent in each plan node. |
| 1(on) | Includes actual start up time and time spent in each plan node. |
aurora_stat_plans.with_buffers
Plan buffer usage statistics will be captured in the explain when ANALYZE is used. The default is off.
| Default | Allowed values | Description |
|---|---|---|
| off | 0(off) | Doesn't include information on buffer usage. |
| 1(on) | Includes information on buffer usage. |
aurora_stat_plans.with_wal
Plan wal usage statistics will be captured in the explain when ANALYZE is used. The default is off.
| Default | Allowed values | Description |
|---|---|---|
| off | 0(off) | Doesn't include information on WAL record generation. |
| 1(on) | Includes information on WAL record generation. |
aurora_stat_plans.with_triggers
Plan trigger execution statistics will be captured in the explain when ANALYZE is used. The default is off.
| Default | Allowed values | Description |
|---|---|---|
| off | 0(off) | Doesn't include triggers execution statistics. |
| 1(on) | Includes triggers execution statistics. |
