aurora_stat_statements - 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_statements

Displays all pg_stat_statements columns and adds more columns in the end.

Syntax

aurora_stat_statements(showtext boolean)

Arguments

showtext boolean

Return type

SETOF record with all pg_stat_statements columns and the following additional columns. For more information on pg_stat_statements columns, see pg_stat_statements.

You can reset stats for this function using pg_stat_statements_reset().

  • storage_blks_read – Total number of shared blocks read from aurora storage by this statement.

  • orcache_blks_hit – Total number of optimized reads cache hits by this statement.

  • storage_blk_read_time – If track_io_timing is enabled, it tracks the total time the statement spent reading data file blocks from aurora storage, in milliseconds, otherwise the value is zero. For more information, see track_io_timing.

  • local_blk_read_time – If track_io_timing is enabled, it tracks the total time the statement spent reading local data file blocks, in milliseconds, otherwise the value is zero. For more information, see track_io_timing.

  • orcache_blk_read_time – If track_io_timing is enabled, it tracks the total time the statement spent reading data file blocks from optimized reads cache, in milliseconds, otherwise the value is zero. For more information, see track_io_timing.

Usage notes

you must create pg_stat_statements extension first, to receive correct data from this virtual function.

This function is available in the following Aurora PostgreSQL versions:

  • 15.4 and higher 15 versions

  • 14.9 and higher 14 versions

Examples

The following example shows how it carries all the pg_stat_statements columns and append 5 new columns in the end:

=> select * from aurora_stat_statements(true) where queryid=-7342090857217643794;    
-[ RECORD 1 ]---------+--------------------------------------------------
userid                | 10
dbid                  | 16419
toplevel              | t
queryid               | -7342090857217643794
query                 | CREATE TABLE quad_point_tbl AS                   +
                      |     SELECT point(unique1,unique2) AS p FROM tenk1
plans                 | 0
total_plan_time       | 0
min_plan_time         | 0
max_plan_time         | 0
mean_plan_time        | 0
stddev_plan_time      | 0
calls                 | 1
total_exec_time       | 571.844376
min_exec_time         | 571.844376
max_exec_time         | 571.844376
mean_exec_time        | 571.844376
stddev_exec_time      | 0
rows                  | 10000
shared_blks_hit       | 462
shared_blks_read      | 422
shared_blks_dirtied   | 0
shared_blks_written   | 55
local_blks_hit        | 0
local_blks_read       | 0
local_blks_dirtied    | 0
local_blks_written    | 0
temp_blks_read        | 0
temp_blks_written     | 0
blk_read_time         | 170.634621
blk_write_time        | 0
wal_records           | 0
wal_fpi               | 0
wal_bytes             | 0
storage_blks_read     | 47
orcache_blks_hit      | 375
storage_blk_read_time | 124.505772
local_blk_read_time   | 0
orcache_blk_read_time | 44.684038