aurora_stat_plans - Amazon Aurora
SyntaxArgumentsReturn typeUsage notesExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

aurora_stat_plans

Returns a row for every tracked execution plan.

Syntax

aurora_stat_plans(
    showtext
)

Arguments

  • showtext – Show the query and plan text. Valid values are NULL, true or false. True will show the query and plan text.

Return type

Returns a row for each tracked plan that contains all the columns from aurora_stat_statements and the following additional columns.

  • planid – plan identifier

  • explain_plan – explain plan text

  • plan_type:

    • no plan - no plan was captured

    • estimate - plan captured with estimated costs

    • actual - plan captured with EXPLAIN ANALYZE

  • plan_captured_time – last time a plan was captured

Usage notes

aurora_compute_plan_id must be enabled and pg_stat_statements must be in shared_preload_libraries for the plans to be tracked.

The number of plans available is controlled by the value set in the pg_stat_statements.max parameter. When compute_plan_id is enabled, you can track the plans up to this specified value in aurora_stat_plans.

This function is available from Aurora PostgreSQL versions 14.10, 15.5, and for all other later versions.

Examples

In the example below, the two plans that are for the query identifier -5471422286312252535 are captured and the statements statistics are tracked by the planid.


db1=# select calls, total_exec_time, planid, plan_captured_time, explain_plan
db1-# from aurora_stat_plans(true)
db1-# where queryid = '-5471422286312252535'   

calls    |  total_exec_time   |   planid    |      plan_captured_time       |                           explain_plan                           
---------+--------------------+-------------+-------------------------------+------------------------------------------------------------------
 1532632 |  3209846.097107853 |  1602979607 | 2023-10-31 03:27:16.925497+00 | Update on pgbench_branches                                      +
         |                    |             |                               |   ->  Bitmap Heap Scan on pgbench_branches                      +
         |                    |             |                               |         Recheck Cond: (bid = 76)                                +
         |                    |             |                               |         ->  Bitmap Index Scan on pgbench_branches_pkey          +
         |                    |             |                               |               Index Cond: (bid = 76)
   61365 | 124078.18012200127 | -2054628807 | 2023-10-31 03:20:09.85429+00  | Update on pgbench_branches                                      +
         |                    |             |                               |   ->  Index Scan using pgbench_branches_pkey on pgbench_branches+
         |                    |             |                               |         Index Cond: (bid = 17)