F.37. pgpro_stats

The pgpro_stats extension provides a means for tracking planning and execution statistics of all SQL statements executed by a server. It is based on the pg_stat_statements module and provides the following additional functionality:

  • Storing query plans in addition to query statements.

  • Configuring sample rate for statistics collection to reduce overhead.

  • Calculating wait event statistics for executed queries.

  • Calculating resource usage statistics of statement planning and execution.

  • Calculating cache invalidation statistics.

F.37.1. Limitations

  • pgpro_stats can sometimes fail to match identical parameters in the query statement and the corresponding query plan.

  • Some SPI queries are not included into statistics.

  • pgpro_stats may not work correctly with third-party extensions that use parser, planner, or executor hooks to modify parse and plan trees and execution of the queries. In order to dump the final versions of the queries and plans, pgpro_stats should be the last on the list of shared_preload_libraries, but some existing extensions, such as pg_pathman, will not work at all unless they are the last on this list.

  • pgpro_stats may not work correctly with third-party extensions that produce CustomScan and ForeignScan nodes.

F.37.2. Installation and Setup

The pgpro_stats extension is included into Postgres Pro Standard, but has to be installed separately. Once you have pgpro_stats installed, complete the following steps to enable pgpro_stats:

  1. Add pgpro_stats to the shared_preload_libraries parameter in the postgresql.conf file:

    shared_preload_libraries = 'pgpro_stats'
    
  2. Restart the Postgres Pro Standard instance for the changes to take effect.

    Once the server is reloaded, pgpro_stats starts tracking statistics across all databases of the cluster. If required, you can change the scope of statistics collection or disable it altogether using pgpro_stats configuration parameters.

  3. To access the collected statistics, you have to create pgpro_stats extension:

    CREATE EXTENSION pgpro_stats;
    

F.37.3. Usage

F.37.3.1. Collecting Statistics on Query Statements and Plans

Once installed, the pgpro_stats extension starts collecting statistics on the executed statements. The collected data is similar to the one provided by pg_stat_statements, but also includes information on query plans and wait events for each query type. The statistics is saved into an in-memory ring buffer and is accessible through the pgpro_stats_statements view.

By default, pgpro_stats collects statistics on all the executed statements that satisfy the pgpro_stats.track and pgpro_stats.track_utility settings. If performance is a concern, you can set a sample rate for queries using the pgpro_stats.query_sample_rate parameter, and pgpro_stats will randomly select queries for statistics calculation at the specified rate.

To collect statistics on wait events, pgpro_stats uses time-based sampling. Wait events are sampled at the time interval specified by the pgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, the pgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if the pgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting the pgpro_stats.enable_profile parameter to false.

pgpro_stats_statements.plans and pgpro_stats_statements.calls aren't always expected to match because planning and execution statistics are updated at their respective end phase, and only for successful operations. For example, if a statement is successfully planned but fails during the execution phase, only its planning statistics will be updated. If planning is skipped because a cached plan is used, only its execution statistics will be updated.

As an example, let's create a table with some random data and build an index on this table:

CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i);
CREATE INDEX test_x_idx ON test (x);

Now run the following query several times using different values for :x_min and :x_max:

SELECT * FROM test WHERE x >= :x_min AND x <= :x_max;

The collected statistics should appear in the pgpro_stats_statements view:

SELECT queryid, query, planid, plan, wait_stats FROM pgpro_stats_statements WHERE query LIKE 'select * from test where%';
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | 8287793242828473388
plan       | Gather
           |   Output: i, x
           |   Workers Planned: 2
           |   ->  Parallel Seq Scan on public.test
           |         Output: i, x
           |         Filter: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | {"IO": {"DataFileRead": 10}, "IPC": {"BgWorkerShutdown": 10}, "Total": {"IO": 10, "IPC": 10, "Total": 20}}
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -9045072158333552619
plan       | Bitmap Heap Scan on public.test
           |   Output: i, x
           |   Recheck Cond: ((test.x >= $3) AND (test.x <= $4))
           |   ->  Bitmap Index Scan on test_x_idx
           |         Index Cond: ((test.x >= $5) AND (test.x <= $6))
           |
wait_stats | {"IO": {"DataFileRead": 40}, "Total": {"IO": 40, "Total": 40}}
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -1062789671372193287
plan       | Seq Scan on public.test
           |   Output: i, x
           |   Filter: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | NULL
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -1748292253893834280
plan       | Index Scan using test_x_idx on public.test
           |   Output: i, x
           |   Index Cond: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | NULL

F.37.3.2. Monitoring Custom Metrics

With pgpro_stats, you can define custom metrics to be monitored. The collected data will be saved into an in-memory ring buffer and then sent to a monitoring system. Unlike direct polling of a database by a monitoring system that can lose some data if the connection is interrupted, this approach allows to get all the collected data regardless of connection issues, as long as this data is still available in the ring buffer.

To set up a custom metric to collect, do the following:

  1. For each metric, define all configuration parameters listed in Section F.37.7.2. You must specify a unique numeric identifier of each metric in the parameter names.

    For example, to monitor index bloating each 60 seconds, you can define a new metric by setting metrics-related parameters as follows:

    pgpro_stats.metric_1_name = index_bloat
    pgpro_stats.metric_1_query = 'select iname, ibloat, ipages from bloat'
    pgpro_stats.metric_1_db = 'postgres'
    pgpro_stats.metric_1_user = postgres
    pgpro_stats.metric_1_period = '60s'
    

  2. Restart the server.

    pgpro_stats starts collecting statistics on executed statements and saves it into the ring buffer, and the collected data appears in the pgpro_stats_metrics view:

    SELECT * FROM pgpro_stats_metrics;
    

    Once the new metric is added, its parameters can be changed without a server restart by simply reloading the postgresql.conf configuration file.

  3. If required, set up data export to a monitoring system of your choice.

F.37.4. Views

F.37.4.1. The pgpro_stats_statements View

The statistics gathered by the module are made available via a view named pgpro_stats_statements. This view contains one row for each distinct database ID, user ID and query ID (up to the maximum number of distinct statements that the module can track). The columns of the view are shown in Table F.63.

Table F.63. pgpro_stats_statements Columns

NameTypeReferencesDescription
useridoidpg_authid.oidOID of user who executed the statement
dbidoidpg_database.oidOID of database in which the statement was executed
queryidbigint Internal hash code, computed from the statement's parse tree
planidbigint Internal hash code, computed from the statement's plan tree
querytext Text of a representative statement
plantext The text of the query plan, in the format defined by the pgpro_stats.plan_format configuration parameter
plansint8  Number of times the statement was planned (if pgpro_stats.track_planning is enabled, otherwise zero)
total_plan_timefloat8  Total time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero).
min_plan_timefloat8  Minimum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
max_plan_timefloat8  Maximum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
mean_plan_timefloat8  Mean time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
stddev_plan_timefloat8  Population standard deviation of time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
plan_rusagepgpro_stats_rusage  Resource usage statistics of the statement planning.
callsint8 Number of times the statement was executed
total_exec_timefloat8 Total time spent executing the statement, in milliseconds
min_exec_timefloat8 Minimum time spent executing the statement, in milliseconds
max_exec_timefloat8 Maximum time spent executing the statement, in milliseconds
mean_exec_timefloat8 Mean time spent executing the statement, in milliseconds
stddev_exec_timefloat8 Population standard deviation of time spent executing the statement, in milliseconds
exec_rusagepgpro_stats_rusage  Resource usage statistics of the statement execution.
rowsint8 Total number of rows retrieved or affected by the statement
shared_blks_hitint8 Total number of shared block cache hits by the statement
shared_blks_readint8 Total number of shared blocks read by the statement
shared_blks_dirtiedint8 Total number of shared blocks dirtied by the statement
shared_blks_writtenint8 Total number of shared blocks written by the statement
local_blks_hitint8 Total number of local block cache hits by the statement
local_blks_readint8 Total number of local blocks read by the statement
local_blks_dirtiedint8 Total number of local blocks dirtied by the statement
local_blks_writtenint8 Total number of local blocks written by the statement
temp_blks_readint8 Total number of temp blocks read by the statement
temp_blks_writtenint8 Total number of temp blocks written by the statement
blk_read_timefloat8  Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timefloat8  Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
wal_recordsint8 Total number of WAL records generated by the statement
wal_fpiint8 Total number of WAL full page images generated by the statement
wal_bytesnumeric Total amount of WAL bytes generated by the statement
wait_statsjsonb A jsonb object containing statistics on wait events, for each execution of the query that uses the corresponding plan
inval_msgspgpro_stats_inval_msgs  Number of cache invalidation messages by type generated by the statement (if this is supported by the server, otherwise zero)

F.37.4.2. The pgpro_stats_totals View

The aggregate statistics gathered by the module are made available via a view named pgpro_stats_totals. This view contains one row for each distinct object (up to the maximum number of distinct objects that the module can track). The columns of the view are shown in Table F.64.

Table F.64. pgpro_stats_totals Columns

NameTypeDescription
object_typetextType of the object for which aggregated statistics are collected: "cluster", "database", "user", "client_addr", "application", "backend", "session"
object_idbigintID of the object: oid for databases and users, pid for backends, sid for sessions, NULL for others
object_nametextTextual name of the object or NULL
queries_plannedint8Number of queries planned
total_plan_timefloat8Total time spent in the planning of statements, in milliseconds
total_plan_rusagepgpro_stats_rusageAggregate resource usage statistics of the statement planning
queries_executedint8Number of queries executed
total_exec_timefloat8Total time spent in the execution of statements, in milliseconds
total_exec_rusagepgpro_stats_rusageAggregate resource usage statistics of the statement execution
rowsint8Total number of rows retrieved or affected by the statements
shared_blks_hitint8Total number of shared block cache hits by the statements
shared_blks_readint8Total number of shared blocks read by the statements
shared_blks_dirtiedint8Total number of shared blocks dirtied by the statements
shared_blks_writtenint8Total number of shared blocks written by the statements
local_blks_hitint8Total number of local block cache hits by the statements
local_blks_readint8Total number of local blocks read by the statements
local_blks_dirtiedint8Total number of local blocks dirtied by the statements
local_blks_writtenint8Total number of local blocks written by the statements
temp_blks_readint8Total number of temp blocks read by the statements
temp_blks_writtenint8Total number of temp blocks written by the statements
blk_read_timefloat8 Total time the statements spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timefloat8 Total time the statements spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
wal_recordsint8Total number of WAL records generated by the statements
wal_fpiint8Total number of WAL full page images generated by the statements
wal_bytesnumericTotal amount of WAL bytes generated by the statements
wait_statsjsonbA jsonb object containing statistics on wait events for each execution of the queries
inval_msgspgpro_stats_inval_msgs Number of cache invalidation messages by type generated by the statements (if this is supported by the server, otherwise zero)
cache_resetsint4Number of shared cache resets (only for cluster, databases and backends)

F.37.4.3. The pgpro_stats_inval_status View

The pgpro_stats_inval_status view shows one row with the current status of the global cache invalidation queue. The columns of the view are shown in Table F.65.

Table F.65. pgpro_stats_inval_status Columns

NameTypeDescription
num_inval_messagesint8Number of invalidation messages in the queue
num_inval_queue_cleanupsint8Number of invalidation queue cleanups done to prevent its overflow
num_inval_queue_resetsint4Number of cache resets for backends that delay the queue cleanup

In a working system, num_inval_messages usually approximately equals 4000, which means that the queue is pretty full. Growing num_inval_queue_cleanups indicates an active generation of messages. Growth of num_inval_queue_resets is normally zero, and a non-zero growth indicates either a too fast generation of messages or delays in processing messages by backends. You can see reset counters for each backend in the cache_resets column of the pgpro_stats_totals view.

F.37.4.4. The pgpro_stats_metrics View

The metrics gathered by pgpro_stats are displayed in the pgpro_stats_metrics view. The table below describes the columns of the view.

Table F.66. pgpro_stats_metrics Columns

NameTypeDescription
metric_numberint4A unique ID of the collected metric assigned by user. This ID is included into parameter names that define the metric.
metric_nametextThe name of the metric defined by the pgpro_stats.metric_N_name parameter.
db_nametextThe name of the database for which a particular metric was collected.
tstimestamptzThe time when the metric value got calculated.
valuejsonbThe result of the query used for metric measurement. It is serialized in jsonb as an array of objects received via to_jsonb(resulting_row). If an error occurs, a single object is returned that contains code, message, detail, and hint fields.

F.37.5. Data Types

F.37.5.1. The pgpro_stats_rusage Type

pgpro_stats_rusage is a record type that contains resource usage statistics of statement planning/execution. The fields of this type are shown in Table F.67.

Table F.67. pgpro_stats_rusage Fields

NameTypeDescription
readsbigintNumber of bytes read by the filesystem layer
writesbigintNumber of bytes written by the filesystem layer
user_timedouble precisionUser CPU time used
system_timedouble precisionSystem CPU time used
minfltsbigintNumber of page reclaims (soft page faults)
majfltsbigintNumber of page faults (hard page faults)
nswapsbigintNumber of swaps
msgsndsbigintNumber of IPC messages sent
msgrcvsbigintNumber of IPC messages received
nsignalsbigintNumber of signals received
nvcswsbigintNumber of voluntary context switches
nivcswsbigintNumber of involuntary context switches

F.37.5.2. The pgpro_stats_inval_msgs Type

pgpro_stats_inval_msgs is a record type containing counters for cache invalidation messages. The fields of the type are shown in Table F.68.

Table F.68. pgpro_stats_inval_msgs Fields

NameTypeDescription
totalbigintTotal number of invalidation messages
catcachebigintNumber of catcache invalidation messages
catalogbigintNumber of catalog invalidation messages
relcachebigintNumber of selective relcache invalidation messages
relcache_allbigintNumber of whole relcache invalidation messages
smgrbigintNumber of storage manager invalidation messages
relmapbigintNumber of relation map invalidation messages
snapshotbigintNumber of snapshot invalidation messages

F.37.6. Functions

pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint) returns void

pgpro_stats_statements_reset discards statistics gathered so far by pgpro_stats corresponding to the specified userid, dbid, queryid, and planid. If any of the parameters are not specified, the default value 0(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are 0(invalid), it will discard all statistics. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

pgpro_stats_statements(showtext boolean) returns setof record

The pgpro_stats_statements view is defined in terms of a function also named pgpro_stats_statements. Users can also call the pgpro_stats_statements function directly, and by specifying showtext := false make query text be omitted (that is, the OUT argument that corresponds to the view's query column will return nulls). This feature is intended to support external tools that might wish to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Such tools can instead cache the first query text observed for each entry themselves, since that is all pgpro_stats itself does, and then retrieve query texts only as needed. Since the server stores query texts in a file, this approach may reduce physical I/O for repeated examination of the pgpro_stats_statements data.

pgpro_stats_totals_reset(type text, id bigint) returns void

pgpro_stats_totals_reset discards statistics gathered so far by pgpro_stats corresponding to the specified object type and id. If no parameter is specified or the type parameter is set to 0, all statistics will be discarded. If type is set to a valid object type, then if id is specified, then statistics will be discarded only for the specified object, else, statistics will be discarded for all objects of the specified type. Otherwise, no statistics will be discarded. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

pgpro_stats_totals() returns setof record

The pgpro_stats_totals view is defined in terms of a function also named pgpro_stats_totals. Users can also call the pgpro_stats_totals function directly.

pgpro_stats_metrics(showtext boolean) returns setof record

Defines the pgpro_stats_metrics view, which is described in detail in Table F.66.

F.37.7. Configuration Parameters

F.37.7.1. General Settings

pgpro_stats.max (integer)

pgpro_stats.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pgpro_stats_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 5000. This parameter can only be set at server start.

pgpro_stats.max_totals (integer)

pgpro_stats.max_totals is the maximum number of objects tracked by the module (i.e., the maximum number of rows in the pgpro_stats_totals view). If more distinct objects than that are observed, information about least-used objects is discarded. The default value is 1000. This parameter can only be set at server start.

pgpro_stats.track (enum)

pgpro_stats.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable statement statistics collection. The default value is top. Only superusers can change this setting.

pgpro_stats.track_utility (boolean)

pgpro_stats.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.

pgpro_stats.track_planning (boolean)

pgpro_stats.track_planning controls whether planning operations and duration are tracked by the module. Enabling this parameter may incur a noticeable performance penalty, especially when fewer kinds of queries are executed on many concurrent connections. The default value is off. Only superusers can change this setting.

pgpro_stats.track_totals (boolean)

pgpro_stats.track_totals controls whether aggregate statistics for objects (cluster, users, databases etc.) are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_cluster (boolean)

pgpro_stats.track_cluster controls whether aggregate statistics for the cluster are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_databases (boolean)

pgpro_stats.track_databases controls whether aggregate statistics for the databases are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_users (boolean)

pgpro_stats.track_users controls whether aggregate statistics for the users are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_applications (boolean)

pgpro_stats.track_applications controls whether aggregate statistics for the applications (whose names are set by application_name) are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_client_addr (boolean)

pgpro_stats.track_client_addr controls whether aggregate statistics for the client IP addresses are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_backends (boolean)

pgpro_stats.track_backends controls whether aggregate statistics for the backends are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_sessions (boolean)

pgpro_stats.track_sessions controls whether aggregate statistics for the sessions are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.save (boolean)

pgpro_stats.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are neither saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.

pgpro_stats.plan_format (text)

pgpro_stats.plan_format selects the EXPLAIN format for the query plan. Possible values are text, xml, json, and yaml. The default value is text. Changing this parameter requires a server restart.

pgpro_stats.enable_profile (boolean)

pgpro_stats.enable_profile enables sampling of wait events for separate statements. The default value is true. Changing this parameter requires a server restart.

pgpro_stats.query_sample_rate (float)

pgpro_stats.query_sample_rate specifies the fraction of queries that are randomly selected for statistics calculation. Possible values lie between 0.0 (no queries) and 1.0 (all queries). The default value is 1.0. Changing this parameter requires a server restart.

pgpro_stats.profile_period (integer)

pgpro_stats.profile_period specifies the period, in milliseconds, during which to sample wait events. The default value is 10. Only superusers can change this setting.

pgpro_stats.metrics_buffer_size (integer)

pgpro_stats.metrics_buffer_size specifies the size of the ring buffer used for collecting statistical metrics. The default value is 16kB. Changing this parameter requires a server restart.

pgpro_stats.metrics_workers (integer)

pgpro_stats.metrics_workers specifies the number of workers used to collect statistical metrics. If this parameter is set to 2 or higher, one of the workers serves as the master worker distributing queries to other workers. If only one worker is available, it gets reloaded to connect to different databases. Setting this parameter to 0 disables metrics collection. The default value is 2. Changing this parameter requires a server restart.

F.37.7.2. Metrics Settings

The following parameters can be used to define a custom metric to collect. The N placeholder in the parameter name serves as a unique identifier of the metric to which this setting should apply; it must be set to a non-negative integer for each metric.

When you add these parameters for a new metric, you have to restart the server for the changes to take effect. Once the new metric is added, its parameters can be changed without a server restart by simply reloading the postgresql.conf configuration file.

pgpro_stats.metric_N_name (text)

The name of metric N. This name will be displayed in the metric_name column of the pgpro_stats_metrics view.

pgpro_stats.metric_N_query (text)

The query statement that defines the metric to collect.

pgpro_stats.metric_N_period (integer)

The time interval at which to collect metric N, in milliseconds. Default: 60000 ms

pgpro_stats.metric_N_db (text)

The list of databases for which to collect metric N. Database names must be separated by commas. You can specify the * character to select all databases in the cluster except the template databases. If you need to analyze the template databases as well, you have to specify them explicitly.

pgpro_stats.metric_N_user (text)

The name of the user on behalf of which to collect metric N. This user must have access to the database for which the metric is collected.

F.37.8. Authors

Postgres Professional, Moscow, Russia