F.41. pgpro_stats

The pgpro_stats extension provides a means for tracking 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.

F.41.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.

F.41.2. Installation and Setup

The pgpro_stats extension is included into Postgres Pro Enterprise, 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 Enterprise 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.41.3. Usage

F.41.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.

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.41.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.41.6.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.41.4. Views

F.41.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.30.

Table F.30. 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
plantext The text of the query plan, in the format defined by the pgpro_stats.plan_format configuration parameter
wait_statsjsonb A jsonb object containing statistics on wait events, for each execution of the query that uses the corresponding plan
querytext Text of a representative statement
callsbigint Number of times executed
total_timedouble precision Total time spent in the statement, in milliseconds
min_timedouble precision Minimum time spent in the statement, in milliseconds
max_timedouble precision Maximum time spent in the statement, in milliseconds
mean_timedouble precision Mean time spent in the statement, in milliseconds
stddev_timedouble precision Population standard deviation of time spent in the statement, in milliseconds
rowsbigint Total number of rows retrieved or affected by the statement
shared_blks_hitbigint Total number of shared block cache hits by the statement
shared_blks_readbigint Total number of shared blocks read by the statement
shared_blks_dirtiedbigint Total number of shared blocks dirtied by the statement
shared_blks_writtenbigint Total number of shared blocks written by the statement
local_blks_hitbigint Total number of local block cache hits by the statement
local_blks_readbigint Total number of local blocks read by the statement
local_blks_dirtiedbigint Total number of local blocks dirtied by the statement
local_blks_writtenbigint Total number of local blocks written by the statement
temp_blks_readbigint Total number of temp blocks read by the statement
temp_blks_writtenbigint Total number of temp blocks written by the statement
blk_read_timedouble precision  Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timedouble precision  Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

F.41.4.2. 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.31. 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.41.5. 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. It is possible for clients to call the pgpro_stats_statements function directly, and by specifying showtext := false have 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_metrics(showtext boolean) returns setof record

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

F.41.6. Configuration Parameters

F.41.6.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.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.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. Changing this parameter requires superuser rights.

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.41.6.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.41.7. Authors

Postgres Professional, Moscow, Russia