Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Home

pg_stat_monitor: Query Performance Monitoring

pg_stat_monitor is query performance monitoring tool that provides query execution statistics in a SQL view named pg_stat_monitor. It tracks a superset of statistics available in pg_stat_statements. Some of the most useful features are:

  • Time Interval Grouping: configurable time buckets to track query usage over time
  • Capture Parameters: optionally track parameters passed into queries instead of generic placeholders e.g. $1
  • Query Plan: store query plans used for execution

For more information on query optimization, check out the query performance guide.

Enable the extension

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "pg_stat_monitor" and enable the extension.

Views


_10
select * from extensions.pg_stat_monitor;

The following table shows a subset of available columns:

Column TypeDescription
bucket bigintData collection unit. The number shows what bucket in a chain a record belongs to
bucket_start_time timestampzThe start time of the bucket
userid oid (references pg_authid.oid)OID of user who executed the statement
dbid oid (references pg_database.oid)OID of database in which the statement was executed
toplevel boolTrue if the query was executed as a top-level statement (always true if pg_stat_statements.track is set to top)
client_ip inetThe IP address of a client that ran the query
queryid bigintHash code to identify identical normalized queries.
planid textAn internally generated ID of a query plan
query_plan textThe sequence of steps used to execute a query. This parameter is only available when pgsm_enable_query_plan is enabled
query textText of a representative statement
plans bigintNumber of times the statement was planned (if pg_stat_statements.track_planning is enabled, otherwise zero)
total_plan_time double precisionTotal time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero)
min_plan_time double precisionMinimum time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero)

A full list of statistics is available in the pg_stat_monitor docs.

Functions

Resources