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
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_monitorview and deletes all previous data.pg_stat_monitor_version(): Displays the version of thepg_stat_monitorextension.
Resources
- Official
pg_stat_monitordocumentation