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
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_stat_monitor" and enable the extension.
Views
_10select * from extensions.pg_stat_monitor;
The following table shows a subset of available columns:
Column Type | Description |
---|---|
bucket bigint | Data collection unit. The number shows what bucket in a chain a record belongs to |
bucket_start_time timestampz | The 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 bool | True if the query was executed as a top-level statement (always true if pg_stat_statements.track is set to top) |
client_ip inet | The IP address of a client that ran the query |
queryid bigint | Hash code to identify identical normalized queries. |
planid text | An internally generated ID of a query plan |
query_plan text | The sequence of steps used to execute a query. This parameter is only available when pgsm_enable_query_plan is enabled |
query text | Text of a representative statement |
plans bigint | Number of times the statement was planned (if pg_stat_statements.track_planning is enabled, otherwise zero) |
total_plan_time double precision | Total time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero) |
min_plan_time double precision | Minimum 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
pg_stat_monitor_reset()
: Resets the statistics tracked by thepg_stat_monitor
view and deletes all previous data.pg_stat_monitor_version()
: Displays the version of thepg_stat_monitor
extension.
Resources
- Official
pg_stat_monitor
documentation