Movatterモバイル変換

[0]ホーム
URL:

Facebook
Postgres Pro
Facebook
Downloads
G.1. pgpro_pwr — workload reports
Prev UpAppendix G. Postgres Pro Modules and Extensions Shipped as Individual PackagesHome Next

G.1. pgpro_pwr — workload reports#

Thepgpro_pwr module is designed to discover most resource-intensive activities in your database. (PWR, pronounced like "power", is an abbreviation of Postgres Pro Workload Reporting.) This extension is based onPostgres Pro'sStatistics Collector views and thepgpro_stats orpg_stat_statements extension.

Note

Althoughpgpro_pwr can work with thepg_stat_statements extension, it is recommended that you use thepgpro_stats extension since it provides statement plans, wait events sampling and load distribution statistics for databases, roles, client hosts and applications.

Below, use ofpgpro_stats is assumed unless otherwise noted.

If you cannot usepgpro_stats for an observed database, but thepg_stat_kcache extension is available,pgpro_pwr can processpg_stat_kcache data, which also provides information about CPU resource usage of statements and filesystem load (rusage).

pgpro_pwr can obtain summary wait statistics from thepg_wait_sampling extension. Whenpg_wait_sampling is in use,pgpro_pwr will reset the wait sampling profile on every sample.

pgpro_pwr is based on cumulative statistics sampling. Each sample contains statistic increments for most active objects and queries since the time when the previous sample was taken, or more concisely,since the previous sample. This data is later used to generate reports.

pgpro_pwr provides functions to collect samples. Regular sampling allows building a report on the database workload in the past.

pgpro_pwr allows you to take explicit samples during batch processing, load testing, etc.

Any time a sample is taken,pgpro_stats_statements_reset() (seepgpro_stats for the function description) is called to ensure that statement statistics will not be lost when the statements count exceedpgpro_stats.max (seeSection G.2.7.1). The report will also contain a section informing you of whether the count of captured statements in any sample reaches 90% ofpgpro_stats.max.

pgpro_pwr installed on onePostgres Pro server can also collect statistics from other servers. This feature is useful for gathering workload statistics from hot standbys on the primary server. To benefit from it, make sure that all server names and connection strings are specified and that thepgpro_pwr server can connect to all databases on all servers.

G.1.1. pgpro_pwr Architecture#

The extension consists of the following parts:

  • Historical repository is a storage for sampling data. The repository is a set of extension tables.

    Note

    Among the rest,pgpro_pwr tables store query texts, which can contain sensitive information. So, for security reasons, restrict access to the repository as appropriate.

  • Sample management engine comprises functions used totake samples and maintain the repository by removing obsolete sample data.

  • Report engine comprises functions forgenerating reports based on data from the historical repository.

  • Administrative functions allow you to create and manageservers andbaselines.

G.1.2. Prerequisites#

The prerequisites assume thatpgpro_pwr, which is usually installed in atarget cluster, i.e., the cluster that you will mainly track the workload for, the extension can also collect performance data from other clusters.

G.1.2.1. For thepgpro_pwr Database#

Thepgpro_pwr extension depends onPL/pgSQL and thedblink extension.

G.1.2.2. For the Target Server#

The target server must allow connections to all databases from the server wherepgpro_pwr is running. To connect to the target server, provide a connection string where a particular database on this server is specified. This database is of high importance forpgpro_pwr since the functionality of thepgpro_stats orpg_stat_statements extensions will be provided through this database. Note, however, thatpgpro_pwr will also connect to all the other databases on this server.

Optionally, for completeness of gathered statistics:

  • If statement statistics are needed in reports,pgpro_stats must be installed and configured in the aforementioned database. The following settings may affect the completeness and accuracy of gathered statistics:

    • pgpro_stats.max

      Low setting of this parameter may cause some statement statistics to be wiped out before the sample is taken. A report will warn you if the value ofpgpro_stats.max seems undersized.

    • pgpro_stats.track

      Avoid changing the default value of 'top' (note that the value of 'all' will affect the accuracy of%Total fields for statements-related sections of a report).

  • Set the parameters of thePostgres Pro'sStatistics Collector as follows:

            track_activities = on        track_counts = on        track_io_timing = on        track_wal_io_timing = on   # Since PostgreSQL 14        track_functions = all/pl

G.1.3. Installation and Setup#

pgpro_pwr is provided withPostgres Pro Standard as a separate pre-built packagepgpro-pwr-std-17 (for the detailed installation instructions, seeChapter 16).

Note

pgpro_pwr creates a bunch of database objects, so installation in a dedicated schema is recommended.

Although the use ofpgpro_pwr with superuser privileges does not have any issues, superuser privileges are not necessary. So you can choose one of the following setup procedures depending on your configuration and security requirements or customize them to meet your needs:

G.1.3.1. Simple Setup#

Use this setup procedure whenpgpro_pwr is to be installed on the target cluster to only track its workload as superuser.

Create a schema for thepgpro_pwr installation and create the extension:

CREATE SCHEMA profile;CREATE EXTENSION pgpro_pwr SCHEMA profile;

G.1.3.2. Complex Setup#

Use this setup procedure when you intend to usepgpro_pwr for tracking workload on one or more servers and need to follow the principle of least privilege.

G.1.3.2.1. In the Target Server Database#

Create a user forpgpro_pwr on the target server:

CREATE USER pwr_collector PASSWORD 'collector_pwd';

Make sure this user has permissions to connect to any database in the target cluster (by default, it is true) and thatpg_hba.conf permits such a connection from thepgpro_pwr database host. Also, grantpwr_collector with membership in thepg_read_all_stats role and theEXECUTE privilege on the following functions:

GRANT pg_read_all_stats TO pwr_collector;GRANT EXECUTE ON FUNCTION pgpro_stats_statements_reset TO pwr_collector;GRANT EXECUTE ON FUNCTION pgpro_stats_totals_reset(text,bigint) TO pwr_collector;

Also ensure theSELECT privilege on thepgpro_stats_archiver view:

GRANT SELECT ON pgpro_stats_archiver TO pwr_collector;
G.1.3.2.2. In thepgpro_pwr Database#

Create an unprivileged user:

CREATE USER pwr_user;

This user will be the owner of the extension schema and will collect samples.

Create a schema for thepgpro_pwr installation:

CREATE SCHEMA profile AUTHORIZATION pwr_user;

Grant theUSAGE privilege on the schema where thedblink extension resides:

GRANT USAGE ON SCHEMA public TO pwr_user;

Create the extension usingpwr_user account:

\c - pwr_userCREATE EXTENSION pgpro_pwr SCHEMA profile;

Define the connection parameters of the target server forpgpro_pwr. For example:

SELECT profile.create_server('target_server_name','host=192.168.1.100 dbname=postgres port=5432');

The connection string provided will be used in thedblink_connect() call while executing thetake_sample() function.

Note

Connection strings are stored in apgpro_pwr table in clear-text form. Make sure no other database users can access tables of thepgpro_pwr extension.

G.1.3.3. Setting Uppgpro_pwr Roles#

Up to three roles can be distinguished whenpgpro_pwr is in operation:

  • Thepgpro_pwr owner role is the owner of thepgpro_pwr extension.

  • Thecollecting role is used bypgpro_pwr to connect to databases and collect statistics.

  • Thereporting role is used to generate reports.

If all the actions withpgpro_pwr are performed by the superuser rolepostgres, you can skip most of the setup explained below.

G.1.3.3.1. Thepgpro_pwr Owner#

This role can be used to perform all actions related topgpro_pwr. This role will have access to server connection strings, which may contain passwords. You should use this role to call thetake_sample() function. Thedblink extension is needed for this user.

Consider an example assuming each extension in its own schema:

\c postgres postgresCREATE SCHEMA dblink;CREATE EXTENSION dblink SCHEMA dblink;CREATE USER pwr_usr with password 'pwr_pwd';GRANT USAGE ON SCHEMA dblink TO pwr_usr;CREATE SCHEMA profile AUTHORIZATION pwr_usr;\c postgres pwr_usrCREATE EXTENSION pgpro_pwr SCHEMA profile;

G.1.3.3.2. The Collecting Role#

This role should be used bypgpro_pwr to connect to databases and collect statistics. Unprivileged users cannot open connections usingdblink without a password, so you need to provide the password in the connection string for each server. This role should have access to all supported statistics extensions. It should also be able to perform a reset of statistics extensions.

Consider an example. If you usepgpro_stats to collect statistics, set up the collecting role as follows:

\c postgres postgresCREATE SCHEMA pgps;CREATE EXTENSION pgpro_stats SCHEMA pgps;CREATE USER pwr_collector with password 'collector_pwd';GRANT pg_read_all_stats TO pwr_collector;GRANT USAGE ON SCHEMA pgps TO pwr_collector;GRANT EXECUTE ON FUNCTION pgps.pgpro_stats_statements_reset TO pwr_collector;

If you usepg_stat_statements to collect statistics, set up the collecting role as follows:

\c postgres postgresCREATE SCHEMA pgss;CREATE SCHEMA pgsk;CREATE SCHEMA pgws;CREATE EXTENSION pg_stat_statements SCHEMA pgss;CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;CREATE EXTENSION pg_wait_sampling SCHEMA pgws;CREATE USER pwr_collector with password 'collector_pwd';GRANT pg_read_all_stats TO pwr_collector;GRANT USAGE ON SCHEMA pgss TO pwr_collector;GRANT USAGE ON SCHEMA pgsk TO pwr_collector;GRANT USAGE ON SCHEMA pgws TO pwr_collector;GRANT EXECUTE ON FUNCTION pgss.pg_stat_statements_reset TO pwr_collector;GRANT EXECUTE ON FUNCTION pgsk.pg_stat_kcache_reset TO pwr_collector;GRANT EXECUTE ON FUNCTION pgws.pg_wait_sampling_reset_profile TO pwr_collector;

Now you should set up a connection string pointing to the database with statistics extensions installed:

 \c postgres pwr_usr SELECT profile.set_server_connstr('local','dbname=postgres port=5432 host=localhost user=pwr_collector password=collector_pwd');

Password authentication must be configured in thepg_hba.conf file for thepwr_collector user.

Obviously, the collecting role should be properly configured on all servers observed bypgpro_pwr.

Now you should be able to calltake_sample() using thepwr_usr role:

\c postgres pwr_usrSELECT * FROM take_sample();

And now it's time to configure the scheduler (in our example, thecrontab command of thepostgres user):

*/30 * * * *   psql -U pwr_usr -d postgres -c 'SELECT profile.take_sample()' > /dev/null 2>&1

Note that you can use thePostgres Pro password file to store passwords.

G.1.3.3.3. The Reporting Role#

Any user can build apgpro_pwr report. The minimal privileges needed to generatepgpro_pwr reports are granted to thepublic role. However a full report, with query texts, is only available to the member of thepg_read_all_stats role. Anyway, the reporting role cannot access server connection strings, so it cannot get the passwords of servers.

G.1.3.4. Setting Extension Parameters#

Inpostgresql.conf, you can define the followingpgpro_pwr parameters:

pgpro_pwr.max (integer)#

Number of top objects (statements, relations, etc.) to be reported in each sorted report table. This parameter affects the size of a sample.

The default value is20.

pgpro_pwr.max_sample_age (integer)#

Retention time of the sample, in days. Samples agedpgpro_pwr.max_sample_age days and older are automatically deleted on the nexttake_sample() call.

The default value is7 days.

pgpro_pwr.max_query_length (integer)#

Maximum query length allowed in reports. All queries in a report will be truncated topgpro_pwr.max_query_length characters.

The default value is20 000 characters.

pgpro_pwr.track_sample_timings (boolean)#

Enables collecting detailed timing statistics ofpgpro_pwr's own sampling procedures. Set this parameter to diagnose why sampling functions run slowly. Collected timing statistics will be available in thev_sample_timings view.

The default value isoff.

pgpro_pwr.statements_reset (boolean)#

Controls thepgpro_stats/pg_stat_statements statistics reset during taking a sample. Allows you not to reset the statistics during taking a sample due to new techniques employed. When disabled,pgpro_pwr will track statement evictions using the value of thecalls field. However this method does not completly prevent statistics loss.pg_stat_statements v1.11 andpgpro_stats v1.8 contain time tracking abilities that can reduce the possible data loss. When this setting is disabled, you can temporarily enable it in a session if you want to sometimes perform a reset ofpgpro_stats/pg_stat_statements.

The default value ison.

pgpro_pwr.relsize_collect_mode (text)#

Defines the mode of collecting relation sizes. Possible values:

  • off — collection of relation sizes is based onpg_class. Although the relation sizes collected this way are rough, their collection consumes almost no resources.

  • on — accurate relation sizes are collected for each sample using thepg_relation_size() function, which requires a lock on the table and is pretty resource intensive.

  • schedule — accurate relation sizes are collected in the size-collection window, defined for each server.

The default value isoff.

G.1.4. Managing Servers#

Once installed,pgpro_pwr creates one enabledlocal server for the current cluster. If a server isenabled,pgpro_pwr includes it in sampling when no server is explicitly specified (seetake_sample() for details). A server that is not enabled is referred to asdisabled.

The default connection string for a local node contains onlydbname andport parameters. The values of these parameters are taken from the connection used to create the extension. You can change the server connection string using theset_server_connstr() function when needed.

G.1.4.1. Server Management Functions#

Use the followingpgpro_pwr functions for server management:

create_server(servername,connstrtext,enabledboolean DEFAULTTRUE,max_sample_ageinteger DEFAULTNULLdescriptiontext DEFAULTNULL)#

Creates a server definition.

Arguments:

  • server — server name. Must be unique.

  • connstr — connection string. Must contain all the necessary settings to connect frompgpro_pwr server to the target server database.

  • enabled — set to include the server in sampling by thetake_sample() function without arguments.

  • max_sample_age — retention time of the sample. Overrides the globalpgpro_pwr.max_sample_age setting for this server.

  • description — server description text, to be included in reports.

Here is an example of how to create a server definition:

SELECT profile.create_server('omega','host=192.168.1.100 dbname=postgres port=5432');

drop_server(servername)#

Drops a server and all its samples.

set_server_description(servernamedescriptiontext)#

Sets a new server description.

set_server_subsampling(servername,subsample_enabledboolean,min_query_durationinterval,min_xact_durationinterval,min_xact_ageinteger,min_idle_xact_durinterval hour to second)#

Defines subsample settings for a server.

Arguments:

  • server — server name.

  • subsample_enabled — defines whether subsampling is enabled for the server, that is, whethertake_subsample() function should actually take a subsample.

  • min_query_duration — the query duration threshold.

  • min_xact_duration — the transaction duration threshold.

  • min_xact_age — the transaction age threshold.

  • min_idle_xact_dur_age — the idle transaction threshold.

enable_server(servername)#

Includes a server in sampling by thetake_sample() function without arguments.

disable_server(servername)#

Excludes a server from sampling by thetake_sample() function without arguments.

rename_server(servername,new_namename)#

Renames a server.

set_server_max_sample_age(servername,max_sample_ageinteger)#

Sets the retention period for a server (in days). To reset the server retention, set the value ofmax_sample_age toNULL.

set_server_db_exclude(servername,exclude_dbname[])#

Excludes a list of databases on a server from sampling. Use whenpgpro_pwr is unable to connect to some databases in a cluster (for example, in Amazon RDS instances).

set_server_connstr(servername,server_connstrtext)#

Sets the connection string for a server.

set_server_setting(servername,settingtext,valuejsonb)#

Fine-tunes settings of the server statistics collection.collect* settings control which statistics should be collected, andvalue for these settings accepts bollean values, with the default equal totrue. Available settings:

  • collect_pg_stat_statement — collect statement statistics usingpg_stat_statements andpg_stat_kcache extensions.

  • collect_pg_wait_sampling — collect wait event statistics using thepg_wait_sampling extension.

  • collect_objects — collect all the schema object statistics, that is, tables, indexes, and functions, frompg_stat_* views.

  • collect_relations — collect statistics on tables and indexes frompg_stat_* views.

  • collect_functions — collect statistics on user functions from thepg_stat_user_functions view.

  • collect_vacuum_stats — collect the extended vacuum statistics.

show_server_settings(servername)#

Returns the statistics collection settings for the specified server.

show_servers()#

Displays the list of configured servers.

G.1.5. Managing Samples#

Asample contains the database workload statistics since the previous sample

G.1.5.1. Sampling Functions#

The followingpgpro_pwr functions relate to sampling:

take_sample()
take_sample(servername [,skip_sizesboolean])#

Takes samples.

If the parameter is omitted, the function takes a sample on each enabled server. Servers are accessed for sampling sequentially, one by one. The function returns a table with the following columns:

  • server — server name.

  • result — result of taking the sample. Can beOK if the sample was taken successfully or contain the error trace text in case of exception.

  • elapsed — time elapsed while the sample was taken.

If called with the parameter, the function takes a sample on the specified server even if this server is disabled. Use when you need different sampling frequencies on specific servers. Returns0 on success.

Arguments:

  • server — server name.

  • skip_sizes — if omitted or set to null, the size-collection policy applies; iffalse, relation sizes are collected; iftrue, the collection of relation sizes is skipped.

take_sample_subset([sets_cntinteger,current_setinteger])#

Takes a sample on each server in a subset of servers. Use to take samples on servers in parallel if you have many enabled servers. AlthoughPL/pgSQL does not support parallel execution, you can call this function in parallel sessions. This function returns the same type astake_sample(). If both parameters are omitted, the function behaves like thetake_sample() function, i.e., it takes a sample on all enabled servers one by one.

Arguments:

  • sets_cnt — number of subsets to divide all enabled servers into.

  • current_set — number of the subset to collect samples for. Takes values from 0 throughsets_cnt - 1. For the specified subset, samples are collected as usual, server by server.

If a reset of statistics since the previous sample was detected,pgpro_pwr treats corresponding absolute values as differentials; however, the accuracy will be affected anyway.

show_samples([servername,] [daysinteger])#

Returns a table with information on server samples (local server is assumed ifserver is omitted) for the lastdays days (all existing samples are assumed if omitted). This table has the following columns:

  • sample — sample identifier.

  • sample_time — time when this sample was taken.

  • dbstats_reset —NULL or the statistics reset timestamp of thepg_stat_database view if the statistics were reset since the previous sample.

  • clustats_reset —NULL or the statistics reset timestamp of thepg_stat_bgwriter view if the statistics were reset since the previous sample.

  • archstats_reset —NULL or the statistics reset timestamp of thepg_stat_archiver view if the statistics were reset since the previous sample.

Sampling functions also maintain the server repository by deleting obsolete samples and baselines according to the retention policy.

G.1.5.2. Taking Samples#

To take samples for all enabled servers, call thetake_sample() function. Usually, one or two samples per hour is sufficient. You can use a cron-like tool to schedule sampling. Here is an example for a 30-minute sampling period:

*/30 * * * *   psql -c 'SELECT profile.take_sample()' &> /dev/null

However, the results of such a call are not checked for errors. In a production environment, function results can be used for monitoring. This function returnsOK for all servers with successfully taken samples and shows error text for failed servers:

SELECT * FROM take_sample();  server   |                                   result                                    |   elapsed-----------+-----------------------------------------------------------------------------+------------- ok_node   | OK                                                                          | 00:00:00.48 fail_node | could not establish connection                                             +| 00:00:00           | SQL statement "SELECT dblink_connect('server_connection',server_connstr)"  +|           | PL/pgSQL function take_sample(integer) line 69 at PERFORM                  +|           | PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+|           | SQL function "take_sample" statement 1                                     +|           | FATAL:  database "postgresno" does not exist                                |(2 rows)

G.1.5.3. Sample Retention Policy#

You can define sample retention at the following levels:

  1. Global

    The value of thepgpro_pwr.max_sample_age parameter in thepostgresql.conf file defines a common retention setting, which is effective if none of other related settings are defined.

  2. Server

    Specifying themax_sample_age parameter while creating a server or calling theset_server_max_sample_age(server,max_sample_age) function for an existing server defines the retention for the server. A server retention setting overridespgpro_pwr.max_sample_age for a specific server.

  3. Baseline

    Abaseline created overrides all the other retention periods for included samples.

G.1.6. Managing the Collection of Relation Sizes#

It may take considerable time to collect sizes of all relations in a database byPostgres Pro relation-size functions. Besides, those functions requireAccessExclusiveLock on a relation. However, it may be sufficient for you to collect relation sizes on a daily basis.pgpro_pwr allows you to skip collecting relation sizes by defining thesize-collection policy for servers. The policy defines:

  • A daily window when the collection of relation sizes is permitted.

  • A minimum gap between two samples with relation sizes collected.

When the size-collection policy is defined, sampling functions collect relation sizes only when the sample is taken in the defined window and the previous sample with sizes is older than the gap. The following function defines this policy:

set_server_size_sampling(servername,window_starttime with time zone DEFAULTNULL,window_durationinterval hour to second DEFAULTNULL,sample_intervalinterval day to minute DEFAULTNULL,collect_modetext DEFAULTNULL)#

Defines the size-collection policy for a server.

Arguments:

  • server — server name.

  • window_start — start time of the size-collection window.

  • window_duration — duration of the size-collection window.

  • sample_interval — minimum time gap between two samples with relation sizes collected.

  • collect_mode — when set tooff, which is the default for new installations, relation sizes are collected frompg_class, when set toon, relation sizes are collected using thepg_relation_size() function, when set toschedule,pgpro_pwr collects the relation size in the size-collection window. This parameter overrides therelsize_collect_mode extension parameter. Upgrading from a previous version sets the value of this parameter toon orschedule, so the previous behavior does not change.

Note

When you build a report between samples either of which lacks relation-size data, relation-growth sections will be based onpg_class.relpages data. However, you can expand the report interval bounds to the nearest samples with relation sizes collected using thewith_growth parameter ofreport generation functions; this makes the growth data more accurate.

Relation sizes are needed to calculate sequentially scanned volume for tables and explicit vacuum load for indexes.

Example:

SELECT set_server_size_sampling('local','23:00+03',interval '2 hour',interval '8 hour', 'schedule');

Theshow_servers_size_sampling function shows size collection policies for all servers:

postgres=# SELECT * FROM show_servers_size_sampling(); server_name | window_start | window_end  | window_duration | sample_interval | limited_collection-------------+--------------+-------------+-----------------+-----------------+-------------------- local       | 23:00:00+03  | 01:00:00+03 | 02:00:00        | 08:00:00        | t

G.1.7. Managing Subsamples#

Some performance-related data available inPostgres Pro is not cumulative. For example, the most often used data about session states is available through thepg_stat_activity view and can only be obtained with frequent samples. However, thetake_sample() function is heavy and can take considerable amount of time. So it is not suitable for collecting session state data.

Thesubsample feature provides a new fasttake_subsample() function. It can be used to collect relatively fast changing data. Every subsample is bound to the next regular sample and is deleted by the retention policy together with it.

The subsample feature can be used to capture the most interesting session states:

  • Long running queries

  • Long transactions

  • Aged transactions, that is, those that hold a snapshot behind a lot of other transactions

  • Transactions being in an idle state for a long time

G.1.7.1. Subsampling Functions#

The followingpgpro_pwr functions relate to subsampling:

take_subsample()
take_subsample(servername)#

If the parameter is omitted, the function takes a subsample on eachenabled server with subsampling enabled (seeset_server_subsampling for details). Server subsamples are taken sequentially, one by one. The function returns a table with the following columns:

  • server — server name.

  • result — result of taking the subsample. Can beOK if the subsample was taken successfully or contain the error text in case of exception.

  • elapsed — time elapsed while the subsample was taken.

This tabular return format makes it easy to control subsample creation using an SQL query.

If called with the parameter, the function takes a subsample for the specified server. Use when you need different subsampling frequencies on servers or if you want to take an explicit subsample on a specific server.

Arguments:

  • server — server name.

Note

Trying to take a subsample during taking a sample fails.

take_subsample_subset([sets_cntinteger], [current_setinteger])#

Takes subsamples for a subset of enabled servers with subsampling enabled. Although subsamples should be fast enough for serial processing, subsamples can be taken in parallel, like regular samples. This function returns the same type astake_subsample(). If both parameters are omitted, the function behaves like thetake_subsample() function.

Arguments:

  • sets_cnt — number of server subsets.

  • current_set — the subset to process. Takes values from 0 throughsets_cnt - 1. For the specified subset, subsamples are collected as usual, server by server.

G.1.7.2. Configuring the Subsample Feature#

The following settings affect the subsample behaviour:

  • pgpro_pwr.subsample_enabled — defines whether thetake_subsample() function should actually take a subsample.

  • pgpro_pwr.min_query_duration — the long running query threshold.

  • pgpro_pwr.min_xact_duration — the long transaction threshold.

  • pgpro_pwr.min_xact_age — the transaction age threshold.

  • pgpro_pwr.min_idle_xact_dur_age — the idle transaction threshold.

The subsample behavior can be defined at the server level using theset_server_subsampling function.

The last observed session state is saved in the repository when either of the following threshold-related events happens:

  • During query execution, the difference betweennow() and thequery_start exceeds thepgpro_pwr.min_query_duration threshold.

  • During a transaction, the difference betweennow() and thexact_start exceeds thepgpro_pwr.min_xact_duration threshold.

  • During a transaction, theage(backend_xmin) exceeds thepgpro_pwr.min_xact_age threshold.

  • During a transaction in a stateidle in transaction oridle in transaction (aborted), the difference betweennow() and thestate_change exceeds thepgpro_pwr.min_idle_xact_duration threshold.

SeeChapter 26 for details of the mentioned fields. Every subsample can hold at mostpg_profile.topn entries for every threshold type.

G.1.7.3. Scheduling Subsamples#

Subsamples are fast enough to take them quite often. However usually you do not need more than 2-4 subsamples per minute. Obviously the subsample frequency depends on the shortest used duration of a threshold setting.

Cron only allows one call per minute, so some effort is needed to schedule more frequent subsamples. For example, the\watchpsql command can be used:

echo "select take_subsample(); \watch 15" | psql &> /dev/null

Thepsql call can be wrapped in thesystemd service like this:

Description=pg_profile subsampling unit[Unit][Service]Type=simpleExecStart=/bin/sh -c 'echo "select take_subsample(); \\watch 15" | /path/to/psql -qo /dev/null'User=postgresGroup=postgres[Install]WantedBy=multi-user.target

G.1.8. Managing Baselines#

Abaseline is a named sequence of samples that has its own retention setting. A baseline can be used as a sample interval in report generation functions. An undefined baseline retention means infinite retention. Use baselines to save information about the database workload for a certain time interval.

G.1.8.1. Baseline Management Functions#

Use the followingpgpro_pwr functions for baseline management:

create_baseline([servername,]baselinevarchar(25),start_idinteger,end_idinteger [,daysinteger])
create_baseline([servername,]baselinevarchar(25),time_rangetstzrange [,daysinteger])#

Creates a baseline.

Arguments:

  • server — server name.local sever is assumed if omitted.

  • baseline — baseline name. Must be unique for a server.

  • start_id — identifier of the first sample in the baseline.

  • end_id — identifier of the last sample in the baseline.

  • time_range — time interval for the baseline. The baseline will include all samples for the minimal interval that coverstime_range.

  • days — baseline retention time, defined in integer days sincenow(). Omit or set to null for infinite retention.

drop_baseline([servername,]baselinevarchar(25))

Drops a baseline. For the meaning and usage details of function arguments, seecreate_baseline. Dropping a baseline does not mean dropping all its samples immediately. The baseline retention just no longer applies to them.

keep_baseline([servername,]baselinevarchar(25) [,daysinteger])

Changes the retention of a baseline. For the meaning and usage details of function arguments, seecreate_baseline. Omit thebaseline parameter or pass null to it to change the retention of all existing baselines.

show_baselines([servername])

Displays existing baselines. Callshow_baselines to get information about the baselines, such as names, sampling intervals and retention periods.local sever is assumed if theserver parameter is omitted.

G.1.9. Data Export and Import#

Collected samples can be exported from one instance of thepgpro_pwr extension and then loaded into another one. This feature helps you to move server data from one instance to another or to send collected data to your support team.

G.1.9.1. Data Export#

Theexport_data function exports data to a regular table. You can use any method available to export this table from your database. For example, you can use the\copy meta-command ofpsql to obtain a singlecsv file:

postgres=# \copy (select * from export_data()) to 'export.csv'

G.1.9.2. Data Import#

Since data can only be imported from a local table, first, load the data you exported. Using the\copy meta-command again:

postgres=# CREATE TABLE import (section_id bigint, row_data json);CREATE TABLEpostgres=# \copy import from 'export.csv'COPY 6437

Now you can import the data by providing theimport table to theimport_data function:

postgres=# SELECT * FROM import_data('import');

After successful import, you can drop theimport table.

Note

If server data is imported for the first time, your localpgpro_pwr servers with matching names will cause a conflict during import. To avoid this, you can temporarily rename such servers or you can specify the server name prefix for import operations. However, during import of new data for already imported servers, they are matched by system identifiers, so feel free to rename imported severs. Also keep in mind thatpgpro_pwr sets servers being imported to thedisabled state fortake_sample() to bypass them.

G.1.9.3. Export and Import Functions#

Use these functions to export or import data:

export_data([servername, [min_sample_idinteger,] [max_sample_idinteger,]] [,obfuscate_queriesboolean])#

Exports collected data.

Arguments:

  • server — server name. All configured servers are assumed if omitted.

  • min_sample_id,max_sample_id — sample identifiers to bound the export (inclusive). Ifmin_sample_id is omitted or set to null, all samples untilmax_sample_id sample are exported; ifmax_sample_id is omitted or set to null, all samples sincemin_sample_id sample are exported.

  • obfuscate_queries — iftrue, query texts are exported asMD5 hash.

import_data(dataregclass [,server_name_prefixtext])#

Imports previously exported data. Returns the number of actually loaded rows inpgpro_pwr tables.

Arguments:

  • data is the name of the table containing import data.

  • server_name_prefix specifies the server name prefix for the import operation. It can be used to avoid name conflicts.

G.1.10. Report Generation Functions#

pgpro_pwr reports are generated in HTML format by reporting functions. The following types of reports are available:

  • Regular reports provide statistics on the workload for an interval.

  • Differential reports provide statistics on the same objects for two intervals. Corresponding values are located next to each other, which makes it easy to compare the workloads.

Reporting functions take sample identifiers, baselines or time ranges to determine the intervals. For time ranges, these are the minimal intervals that cover the ranges.

G.1.10.1. Regular Reports#

Use these functions to generate regular reports:

get_report([servername,]start_idinteger,end_idinteger [,descriptiontext [,with_growthboolean [,db_excludename[]]]])
get_report([servername,]time_rangetstzrange [,descriptiontext [,with_growthboolean [,db_excludename[]]]])
get_report([servername,]baselinevarchar(25) [,descriptiontext [,with_growthboolean [,db_excludename[]]]])

Generates a regular report defined by the arguments.

Arguments:

  • server — server name.local sever is assumed if omitted.

  • start_id — identifier of the interval starting sample.

  • end_id — identifier of the interval ending sample.

  • baseline — baseline name.

  • time_range — time range.

  • description — short text to be included in the report as its description.

  • with_growth — flag requesting interval expansion to the nearest bounds with data on relation growth available. The default value isfalse.

  • db_exclude — the database exclusion list. Lists databases to be excluded from all report tables having theDatabase column. Use to hide some databases in the report.

get_report_latest([servername,])
get_report_latest([servername [,db_excludename[]]])

Generates a regular report for two latest samples.

Arguments:

  • server — server name.local sever is assumed if omitted.

  • db_exclude — the database exclusion list. Lists databases to be excluded from all report tables having theDatabase column. Use to hide some databases in the report.

G.1.10.2. Differential Reports#

Use this function to generate differential reports:

get_diffreport([servername,]start1_idinteger,end1_idinteger,start2_idinteger,end2_idinteger [,descriptiontext [,with_growthboolean [,db_excludename[]]]])
get_diffreport([servername,]time_range1tstzrange,time_range2tstzrange [,descriptiontext [,with_growthboolean [,db_excludename[]]]])
get_diffreport([servername,]baseline1varchar(25),baseline2varchar(25) [,descriptiontext [,with_growthboolean [,db_excludename[]]]])
get_diffreport([servername,]baseline1varchar(25),time_range2tstzrange [,descriptiontext [,with_growthboolean[,db_excludename[]]]])
get_diffreport([servername,]time_range1tstzrange,baseline2varchar(25) [,descriptiontext [,with_growthboolean[,db_excludename[]]]])
get_diffreport([servername,]start1_idinteger,end1_idinteger,baseline2varchar(25) [,descriptiontext [,with_growthboolean[,db_excludename[]]]])
get_diffreport([servername,]baseline1varchar(25),start2_idinteger,end2_idinteger [,descriptiontext [,with_growthboolean[,db_excludename[]]]])

Generates a differential report for two intervals. The combinations of arguments provide possible ways to specify the two intervals.

Arguments:

  • server — server name.local sever is assumed if omitted.

  • start1_id,end1_id — identifiers of the starting and ending samples for the first interval.

  • start2_id,end2_id — identifiers of the starting and ending samples for the second interval.

  • baseline1 — baseline name for the first interval.

  • baseline2 — baseline name for the second interval.

  • time_range1 — time range for the first interval.

  • time_range2 — time range for the second interval.

  • description — short text to be included in the report as its description.

  • with_growth — flag requesting interval expansion to the nearest bounds with data on relation growth available. The default value isfalse.

  • db_exclude — the database exclusion list. Lists databases to be excluded from all report tables having theDatabase column. Use to hide some databases in the report.

G.1.10.3. Report Generation Example#

Generate a report for thelocal server and interval defined by samples:

psql -Aqtc "SELECT profile.get_report(480,482)" -o report_480_482.html

For any other server, provide its name:

psql -Aqtc "SELECT profile.get_report('omega',12,14)" -o report_omega_12_14.html

Generate a report using time ranges:

psql -Aqtc "SELECT profile.get_report(tstzrange('2020-05-13 11:51:35+03','2020-05-13 11:52:18+03'))" -o report_range.html

Generate a relative time-range report:

psql -Aqtc "SELECT profile.get_report(tstzrange(now() - interval '1 day',now()))" -o report_last_day.html

G.1.11. pgpro_pwr Report Sections#

Eachpgpro_pwr report is divided into sections, described below. The number of top objects reported in each sorted report table is specified by thepgpro_pwr.max parameter.

Almost every item in the report can be accentuated by a single mouse click. The accentuated item will be instantly highlighted in all report sections, making it easy to find. The attributes identifying the item will appear in the bottom-right corner of the page. For example, if you click on a database name in the“Database statistics” report table, you can notice a small table with the database attributes in the bottom-right corner of the page.

When you scroll down the report, its table of contents will be available on the right side of the page. It can be hidden with a single mouse click on the“content” tag.

A substring-based filter is also available that helps limit the report contents to particular objects based on a substring. Specifically, substring-based filtering is applied to query texts.

G.1.11.1. Server statistics#

Tables in this section of apgpro_pwr report are described below.

The report table“Database statistics” provides per-database statistics for the report interval. The statistics are based on thepg_stat_database view.Table G.1 lists columns of this report table.

Table G.1. Database statistics

ColumnDescriptionField/Calculation
Database Database namedatname
Commits Number of committed transactionsxact_commit
Rollbacks Number of rolled back transactionsxact_rollback
Deadlocks Number of deadlocks detecteddeadlocks
Checksum Failures Number of data page checksum failures detected in this database. This field is only shown if any checksum failures were detected in this database during the report interval.checksum_failures
Checksums Last Time at which the last data page checksum failure was detected in this database. This field is only shown if any checksum failures were detected in this database during the report interval.checksum_last_failure
Hit% Buffer cache hit ratio, i.e., percentage of pages fetched from buffers in all pages fetched 
Read Number of disk blocks read in this databaseblks_read
Hit Number of times disk blocks were found already in the buffer cacheblks_hit
Ret Number of returned tuplestup_returned
Fet Number of fetched tuplestup_fetched
Ins Number of inserted tuplestup_inserted
Upd Number of updated tuplestup_updated
Del Number of deleted tuplestup_deleted
Temp Size Total amount of data written to temporary files by queries in this databasetemp_bytes
Temp Files Number of temporary files created by queries in this databasetemp_files
Size Database size at the time of the last sample in the report intervalpg_database_size()
GrowthDatabase growth during the report intervalpg_database_size() increment between interval bounds

The report table“Cluster I/O statistics” provides I/O statistics by object types, backend types and contexts. This table is based on thepg_stat_io view of theCumulative Statistics System, available sincePostgres Pro 16.Table G.2 lists columns of this report table. Times are provided in seconds.

Table G.2. Cluster I/O statistics

ColumnDescription
Object Target object of an I/O operation
Backend Type of the backend that performed an I/O operation
Context The context of an I/O operation
Reads Count Number of read operations
Reads Bytes Amount of data read
Reads Time Time spent in read operations
Writes Count Number of write operations
Writes Bytes Amount of data written
Writes Time Time spent in write operations
Writebacks Count Number of blocks which the process requested the kernel write out to permanent storage
Writebacks Bytes Amount of data requested for write out to permanent storage
Writebacks Time Time spent in writeback operations, including the time spent queueing write-out requests and, potentially, the time spent to write out the dirty data
Extends Count Number of relation extend operations
Extends Bytes Amount of space used by extend operations
Extends Time Time spent in extend operations
Hits The number of times a desired block was found in a shared buffer
Evictions Number of times a block has been written out from a shared or local buffer in order to make it available for another use
Reuses The number of times an existing buffer in a size-limited ring buffer outside of shared buffers was reused as part of an I/O operation in thebulkread,bulkwrite, orvacuumcontexts
Fsyncs Count Number of fsync calls. These are only tracked incontextnormal.
Fsyncs Time Time spent in fsync operations

The report table“Cluster SLRU statistics” provides access statistics on SLRU (simple least-recently-used) caches. This table is based on thepg_stat_slru view of theCumulative Statistics System.Table G.3 lists columns of this report table. Times are provided in seconds.

Table G.3. Cluster SLRU statistics

ColumnDescriptionField/Calculation
Name Name of the SLRUname
Zeroed Number of blocks zeroed during initializationsblks_zeroed
Hits Number of times disk blocks were found already in the SLRU, so that a read was not necessary (this only includes hits in the SLRU, not the operating system's file system cache)blks_hit
Reads Number of disk blocks read for this SLRUblks_read
%Hit Number of disk block hits for this SLRU as the percentage ofReads +Hitsblks_hit*100/blks_read +blks_hit
Writes Number of disk blocks written for this SLRUblks_written
Checked Number of blocks checked for existence for this SLRUblks_exists
Flushes Number of flushes of dirty data for this SLRUflushes
Truncates Number of truncates for this SLRUtruncates

Table“Session statistics by database” is available in the report forPostgres Pro databases starting with version 14. This table is based on thepg_stat_database view of theStatistics Collector.Table G.4 lists columns of this report table. Times are provided in seconds.

Table G.4. Session statistics by database

ColumnDescriptionField/Calculation
Database Database name 
Timing Total Time spent by database sessions in this database during the report interval (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won't be included)session_time
Timing Active Time spent executing SQL statements in this database during the report interval (this corresponds to the statesactive andfastpath function call inpg_stat_activity)active_time
Timing Idle(T) Time spent idling while in a transaction in this database during the report interval (this corresponds to the statesidle in transaction andidle in transaction (aborted) inpg_stat_activity)idle_in_transaction_time
Sessions Established Total number of sessions established to this database during the report intervalsessions
Sessions Abandoned Number of database sessions to this database that were terminated because connection to the client was lost during the report intervalsessions_abandoned
Sessions Fatal Number of database sessions to this database that were terminated by fatal errors during the report intervalsessions_fatal
Sessions Killed Number of database sessions to this database that were terminated by operator intervention during the report intervalsessions_killed

InPostgres Pro Standard databases of versions that includepgpro_stats version starting with 1.4, workload statistics of vacuum processes are available. The“Database vacuum statistics” report table provides per-database aggregated total vacuum statistics based on thepgpro_stats_vacuum_tables view.Table G.5 lists columns of this report table. Times are provided in seconds.

Table G.5. Database vacuum statistics

ColumnDescriptionField/Calculation
Database Database name 
Blocks fetched Total number of database blocks fetched by vacuum operationstotal_blks_read +total_blks_hit
%Total Total number of database blocks fetched (read+hit) by vacuum operations as the percentage of all blocks fetched in the clusterBlocks fetched * 100 /Cluster fetched
Blocks read Total number of database blocks read by vacuum operationstotal_blks_read
%Total Total number of database blocks read by vacuum operations as the percentage of all blocks read in the clusterBlocks read * 100 /Cluster read
VM Frozen Total number of blocks marked all-frozen in the visibility mappages_frozen
VM Visible Total number of blocks marked all-visible in the visibility mappages_all_visible
Tuples deleted Total number of dead tuples vacuum operations deleted from tables of this databasetuples_deleted
Tuples left Total number of dead tuples vacuum operations left in tables of this database due to their visibility in transactionsdead_tuples
%Eff Vacuum efficiency in terms of deleted tuples. This is the percentage of tuples deleted from tables of this database in all dead tuples to be deleted from tables of this database.tuples_deleted * 100 / (tuples_deleted +dead_tuples)
WAL size Total amount of WAL bytes generated by vacuum operations performed on tables of this databasewal_bytes
Read I/O time Time spent reading database blocks by vacuum operations performed on tables of this databaseblk_read_time
Write I/O time Time spent writing database blocks by vacuum operations performed on tables of this databaseblk_write_time
%Total Vacuum I/O time spent as the percentage of whole cluster I/O time
Total vacuum time Total time of vacuuming tables of this databasetotal_time
Delay vacuum time Time spent sleeping in a vacuum delay point by vacuum operations performed on tables of this databasedelay_time
User CPU time User CPU time of vacuuming tables of this databaseuser_time
System CPU time System CPU time of vacuuming tables of this databasesystem_time
Interrupts Number of times vacuum operations performed on tables of this database were interrupted on any errorsinterrupts

If thepgpro_stats extension supporting invalidation statistics was available during the report interval, the "Invalidation messages by database" report table provides per-database aggregated total invalidation message statistics.Table G.6 lists columns of this report table.

Table G.6. Invalidation messages by database

ColumnDescriptionField/Calculation
Database Database name 
Invalidation messages sent Total number of invalidation messages sent by backends in this database. Statistics are provided for corresponding message types ofpgpro_stats_inval_msgsFields ofpgpro_stats_totals.inval_msgs
Cache resets Total number of shared cache resetspgpro_stats_totals.cache_resets

If thepgpro_stats extension was available during the report interval, the“Statement statistics by database” report table provides per-database aggregated total statistics for thepgpro_stats_statements view data.Table G.7 lists columns of this report table. Times are provided in seconds.

Table G.7. Statement statistics by database

ColumnDescriptionField/Calculation
Database Database name 
Calls Number of times all statements in the database were executedcalls
Plan Time Time spent planning all statements in the database Sum oftotal_plan_time
Exec Time Time spent executing all statements in the database Sum oftotal_exec_time
Read Time Time spent reading blocks by all statements in the database Sum ofblk_read_time
Write Time Time spent writing blocks by all statements in the database Sum ofblk_write_time
Trg Time Time spent executing trigger functions by all statements in the database 
Shared Fetched Total number of shared blocks fetched by all statements in the database Sum of (shared_blks_read +shared_blks_hit)
Local Fetched Total number of local blocks fetched by all statements in the database Sum of (local_blks_read +local_blks_hit)
Shared Dirtied Total number of shared blocks dirtied by all statements in the database Sum ofshared_blks_dirtied
Local Dirtied Total number of local blocks dirtied by all statements in the database Sum oflocal_blks_dirtied
Read Temp Total number of temp blocks read by all statements in the database Sum oftemp_blks_read
Write Temp Total number of temp blocks written by all statements in the database Sum oftemp_blks_written
Read Local Total number of local blocks read by all statements in the database Sum oflocal_blks_read
Write Local Total number of local blocks written by all statements in the database Sum oflocal_blks_written
Statements Total number of captured statements 
WAL Size Total amount of WAL generated by all statements in the database Sum ofwal_bytes

The“Statement average min/max timings” report table contains per-database aggregated min/max timing statistics from the one ofpgpro_stats orpg_stat_statements extensions that was available during the report interval, with the precedence ofpgpro_stats. This report is sensitive to the fastest and slowest planning and execution of every statement in a cluster. That is, you can see execution and planning stability in your database.Table G.8 lists columns of this report table.

Table G.8. Statement average min/max timings

ColumnDescription
Database Database name
Min average planning time (ms) The average value ofmin_plan_time for all statements and all samples included in the report
Max average planning time (ms) The average value ofmax_plan_time for all statements and all samples included in the report
Delta% of average planning times Difference between the meanmax_plan_time and meanmin_plan_time as the percentage of the meanmin_plan_time. The less this difference, the more stable query planning in your database is.
Min average execution time (ms) The average value ofmin_exec_time for all statements and all samples included in the report
Max average execution time (ms) The average value ofmax_exec_time for all statements and all samples included in the report
Delta% of average execution times Difference between the meanmax_exec_time and meanmin_exec_time as the percentage of the meanmin_exec_time. The less this difference, the more stable query execution in your database is.
Statements Total count of captured statements

If the JIT-related statistics was avaliable in the statement statistics extension during the report interval, the“JIT statistics by database” report table provides per-database aggregated total statistics of JIT executions.Table G.9 lists columns of this report table. Times are provided in seconds.

Table G.9. JIT statistics by database

ColumnDescriptionField/Calculation
Database Database name 
Calls Number of times all statements in the database were executedcalls
Plan Time Time spent planning all statements in the database Sum oftotal_plan_time
Exec Time Time spent executing all statements in the database Sum oftotal_exec_time
Generation count Total number of functions JIT-compiled by the statements Sum ofjit_functions
Generation time Total time spent by the statements on generating JIT code Sum ofjit_generation_time
Inlining count Number of times functions have been inlined Sum ofjit_inlining_count
Inlining time Total time spent by statements on inlining functions Sum ofjit_inlining_time
Optimization count Number of times statements have been optimized Sum ofjit_optimization_count
Optimization time Total time spent by statements on optimizing Sum ofjit_optimization_time
Emission count Number of times code has been emitted Sum ofjit_emission_count
Emission time Total time spent by statements on emitting code Sum ofjit_emission_time
Deform count Number of tuple deform functions JIT-compiled by the statement of the database
Deform time Total time spent by the statements of the database on JIT-compiling the tuple deform functions

The report table“Cluster statistics” provides data from thepg_stat_bgwriter view.Table G.10 lists rows of this report table. Times are provided in seconds.

Table G.10. Cluster statistics

RowDescriptionField/Calculation
Scheduled checkpoints Number of scheduled checkpoints that have been performedcheckpoints_timed
Requested checkpoints Number of requested checkpoints that have been performedcheckpoints_req
Checkpoint write time (s) Total amount of time that has been spent in the portion of checkpoint processing where files are written to diskcheckpoint_write_time
Checkpoint sync time (s) Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to diskcheckpoint_sync_time
Checkpoint buffers written Number of buffers written during checkpointsbuffers_checkpoint
Background buffers written Number of buffers written by the background writerbuffers_clean
Backend buffers written Number of buffers written directly by a backend. Will not be shown sincePostgres Pro 17.buffers_backend
Backend fsync count Number of times a backend had to execute its ownfsync call (normally the background writer handles those even when the backend does its own write). Will not be shown sincePostgres Pro 17.buffers_backend_fsync
Bgwriter interrupts (too many buffers) Number of times the background writer stopped a cleaning scan because it had written too many buffersmaxwritten_clean
Number of buffers allocated Total number of buffers allocatedbuffers_alloc
WAL generated Total amount of WAL generatedpg_current_wal_lsn() value increment
Start LSN Log sequence number at the start of a report intervalpg_current_wal_lsn() at the first sample of a report
End LSN Log sequence number at the end of a report intervalpg_current_wal_lsn() at the last sample of a report
WAL generated by vacuum Total amount of WAL generated by vacuum Based on thewal_bytes field of thepgpro_stats_vacuum_databases view.
WAL segments archived Total number of archived WAL segments Based onpg_stat_archiver.archived_count
WAL segments archive failed Total number of WAL segment archiver failures Based onpg_stat_archiver.failed_count.
Archiver performance Average archiver process performance per second Based on theactive_time field of thepgpro_stats_archiver view.
Archive command performance Averagearchive_command performance per second Based on thearchive_command_time field of thepgpro_stats_archiver view.

Table“WAL statistics” is available in the report forPostgres Pro databases starting with version 14. This table is based on thepg_stat_wal view of theStatistics Collector.Table G.11 lists columns of this report table. Times are provided in seconds.

Table G.11. WAL statistics

RowDescriptionField/Calculation
WAL generated Total amount of WAL generated during the report intervalwal_bytes
WAL per second Average amount of WAL generated per second during the report intervalwal_bytes /report_duration
WAL records Total number of WAL records generated during the report intervalwal_records
WAL FPI Total number of WAL full page images generated during the report intervalwal_fpi
WAL buffers full Number of times WAL data was written to disk because WAL buffers became full during the report intervalwal_buffers_full
WAL writes Number of times WAL buffers were written out to disk viaXLogWrite request during the report intervalwal_write
WAL writes per second Average number of times WAL buffers were written out to disk viaXLogWrite request per second during the report intervalwal_write /report_duration
WAL sync Number of times WAL files were synced to disk viaissue_xlog_fsync request during the report interval (iffsync is on andwal_sync_method is eitherfdatasync,fsync orfsync_writethrough, otherwise zero). SeeSection 27.5 for more information about the internal WAL functionissue_xlog_fsync.wal_sync
WAL syncs per second Average number of times WAL files were synced to disk viaissue_xlog_fsync request per second during the report intervalwal_sync /report_duration
WAL write time Total amount of time spent writing WAL buffers to disk viaXLogWrite request during the report interval (iftrack_wal_io_timing is enabled, otherwise zero; for more details, seeSection 18.9). This includes the sync time whenwal_sync_method is eitheropen_datasync oropen_sync.wal_write_time
WAL write dutyWAL write time as the percentage of the report interval durationwal_write_time * 100 /report_duration
WAL sync time Total amount of time spent syncing WAL files to disk viaissue_xlog_fsync request during the report interval (iftrack_wal_io_timing is enabled,fsync is on, andwal_sync_method is eitherfdatasync,fsync orfsync_writethrough, otherwise zero).wal_sync_time
WAL sync dutyWAL sync time as the percentage of the report interval durationwal_sync_time * 100 /report_duration

The report table“Tablespace statistics” provides information on the sizes and growth of tablespaces.Table G.12 lists columns of this report table.

Table G.12. Tablespace statistics

ColumnDescriptionField/Calculation
Tablespace Tablespace namepg_tablespace.spcname
Path Tablespace pathpg_tablespace_location()
Size Tablespace size at the time of the last sample in the report intervalpg_tablespace_size()
Growth Tablespace growth during the report intervalpg_tablespace_size() increment between interval bounds

If thepgpro_stats extension was available during the report interval, the report table“Wait statistics by database” shows the total wait time by wait event type and database.Table G.13 lists columns of this report table.

Table G.13. Wait statistics by database

ColumnDescription
Database Database name
Wait event type Type of event for which the backends were waiting. Asterisk means aggregation of all wait event types in the database.
Waited (s) Time spent waiting in events ofWait event type, in seconds
%Total Percentage of wait time spent in the database events ofWait event type in all wait time for the cluster

If thepgpro_stats extension was available during the report interval, the report table“Top wait events” shows top wait events in the cluster by wait time.Table G.14 lists columns of this report table.

Table G.14. Top wait events

ColumnDescription
Database Database name
Wait event type The type of event for which the backends were waiting
Wait event Wait event name for which the backends were waiting
Waited Total wait time spent inWait event of the database, in seconds
%Total Percentage of wait time spent inWait event of the database in all wait time in the cluster

G.1.11.2. Load distribution#

This section of apgpro_pwr report is based on thepgpro_stats_totals view of thepgpro_stats extension if it was available during the report interval. Each table in this section provides data for the report interval on load distribution for a certain kind of objects for which aggregated statistics are collected, such as databases, applications, hosts, or users. Each table contains one row for each resource (for example, total time or shared blocks written), where load distribution is shown in graphics, as a stacked bar chart for top objects by load of this resource. If the bar chart area that corresponds to an object is too narrow to include captions, point that area to get a hint with the caption, value and percentage. The report tables“Load distribution among heavily loaded databases”,“Load distribution among heavily loaded applications”,“Load distribution among heavily loaded hosts” and“Load distribution among heavily loaded users” show load distribution for respective objects.Table G.15 lists rows of these report tables.

Table G.15. Load distribution

RowDescriptionCalculation
Total time (sec.) Total time spent in the planning and execution of statementstotal_plan_time +total_exec_time
Executed count Number of queries executedqueries_executed
I/O time (sec.) Total time the statements spent reading or writing blocks (iftrack_io_timing is enabled, otherwise zero)blk_read_time +blk_write_time
Blocks fetched Total number of shared block cache hits and shared blocks read by the statementsshared_blks_hit +shared_blks_read
Shared blocks read Total number of shared blocks read by the statementsshared_blks_read
Shared blocks dirtied Total number of shared blocks dirtied by the statementsshared_blks_dirtied
Shared blocks written Total number of shared blocks written by the statementsshared_blks_written
WAL generated Total amount of WAL generated by the statementswal_bytes
Temp and Local blocks written Total number of temporary and local blocks written by the statementstemp_blks_written +local_blks_written
Temp and Local blocks read Total number of temp and local blocks read by the statementstemp_blks_read +local_blks_read
Invalidation messages sent Total number of all invalidation messages sent by backends in this database (pgpro_stats_totals.inval_msgs).all
Cache resets Total number of shared cache resetspgpro_stats_totals.cache_resets

G.1.11.3. Session states observed by subsamples#

This section of apgpro_pwr report provides information about session states captured by subsamples during the report interval.

Tables of this report section are described below.

The report subsection“Chart with session state” visualizes session states captured by subsamples. It is the timeline chart showing captured session states in backends and transactions. Every state contains a popup with session state attributes. Click on a state to see this state in the table of session states.

The report table“Session state statistics by database” shows the aggregated data on session states. Only session states captured in subsamples are counted.Table G.16 lists columns of this report table.

Table G.16. Session state statistics by database

ColumnDescription
Database Database name
Summary Active Overall time ofactive states captured in subsamples
Summary Idle in xact Overall time ofidle in transaction states captured in subsamples
Summary Idle in xact (A) Overall time ofidle in transaction (aborted) states captured in subsamples
Maximal Active Time of the longestactive state captured in subsamples
Maximal Idle in xact Time of the longestidle in transaction state captured in subsamples
Maximal Idle in xact (A) Time of the longestidle in transaction (aborted) state captured in subsamples
Maximal xact age Maximal transaction age detected in subsamples

The report table“Top 'idle in transaction' session states by duration” shows toppgpro_pwr.topn longestidle in transaction states that were last observed in thepg_stat_activity view for each session.Table G.17 lists columns of this report table.

Table G.17. Top 'idle in transaction' session states by duration

ColumnDescriptionField/Calculation
Database Database namedatname
User User nameusename
App Application nameapplication_name
Pid Process IDpid
Xact start Transaction start timestampxact_start
State change State change timestampstate_change
State duration State durationclock_timestamp() -state_change

The report table“Top 'active' session states by duration” shows toppgpro_pwr.topn longestactive states that were last observed in thepg_stat_activity view for each session.Table G.18 lists columns of this report table.

Table G.18. Top 'active' session states by duration

ColumnDescriptionField/Calculation
Database Database namedatname
User User nameusename
App Application nameapplication_name
Pid Process IDpid
Xact start Transaction start timestampxact_start
State change State change timestampstate_change
State duration State durationclock_timestamp() -state_change

The report table“Top states by transaction age” shows top session states by transaction age that were last observed in thepg_stat_activity view for each session.Table G.19 lists columns of this report table.

Table G.19. Top states by transaction age

ColumnDescriptionField/Calculation
Database Database namedatname
User User nameusename
App Application nameapplication_name
Pid Process IDpid
Xact start Transaction start timestampxact_start
Xact duration Transaction durationclock_timestamp() -xact_start
Age Transaction ageage(backend_xmin)
State Session state at the maximum age detected 
State change State change timestampstate_change
State duration State durationclock_timestamp() -state_change

The report table“Top states by transaction duration” shows top longest session states that were last observed in thepg_stat_activity view for each session.Table G.20 lists columns of this report table.

Table G.20. Top states by transaction duration

ColumnDescriptionField/Calculation
Database Database namedatname
User User nameusename
App Application nameapplication_name
Pid Process IDpid
Xact start Transaction start timestampxact_start
Xact duration Transaction durationclock_timestamp() -xact_start
Age Transaction ageage(backend_xmin)
State Session state at the maximum age detected 
State change State change timestampstate_change
State duration State durationclock_timestamp() -state_change

G.1.11.4. SQL query statistics#

This section of apgpro_pwr report provides data for the report interval on top statements by several important statistics. The data is mainly captured from views of the one ofpgpro_stats andpg_stat_statements extensions that was available during the report interval, with the precedence ofpgpro_stats. Each statement can be highlighted in all SQL-related sections with a single mouse click on it. This click will also show a query text preview just under the query statistics row. The query text preview can be hidden with a second click on a query.

Tables of this report section are described below.

The report table“Top SQL by elapsed time” shows top statements by the sum oftotal_plan_time andtotal_exec_time fields of thepgpro_stats_statements orpg_stat_statements view.Table G.21 lists columns of this report table. Times are provided in seconds.

Table G.21. Top SQL by elapsed time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
%Total Percentage of elapsed time of this statement plan in the total elapsed time of all statements in the cluster 
Elapsed Time (s) Total time spent in planning and execution of the statement plantotal_plan_time +total_exec_time
Plan Time (s) Total time spent in planning of the statementtotal_plan_time
Exec Time (s) Total time spent in execution of the statement plantotal_exec_time
JIT Time (s) Total time spent by JIT executing this statement plan, in secondsjit_generation_time +jit_inlining_time +jit_optimization_time +jit_emission_time
Read I/O time (s) Total time the statement spent reading blocksblk_read_time
Write I/O time (s) Total time the statement spent writing blocksblk_write_time
Usr CPU time (s) Time spent on CPU in the user space, in secondsrusage.user_time
Sys CPU time (s) Time spent on CPU in the system space, in secondsrusage.system_time
Plans Number of times the statement was plannedplans
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by planning time” shows top statements by the value of thetotal_plan_time field of thepgpro_stats_statements orpg_stat_statements view.Table G.22 lists columns of this report table.

Table G.22. Top SQL by planning time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statementDerived fromdbid
User Name of the user executing the statement Derived fromuserid
Plan elapsed(s) Total time spent in planning of the statement, in secondstotal_plan_time
%Elapsed Percentage oftotal_plan_time in the sum oftotal_plan_time andtotal_exec_time of this statement plan 
Mean plan time Mean time spent planning the statement, in millisecondsmean_plan_time
Min plan time Minimum time spent planning the statement, in millisecondsmin_plan_time
Max plan time Maximum time spent planning the statement, in millisecondsmax_plan_time
StdErr plan time Population standard deviation of time spent planning the statement, in millisecondsstddev_plan_time
Plans Number of times the statement was plannedplans
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by execution time” shows top statements by the value of thetotal_time field of thepgpro_stats_statements orpg_stat_statements view.Table G.23 lists columns of this report table.

Table G.23. Top SQL by execution time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Exec (s) Total time spent executing the statement plan, in secondstotal_exec_time
%Elapsed Percentage oftotal_exec_time of this statement plan in this statement elapsed time 
%Total Percentage oftotal_exec_time of this statement plan in the total elapsed time of all statements in the cluster 
JIT Time (s) Total time spent by JIT executing this statement plan, in secondsjit_generation_time +jit_inlining_time +jit_optimization_time +jit_emission_time
Read I/O time (s) Total time spent in reading pages while executing the statement plan, in secondsblk_read_time
Write I/O time (s) Total time spent in writing pages while executing the statement plan, in secondsblk_write_time
Usr CPU time (s) Time spent on CPU in the user space, in secondsrusage.user_time
Sys CPU time (s) Time spent on CPU in the system space, in secondsrusage.system_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Mean execution time Mean time spent executing the statement plan, in millisecondsmean_exec_time
Min execution time Minimum time spent executing the statement plan, in millisecondsmin_exec_time
Max execution time Maximum time spent executing the statement plan, in millisecondsmax_exec_time
StdErr execution time Population standard deviation of time spent executing the statement plan, in millisecondsstddev_exec_time
Executions Number of executions of this statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by mean execution time” shows toppgpro_pwr.topn statements by the value of themean_time ormean_exec_time field of thepgpro_stats_statements orpg_stat_statements view.Table G.24 lists columns of this report table.

Table G.24. Top SQL by mean execution time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Mean execution time (ms) Mean time spent executing the statement, in millisecondsmean_exec_time
Min execution time (ms) Minimum time spent executing the statement, in millisecondsmin_exec_time
Max execution time (ms) Maximum time spent executing the statement, in millisecondsmax_exec_time
StdErr execution time Population standard deviation of time spent executing the statement, in millisecondsstddev_exec_time (ms)
Exec (s) Time spent executing this statement, in secondstotal_exec_time
%Elapsed Execution time of this statement as the percentage of the statement elapsed time
%Total Execution time of this statement as the percentage of the total elapsed time of all statements in the cluster
JIT time (s) Total time spent by JIT executing this statement, in secondsjit_generation_time +jit_inlining_time +jit_optimization_time +jit_emission_time
Read I/O time (s) Time spent reading blocks, in secondsblk_read_time
Write I/O time (s) Time spent writing blocks, in secondsblk_write_time
Rows Number of rows retrieved or affected by the statementrows
Executions Number of times this statement was executedcalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by executions” shows top statements by the value of thecalls field of thepgpro_stats_statements orpg_stat_statements view.Table G.25 lists columns of this report table.

Table G.25. Top SQL by executions

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Executions Number of executions of the statement plancalls
%Total Percentage ofcalls of this statement plan in the totalcalls of all statements in the cluster 
Rows Number of rows retrieved or affected by execution of the statement planrows
Mean (ms) Mean time spent executing the statement plan, in millisecondsmean_exec_time
Min (ms) Minimum time spent executing the statement plan, in millisecondsmin_exec_time
Max (ms) Maximum time spent executing the statement plan, in millisecondsmax_exec_time
StdErr (ms) Population standard deviation of time spent executing the statement plan, in millisecondsstddev_time
Elapsed(s) Total time spent executing the statement plan, in secondstotal_exec_time
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by I/O wait time” shows top statements by read and write time, i.e., sum of values ofblk_read_time andblk_write_time fields of thepgpro_stats_statements orpg_stat_statements view.Table G.26 lists columns of this report table. Times are provided in seconds.

Table G.26. Top SQL by I/O wait time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
IO(s) Total time spent in reading and writing while executing this statement plan, i.e., I/O timeblk_read_time +blk_write_time
R(s) Total time spent in reading while executing this statement planblk_read_time
W(s) Total time spent in writing while executing this statement planblk_write_time
%Total Percentage of I/O time of this statement plan in the total I/O time of all statements in the cluster 
Shr Reads Total number of shared blocks read while executing the statement planshared_blks_read
Loc Reads Total number of local blocks read while executing the statement planlocal_blks_read
Tmp Reads Total number of temp blocks read while executing the statement plantemp_blks_read
Shr Writes Total number of shared blocks written while executing the statement planshared_blks_written
Loc Writes Total number of local blocks written while executing the statement planlocal_blks_written
Tmp Writes Total number of temp blocks written while executing the statement plantemp_blks_written
Elapsed(s) Total time spent in execution of the statement plantotal_plan_time +total_exec_time
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by shared blocks fetched” shows top statements by the number of read and hit blocks, which helps to detect the most data-intensive statements.Table G.27 lists columns of this report table.

Table G.27. Top SQL by shared blocks fetched

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Blks fetched Number of blocks retrieved while executing the statement planshared_blks_hit +shared_blks_read
%Total Percentage of blocks fetched while executing the statement plan in all blocks fetched for all statements in the cluster 
Hits(%) Percentage of blocks got from buffers in all blocks got 
Elapsed(s) Total time spent in execution of the statement plan, in secondstotal_plan_time +total_exec_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by shared blocks read” shows top statements by the number of shared reads, which helps to detect the most read-intensive statements.Table G.28 lists columns of this report table.

Table G.28. Top SQL by shared blocks read

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Reads Number of shared blocks read while executing this statement planshared_blks_read
%Total Percentage of shared reads for this statement plan in all shared reads of all statements in the cluster 
Hits(%) Percentage of blocks got from buffers in all blocks got while executing this statement plan 
Elapsed(s) Total time spent in execution of the statement plan, in secondstotal_plan_time +total_exec_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by shared blocks dirtied” shows top statements by the number of shared dirtied buffers, which helps to detect statements that do most data changes in the cluster.Table G.29 lists columns of this report table.

Table G.29. Top SQL by shared blocks dirtied

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statementDerived fromdbid
User Name of the user executing the statement Derived fromuserid
Dirtied Number of shared buffers dirtied while executing this statement planshared_blks_dirtied
%Total Percentage of dirtied shared buffers for this statement plan in all dirtied shared buffers of all statements in the cluster 
Hits(%) Percentage of blocks got from buffers in all blocks got while executing this statement plan 
WAL Total amount of WAL bytes generated by the statement planwal_bytes
%Total Percentage of WAL bytes generated by the statement plan in total WAL generated in the cluster 
Elapsed(s) Total time spent in execution of the statement plan, in secondstotal_plan_time +total_exec_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by shared blocks written” shows top statements by the number of blocks written.Table G.30 lists columns of this report table.

Table G.30. Top SQL by shared blocks written

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Written Number of blocks written while executing this statement planshared_blks_written
%Total Percentage of blocks written by this statement plan in all written blocks in the cluster Percentage ofshared_blks_written in (pg_stat_bgwriter.buffers_checkpoint+pg_stat_bgwriter.buffers_clean+pg_stat_bgwriter.buffers_backend)
%BackendW Percentage of blocks written by this statement plan in all blocks in the cluster written by backends Percentage ofshared_blks_written inpg_stat_bgwriter.buffers_backend
Hits(%) Percentage of blocks got from buffers in all blocks got while executing this statement plan 
Elapsed(s) Total time spent in execution of the statement plan, in secondstotal_plan_time +total_exec_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by WAL size” shows top statements by the amount of WAL generated.Table G.31 lists columns of this report table.

Table G.31. Top SQL by WAL size

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
WAL Total amount of WAL bytes generated by the statement planwal_bytes
%Total Percentage of WAL bytes generated by the statement plan in total WAL generated in the cluster 
Dirtied Number of shared buffers dirtied while executing this statement planshared_blks_dirtied
WAL FPI Total number of WAL full page images generated by the statement planwal_fpi
WAL records Total number of WAL records generated by the statement planwal_records
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by temp usage” shows top statements by temporary I/O, which is calculated as the sum oftemp_blks_read,temp_blks_written,local_blks_read andlocal_blks_written fields.Table G.32 lists columns of this report table.

Table G.32. Top SQL by temp usage

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Local fetched Number of local blocks retrievedlocal_blks_hit +local_blks_read
Hits(%) Percentage of local blocks got from buffers in all local blocks got 
Write Local (blk) Number of blocks written by this statement plan that are used in temporary tableslocal_blks_written
Write Local %Total Percentage oflocal_blks_written of this statement plan in the total oflocal_blks_written for all statements in the cluster 
Read Local (blk) Number of blocks read by this statement plan that are used in temporary tableslocal_blks_read
Read Local %Total Percentage oflocal_blks_read of this statement plan in the total oflocal_blks_read for all statements in the cluster 
Write Temp (blk) Number of temp blocks written by this statement plantemp_blks_written
Write Temp %Total Percentage oftemp_blks_written of this statement plan in the total oftemp_blks_written for all statements in the cluster 
Read Temp (blk) Number of temp blocks read by this statement plantemp_blks_read
Read Temp %Total Percentage oftemp_blks_read of this statement plan in the total oftemp_blks_read for all statements in the cluster 
Elapsed(s) Total time spent in execution of the statement plan, in secondstotal_plan_time +total_exec_time
Rows Number of rows retrieved or affected by execution of the statement planrows
Executions Number of executions of the statement plancalls
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

The report table“Top SQL by invalidation messages sent” shows top statements by the number of invalidation messages sent.Table G.33 lists columns of this report table.

Table G.33. Top SQL by invalidation messages sent

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements).queryid
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Invalidation messages sent Total number of invalidation messages sent by backends executing this statement. Statistics are provided for corresponding message types ofpgpro_stats_inval_msgsfields ofpgpro_stats_statements.inval_msgs

G.1.11.4.1. rusage statistics#

This section is included in the report only if thepgpro_stats orpg_stat_kcache extension was available during the report interval.

The report table“Top SQL by system and user time” shows top statements by the sum ofuser_time andsystem_time fields ofpg_stat_kcache or of thepgpro_stats_totals view.Table G.34 lists columns of this report table.

Table G.34. Top SQL by system and user time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Plan User (s) User CPU time elapsed during planning, in secondsplan_user_time
Exec User (s) User CPU time elapsed during execution, in secondsexec_user_time
User %Total Percentage ofplan_user_time +exec_user_time in the total user CPU time for all statements 
Plan System (s) System CPU time elapsed during planning, in secondsplan_system_time
Exec System (s) System CPU time elapsed during execution, in secondsexec_system_time
System %Total Percentage ofplan_system_time +exec_system_time in the total system CPU time for all statements 

The report table“Top SQL by reads/writes done by filesystem layer” shows top statements by the sum ofreads andwrites fields ofpg_stat_kcache.Table G.35 lists columns of this report table.

Table G.35. Top SQL by reads/writes done by filesystem layer

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
Plan Read Bytes Bytes read during planningplan_reads
Exec Read Bytes Bytes read during executionexec_reads
Read Bytes %Total Percentage ofplan_reads +exec_reads in the total number of bytes read by the filesystem layer for all statements 
Plan Writes Bytes written during planningplan_writes
Exec Writes Bytes written during executionexec_writes
Write %Total Percentage ofplan_writes +exec_writes in the total number of bytes written by the filesystem layer for all statements 

G.1.11.5. SQL query wait statistics#

If thepgpro_stats extension was available during the report interval, this section of the report will contain a table that is split into sections, each showing top statements by overall wait time or by wait time for a certainwait event type. Table sections related to specific wait events follow in the descending order of the total wait time in wait events of this type.Table G.36 lists columns of this report table. Times are provided in seconds.

Table G.36. SQL query wait statistics

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Hash of the statement planplanid
Database Database name for the statementDerived fromdbid
User Name of the user executing the statement Derived fromuserid
Waited Total wait time for all wait events of this statement plan 
%Total Percentage of the total wait time of this statement plan in all the wait time in the cluster 
Details Waits of this statement plan by wait types 

If the JIT-related statistics was avaliable in the statement statistics extension during the report interval, the report table“Top SQL by JIT elapsed time” shows top statements by the sum ofjit_*_time fields of thepgpro_stats_statements orpg_stat_statements view.Table G.37 lists columns of this report table. Times are provided in seconds.

Table G.37. Top SQL by JIT elapsed time

ColumnDescriptionField/Calculation
Query ID Hex representation ofqueryid. The hash of the query ID, database ID and user ID is in square brackets. The(N) mark will appear here for nested statements (such as statements invoked within top-level statements). 
Plan ID Internal hash code, computed from the tree of the statement planplanid
Database Database name for the statement Derived fromdbid
User Name of the user executing the statement Derived fromuserid
JIT Time (s) Total time spent by JIT executing this statement planjit_generation_time +jit_inlining_time +jit_optimization_time +jit_emission_time
Generation count Total number of functions JIT-compiled by this statement Sum ofjit_functions
Generation time Total time spent by this statement on generating JIT code Sum ofjit_generation_time
Inlining count Number of times functions have been inlined Sum ofjit_inlining_count
Inlining time Total time spent by this statement on inlining functions Sum ofjit_inlining_time
Optimization count Number of times this statement has been optimized Sum ofjit_optimization_count
Optimization time Total time spent by this statement on optimizing Sum ofjit_optimization_time
Emission count Number of times code has been emitted Sum ofjit_emission_count
Emission time Total time spent by this statement on emitting code Sum ofjit_emission_time
Deform count Number of tuple deform functions JIT-compiled by the statement
Deform time Total time spent by the statement on JIT-compiling the tuple deform
Plan Time (s) Total time spent in planning of the statementtotal_plan_time
Exec Time (s) Total time spent in execution of the statement plantotal_exec_time
Read I/O time (s) Total time the statement spent reading blocksblk_read_time
Write I/O time (s) Total time the statement spent writing blocksblk_write_time
%Cvr Coverage: duration of statement statistics collection as the percentage of the report duration

G.1.11.6. Complete list of SQL texts#

The“Complete list of SQL texts” section of the report contains a table that provides query and plan texts for all statements mentioned in the report. Use an appropriateQuery ID/Plan ID link in any statistics table to see the corresponding query/plan text.Table G.38 lists columns of this report table.

Table G.38. Complete list of SQL texts

ColumnDescription
ID Hex representation of the query or plan identifier
Query/Plan Text Text of the query or statement plan

G.1.11.7. Schema object statistics#

Tables in this section of the report show top database objects by statistics from thePostgres Pro'sStatistics Collector views. Report tables that contain data for tables and indexes provide a preview of storage parameters. You can click on a row to see storage parameters of the object right under the row.

The report table“Top tables by estimated sequentially scanned volume” shows top tables by estimated volume read by sequential scans. This can help you find database tables that possibly lack some index. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets. The data is based on thepg_stat_all_tables view.Table G.39 lists columns of this report table.

Table G.39. Top tables by estimated sequentially scanned volume

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
~SeqBytes Estimated volume read by sequential scansSum of (pg_relation_size() *seq_scan)
SeqScan Number of sequential scans performed on the tableseq_scan
IxScan Number of index scans initiated on the tableidx_scan
IxFet Number of live rows fetched by index scansidx_tup_fetch
Ins Number of rows insertedn_tup_ins
Upd Number of rows updatedn_tup_upd
Del Number of rows deletedn_tup_del
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd

In the report table“Top tables by blocks fetched”, blocks fetched include blocks being processed from disk (read) and from shared buffers (hit). This report table shows top database tables by the sum of blocks fetched for the table's heap, indexes, TOAST table (if any) and TOAST table index (if any). This can help you focus on tables with excessive processing of blocks. The data is based on thepg_statio_all_tables view.Table G.40 lists columns of this report table.

Table G.40. Top tables by blocks fetched

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Heap Blks Number of blocks fetched for the table's heapheap_blks_read +heap_blks_hit
Heap Blks %Total Percentage of blocks fetched for the table's heap in all blocks fetched in the cluster 
Ix Blks Number of blocks fetched for table's indexesidx_blks_read +idx_blks_hit
Ix Blks %Total Percentage of blocks fetched for table's indexes in all blocks fetched in the cluster 
TOAST Blks Number of blocks fetched for the table's TOAST tabletoast_blks_read +toast_blks_hit
TOAST Blks %Total Percentage of blocks fetched for the table's TOAST table in all blocks fetched in the cluster 
TOAST-Ix Blks Number of blocks fetched for the table's TOAST indextidx_blks_read +tidx_blks_hit
TOAST-Ix Blks %Total Percentage of blocks fetched for the table's TOAST index in all blocks fetched in the cluster 

The report table“Top tables by blocks read” shows top database tables by the number of blocks read for the table's heap, indexes, TOAST table (if any) and TOAST table index (if any). This can help you focus on tables with excessive block readings. The data is based on thepg_statio_all_tables view.Table G.41 lists columns of this report table.

Table G.41. Top tables by blocks read

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Heap Blks Number of blocks read for the table's heapheap_blks_read
Heap Blks %Total Percentage of blocks read from the table's heap in all blocks read in the cluster 
Ix Blks Number of blocks read from table's indexesidx_blks_read
Ix Blks %Total Percentage of blocks read from table's indexes in all blocks read in the cluster 
TOAST Blks Number of blocks read from the table's TOAST tabletoast_blks_read
TOAST Blks %Total Percentage of blocks read from the table's TOAST table in all blocks read in the cluster 
TOAST-Ix Blks Number of blocks read from the table's TOAST indextidx_blks_read
TOAST-Ix Blks %Total Percentage of blocks read from the table's TOAST index in all blocks read in the cluster 
Hit(%) Percentage of table, index, TOAST and TOAST index blocks got from buffers for this table in all blocks got for this table from either file system or buffers 

The report table“Top DML tables” shows top tables by the number of DML-affected rows, i.e., by the sum ofn_tup_ins,n_tup_upd andn_tup_del (including TOAST tables). The data is based on thepg_stat_all_tables view.Table G.42 lists columns of this report table.

Table G.42. Top DML tables

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Ins Number of rows insertedn_tup_ins
Upd Number of rows updated, including HOTn_tup_upd
Del Number of rows deletedn_tup_del
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd
SeqScan Number of sequential scans performed on the tableseq_scan
SeqFet Number of live rows fetched by sequential scansseq_tup_read
IxScan Number of index scans initiated on this tableidx_scan
IxFet Number of live rows fetched by index scansidx_tup_fetch

The report table“Top tables by updated/deleted tuples” shows top tables by tuples modified by UPDATE/DELETE operations, i.e., by the sum ofn_tup_upd andn_tup_del (including TOAST tables). The data is based on thepg_stat_all_tables view.Table G.43 lists columns of this report table.

Table G.43. Top tables by updated/deleted tuples

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Upd Number of rows updated, including HOTn_tup_upd
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd
Del Number of rows deletedn_tup_del
Vacuum Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
AutoVacuum Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count
Analyze Number of times this table was manually analyzedanalyze_count
AutoAnalyze Number of times this table was analyzed by the autovacuum daemonautoanalyze_count

The report table“Top tables by removed all-visible marks” shows top tables by the number of times that the all-visible mark was removed from the visibility map by any backend. This report section is only shown when corresponding statistics are available.Table G.44 lists columns of this report table.

Table G.44. Top tables by removed all-visible marks

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
All-Visible marks cleared Number of times that the all-visible mark was removed from the relation visibility maprev_all_visible_pages
All-Visible marks set Number of times that the all-visible mark was set in the relation visibility mappages_all_visible
All-Visible marks %Set Percentage of the number of times that the all-visible mark was set in the number of times that it was set or removedpages_all_visible * 100% / (rev_all_visible_pages +pages_all_visible)
Vacuum Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
AutoVacuum Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count

The report table“Top tables by removed all-frozen marks” shows top tables by the number of times that the all-frozen mark was removed from the visibility map by any backend. This report section is only shown when corresponding statistics are available.Table G.45 lists columns of this report table.

Table G.45. Top tables by removed all-frozen marks

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
All-Frozen marks cleared Number of times that the all-frozen mark was removed from the relation visibility maprev_all_frozen_pages
All-Frozen marks set Number of times that the all-frozen mark was set in the relation visibility mappages_frozen
All-Frozen marks %Set Percentage of the number of times that the all-frozen mark was set in the number of times that it was set or removedpages_frozen * 100% / (rev_all_frozen_pages +pages_frozen)
Vacuum Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
AutoVacuum Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count

The report table“Top tables by new-page updated tuples” shows top tables by the number of rows updated where the successor version goes onto anew heap page, leaving behind an original version with at_ctid field that points to a different heap page. These are always non-HOT updates.Table G.46 lists columns of this report table.

Table G.46. Top tables by new-page updated tuples

ColumnDescription
DB Database name for the table
Tablespace Name of the tablespace where the table is located
Schema Schema name for the table
Table Table name
NP Upd Number of rows updated to a new heap page
%Upd Number of new-page updated rows as the percentage of all rows updated
Upd Number of rows updated, including HOT
Upd(HOT) Number of rows HOT updated (i.e., with no separate index update required)

The report table“Top growing tables” shows top tables by growth. The data is based on thepg_stat_all_tables view. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets.Table G.47 lists columns of this report table.

Table G.47. Top growing tables

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Size Table size at the time of the last sample in the report intervalpg_table_size() -pg_relation_size(toast)
Growth Table growth 
Ins Number of rows insertedn_tup_ins
Upd Number of rows updated, including HOTn_tup_upd
Del Number of rows deletedn_tup_del
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd

In the report table“Top indexes by blocks fetched”, blocks fetched include index blocks processed from disk (read) and from shared buffers (hit). The data is based on thepg_statio_all_indexes view.Table G.48 lists columns of this report table.

Table G.48. Top indexes by blocks fetched

ColumnDescriptionField/Calculation
DB Database name for the index 
Tablespace Name of the tablespace where the index is located 
Schema Schema name for the underlying table 
Table Underlying table name 
Index Index name 
Scans Number of index scans initiated on this indexidx_scan
Blks Number of blocks fetched for this indexidx_blks_read +idx_blks_hit
%Total Percentage of blocks fetched for this index in all blocks fetched in the cluster 

The report table“Top indexes by blocks read” is also based on thepg_statio_all_indexes andpg_stat_all_indexes views.Table G.49 lists columns of this report table.

Table G.49. Top indexes by blocks read

ColumnDescriptionField/Calculation
DB Database name for the index 
Tablespace Name of the tablespace where the index is located 
Schema Schema name for the underlying table 
Table Underlying table name 
Index Index name 
Scans Number of index scans initiated on this indexidx_scan
Blk Reads Number of disk blocks read from this indexidx_blks_read
%Total Percentage of disk blocks read from this index in all disk blocks read in the cluster 
Hits(%) Percentage of index blocks got from buffers in all index blocks got for this index 

The report table“Top growing indexes” shows top indexes by growth. The table uses data from thepg_stat_all_tables andpg_stat_all_indexes views. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets.Table G.50 lists columns of this report table.

Table G.50. Top growing indexes

ColumnDescriptionField/Calculation
DB Database name for the index 
Tablespace Name of the tablespace where the index is located 
Schema Schema name for the underlying table 
Table Underlying table name 
Index Index name 
Index Size Index size at the time of the last sample in the report intervalpg_relation_size()
Index Growth Index growth during the report interval 
Table Ins Number of rows inserted into the underlying tablen_tup_ins
Table Upd Number of rows updated in the underlying table, without HOTn_tup_upd -n_tup_hot_upd
Table Del Number of rows deleted from the underlying tablen_tup_del

The report table“Unused indexes” shows top non-scanned indexes (during the report interval) by DML operations on underlying tables that caused index support. Constraint indexes are not counted. The table uses data from thepg_stat_all_tables view.Table G.51 lists columns of this report table.

Table G.51. Unused indexes

ColumnDescriptionField/Calculation
DB Database name for the index 
Tablespace Name of the tablespace where the index is located 
Schema Schema name for the underlying table 
Table Underlying table name 
Index Index name 
Index Size Index size at the time of the last sample in the report intervalpg_relation_size()
Index Growth Index growth during the report interval 
Table Ins Number of rows inserted into the underlying tablen_tup_ins
Table Upd Number of rows updated in the underlying table, without HOTn_tup_upd -n_tup_hot_upd
Table Del Number of rows deleted from the underlying tablen_tup_del

G.1.11.8. User function statistics#

Tables in this section of the report show top functions in the cluster by statistics from thepg_stat_user_functions view. Times in the tables are provided in seconds.

The report table“Top functions by total time” shows top functions by the total time elapsed. The report table“Top functions by executions” shows top functions by the number of executions. The report table“Top trigger functions by total time” shows top trigger functions by the total time elapsed.Table G.52 lists columns of these report tables.

Table G.52. User function statistics

ColumnDescriptionField/Calculation
DB Database name for the function 
Schema Schema name for the function 
Function Function name 
Executions Number of times this function has been calledcalls
Total Time (s) Total time spent in this function and all other functions called by ittotal_time
Self Time (s) Total time spent in this function itself, not including other functions called by itself_time
Mean Time (s) Mean time of a single function executiontotal_time/calls
Mean self Time (s) Mean self time of a single function executionself_time/calls

G.1.11.9. Vacuum-related statistics#

The report table“Top tables by vacuum time spent” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top tables by total time spent vacuuming them. The data is based on thepgpro_stats_vacuum_tables view.Table G.53 lists columns of this report table.

Table G.53. Top tables by vacuum time spent

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Total vacuum time Total time of vacuuming this tabletotal_time
Delay vacuum time Time spent sleeping in a vacuum delay point by vacuum operations performed on this tabledelay_time
Read I/O time Time spent reading database blocks by vacuum operations performed on this tableblk_read_time
Write I/O time Time spent writing database blocks by vacuum operations performed on this tableblk_write_time
User CPU time User CPU time of vacuuming tables of this databaseuser_time
System CPU time System CPU time of vacuuming tables of this databasesystem_time
Vacuum count Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum count Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count
Total fetched Total number of database blocks fetched by vacuum operations performed on this tabletotal_blks_read +total_blks_hit
Heap fetched Total number of blocks fetched from this table by vacuum operations performed on itrel_blks_read +rel_blks_hit
Scanned Number of pages examined by vacuum operations performed on this tablepages_scanned

The report table“Top indexes by vacuum time spent” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top indexes by total time spent vacuuming them. The data is based on thepgpro_stats_vacuum_indexes view.Table G.54 lists columns of this report table.

Table G.54. Top indexes by vacuum time spent

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Index Index name 
Total vacuum time Total time of vacuuming this indextotal_time
Delay vacuum time Time spent sleeping in a vacuum delay point by vacuum operations performed on this indexdelay_time
Read I/O time Time spent reading database blocks by vacuum operations performed on this indexblk_read_time
Write I/O time Time spent writing database blocks by vacuum operations performed on this indexblk_write_time
User CPU time User CPU time of vacuuming this indexuser_time
System CPU time System CPU time of vacuuming this indexsystem_time
Vacuum count Number of times the underlying table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum count Number of times the underlying table has been vacuumed by the autovacuum daemonautovacuum_count
Total fetched Total number of database blocks fetched by vacuum operations performed on the underlying tabletotal_blks_read +total_blks_hit
Index fetched Total number of blocks fetched from this index by vacuum operations performed on itrel_blks_read +rel_blks_hit

The report table“Top tables by blocks vacuum fetched” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top tables by blocks fetched vacuuming these tables. The data is based on thepgpro_stats_vacuum_tables view.Table G.55 lists columns of this report table.

Table G.55. Top tables by blocks vacuum fetched

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
DB fetched Total number of database blocks fetched by vacuum operations performed on this tabletotal_blks_read +total_blks_hit
%Total Total number of database blocks fetched by vacuum operations performed on this table as the percentage of all blocks fetched in the cluster
DB read Total number of database blocks read by vacuum operations performed on this tabletotal_blks_read
%Total Total number of database blocks read by vacuum operations performed on this table as the percentage of all blocks read in the cluster
Heap fetched Total number of blocks fetched from this table by vacuum operations performed on itrel_blks_read +rel_blks_hit
%Rel Total number of table blocks fetched by vacuum operations performed on this table as the percentage of all blocks fetched from this table
Heap read Total number of blocks read from this table by vacuum operations performed on itrel_blks_read
%Rel Total number of table blocks read by vacuum operations performed on this table as the percentage of all blocks read from this table
Scanned Number of pages examined by vacuum operations performed on this tablepages_scanned

The report table“Top indexes by blocks vacuum fetched” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top indexes by blocks fetched vacuuming underlying tables. The data is based on thepgpro_stats_vacuum_indexes view.Table G.56 lists columns of this report table.

Table G.56. Top indexes by blocks vacuum fetched

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Index Index name 
DB fetched Total number of database blocks fetched by vacuum operations performed on this indextotal_blks_read +total_blks_hit
%Total Total number of database blocks fetched by vacuum operations performed on this index as the percentage of all blocks fetched in the cluster
DB read Total number of database blocks read by vacuum operations performed on this indextotal_blks_read
%Total Total number of database blocks read by vacuum operations performed on this index as the percentage of all blocks read in the cluster
Idx fetched Total number of blocks fetched from this index by vacuum operations on itrel_blks_read +rel_blks_hit
%Idx Total number of index blocks fetched by vacuum operations performed on this index as the percentage of all blocks fetched from this index
Idx read Total number of blocks read from this index by vacuum operations performed on itrel_blks_read
%Idx Total number of index blocks read by vacuum operations performed on this index as the percentage of all blocks read from this index

The report table“Top tables by blocks vacuum read” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top tables by blocks read vacuuming these tables. The data is based on thepgpro_stats_vacuum_indexes view.Table G.57 lists columns of this report table.

Table G.57. Top tables by blocks vacuum read

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
DB read Total number of database blocks read by vacuum operations performed on this tabletotal_blks_read
%Total Total number of database blocks read by vacuum operations performed on this table as the percentage of all blocks read in the cluster
%Hit Total number of database blocks found in shared buffers by vacuum operations performed on this table as the percentage of all blocks fetched by vacuum operations performed on this table
Heap read Total number of database blocks vacuum operations read from this tablerel_blks_read
%Rel Total number of table blocks read by vacuum operations performed on this table as the percentage of all blocks read from this table
%Hit Total number of table blocks found in shared buffers by vacuum operations performed on this table as the percentage of table blocks fetched by vacuum operations performed on this table
Scanned Number of pages examined by vacuum operations performed on this tablepages_scanned

The report table“Top indexes by blocks vacuum read” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top indexes by blocks read vacuuming underlying tables. The data is based on thepgpro_stats_vacuum_indexes view.Table G.58 lists columns of this report table.

Table G.58. Top indexes by blocks vacuum read

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Index Index name 
DB read Total number of database blocks read by vacuum operations performed on this indextotal_blks_read
%Total Total number of database blocks read by vacuum operations performed on this index as the percentage of all blocks read in the cluster
%Hit Total number of database blocks found in shared buffers by vacuum operations performed on this index as the percentage of all database blocks fetched by vacuum operations performed on this index
Idx read Total number of blocks read from this index by vacuum operations performed on itrel_blks_read
%Idx Total number of index blocks read by vacuum operations performed on this index as the percentage of all blocks read from this index
%Hit Total number of index blocks found in shared buffers by vacuum operations performed on this index as the percentage of index blocks fetched by vacuum operations performed on this index

The report table“Top tables by dead tuples vacuum left” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top tables by the number of dead tuples left by vacuum due to their visibility in transactions. The data is based on thepgpro_stats_vacuum_tables view.Table G.59 lists columns of this report table.

Table G.59. Top tables by dead tuples vacuum left

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Dead tuples left Total number of dead tuples vacuum operations left in this table due to their visibility in transactionsdead_tuples
Dead tuples deleted Total number of dead tuples vacuum operations deleted from this tabletuples_deleted
%Eff Vacuum efficiency in terms of deleted tuples. This is the percentage of dead tuples deleted from this table in all dead tuples to be deleted from this table.tuples_deleted * 100 / (tuples_deleted +dead_tuples)
Tuples del Number of rows deletedpg_stat_all_tables.n_tup_del
Tuples upd Number of rows updated (includes HOT updated rows)pg_stat_all_tables.n_tup_upd
Vacuum Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count

The report table“Top tables by WAL size generated by vacuum” is available ifpgpro_stats can provide extended vacuum statistics. This table shows top tables by the amount of WAL generated by vacuum operations performed on them. The data is based on thepgpro_stats_vacuum_tables view.Table G.60 lists columns of this report table.

Table G.60. Top tables by WAL size generated by vacuum

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
WAL size Total amount of WAL bytes generated by vacuum operations performed on this tablewal_bytes
%Total Total amount of WAL bytes generated by vacuum operations performed on this table as the percentage of all WAL generated in the cluster
WAL FPI Total number of WAL full page images generated by vacuum operations performed on this tablewal_fpi
Scanned blocks Number of pages examined by vacuum operations performed on this tablepages_scanned
Dirtied blocks Number of database blocks dirtied by vacuum operations performed on this tabletotal_blks_dirtied
Removed blocks Number of pages removed by vacuum operations performed on this tablepages_removed
Vacuum Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count

The report table“Top tables by vacuum operations” shows top tables by the number of vacuum operations performed (vacuum_count +autovacuum_count). The data is based on thepg_stat_all_tables view.Table G.61 lists columns of this report table.

Table G.61. Top tables by vacuum operations

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Vacuum count Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum count Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count
Ins Number of rows insertedn_tup_ins
Upd Number of rows updated (includes HOT updated rows)n_tup_upd
Del Number of rows deletedn_tup_del
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd

The report table“Top tables by analyze operations” shows top tables by the number of analyze operations performed (analyze_count +autoanalyze_count). The data is based on thepg_stat_all_tables view.Table G.62 lists columns of this report table.

Table G.62. Top tables by analyze operations

ColumnDescriptionField/Calculation
DB Database name for the table 
Tablespace Name of the tablespace where the table is located 
Schema Schema name for the table 
Table Table name 
Analyze count Number of times this table has been manually analyzedanalyze_count
Autoanalyze count Number of times this table has been analyzed by the autovacuum daemonautoanalyze_count
Ins Number of rows insertedn_tup_ins
Upd Number of rows updated, including HOTn_tup_upd
Del Number of rows deletedn_tup_del
Upd(HOT) Number of rows HOT updatedn_tup_hot_upd

The report table“Top indexes by estimated vacuum load” shows top indexes by estimated implicit vacuum load. This load is calculated as the number of vacuum operations performed on the underlying table multiplied by the index size. The data is based on thepg_stat_all_indexes view. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets.Table G.63 lists columns of this report table.

Table G.63. Top indexes by estimated vacuum load

ColumnDescriptionField/Calculation
DB Database name for the index 
Tablespace Name of the tablespace where the index is located 
Schema Schema name for the underlying table 
Table Underlying table name 
Index Index name 
~Vacuum bytes Vacuum load estimation (vacuum_count +autovacuum_count) *index_size
Vacuum count Number of times this table has been manually vacuumed (not countingVACUUM FULL)vacuum_count
Autovacuum count Number of times this table has been vacuumed by the autovacuum daemonautovacuum_count
IX size Average index size during the report interval 
Relsize Average relation size during the report interval 

The report table“Top tables by dead tuples ratio” shows top tables larger than 5 MB by the ratio of dead tuples. Statistics are valid for the last sample in the report interval. The data is based on thepg_stat_all_tables view. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets.Table G.64 lists columns of this report table.

Table G.64. Top tables by dead tuples ratio

ColumnDescriptionField/Calculation
DB Database name for the table 
Schema Schema name for the table 
Table Table name 
Live Estimated number of live rowsn_live_tup
Dead Estimated number of dead rowsn_dead_tup
%Dead Percentage of dead rows in all table rows 
Last AV Last time at which this table was vacuumed by the autovacuum daemonlast_autovacuum
Size Table sizepg_table_size() -pg_relation_size(toast)

The report table“Top tables by modified tuples ratio” shows top tables larger than 5 MB by the ratio of modified tuples. Statistics are valid for the last sample in the report interval. The data is based on thepg_stat_all_tables view. When there are no relation sizes collected withpg_relation_size(), relation-size estimates are based on thepg_class.relpages field. Since such values are less accurate, they are shown in square brackets.Table G.65 lists columns of this report table.

Table G.65. Top tables by modified tuples ratio

ColumnDescriptionField/Calculation
DB Database name for the table 
Schema Schema name for the table 
Table Table name 
Live Estimated number of live rowsn_live_tup
Dead Estimated number of dead rowsn_dead_tup
Mod Estimated number of rows modified since this table was last analyzedn_mod_since_analyze
%Mod Percentage of modified rows in all table rows 
Last AA Last time at which this table was analyzed by the autovacuum daemonlast_autoanalyze
Size Table sizepg_table_size() -pg_relation_size(toast)

G.1.11.10. Cluster settings during the report interval#

This section of the report contains a table withPostgres Pro GUC parameters, values of functionsversion(),pg_postmaster_start_time(),pg_conf_load_time() and thesystem_identifier field of thepg_control_system() function during the report interval. The data in the table is grouped underDefined settings andDefault settings.Table G.66 lists columns of this report table.

Table G.66. Cluster settings during the report interval

ColumnDescription
Setting Name of the parameter
reset_valreset_val field of thepg_settings view. Settings changed during the report interval are shown in bold font.
Unit Unit of the setting
Source Configuration file where this setting is defined, semicolon, line number
Notes Timestamp of the sample where this value was first observed

G.1.11.11. Extension versions during the report interval#

This section of the report contains a table that lists installed extension versions found in databases during the report interval.First seen andLast seen columns are not shown if the extension versions have not changed during the report interval.Table G.67 lists columns of this report table.

Table G.67. Extension versions during the report interval

ColumnDescription
Name Extension name
DB Database name
First seen Timestamp of the sample where this extension version appeared first
Last seen Timestamp of the sample where this extension version appeared last
Version Version name of the extension

G.1.12. pgpro_pwr Diagnostic Tools#

pgpro_pwr provides self-diagnostic tools.

G.1.12.1. Collecting Detailed Timing Statistics for Sampling Procedures#

pgpro_pwr collects detailed timing statistics of taking samples when thepgpro_pwr.track_sample_timings parameter is on. You can get the results from thev_sample_timings view.Table G.68 lists columns of this view.

Table G.68. v_sample_timings View

ColumnDescription
server_name Name of the server
sample_id Sample identifier
sample_time Time when the sample was taken
sampling_event Sampling stage. SeeTable G.69 for descriptions of sampling stages.
time_spent Time spent in the event

Table G.69. sampling_event Description

EventDescription
total Taking the sample (all stages)
connect Makingdblink connection to the server
get server environment Getting server GUC parameters, available extensions, etc.
collect database stats Querying thepg_stat_database view for statistics on databases
calculate database stats Calculating differential statistics on databases since the previous sample
collect tablespace stats Querying thepg_tablespace view for statistics on tablespaces
collect statement stats Collecting statistics on statements using thepgpro_stats andpg_stat_kcache extensions
query pg_stat_bgwriter Collecting cluster statistics using thepg_stat_bgwriter view
query pg_stat_archiver Collecting cluster statistics using thepg_stat_archiver view
collect object stats Collecting statistics on database objects. Includes events fromTable G.70.
maintain repository Executing support routines
calculate tablespace stats Calculating differential statistics on tablespaces
calculate object stats Calculating differential statistics on database objects. Includes events fromTable G.71.
calculate cluster stats Calculating cluster differential statistics
calculate archiver stats Calculating archiver differential statistics
delete obsolete samples Deleting obsolete baselines and samples

Table G.70. Events of Collecting Statistics on Database Objects

EventDescription
db:dbnamecollect tables stats Collecting statistics on tables for thedbname database
db:dbnamecollect indexes stats Collecting statistics on indexes for thedbname database
db:dbnamecollect functions stats Collecting statistics on functions for thedbname database

Table G.71. Events of Calculating Differences of Statistics on Database Objects

EventDescription
calculate tables stats Calculating differential statistics on tables of all databases
calculate indexes stats Calculating differential statistics on indexes of all databases
calculate functions stats Calculating differential statistics on functions of all databases

G.1.13. Important Notes#

When using thepgpro_pwr extension, be aware of the following:

  • Postgres Pro collects execution statisticsafter the execution is complete. If a single execution of a statement lasts for several samples, it will only affect statistics of the last sample (in which the execution completed). Besides, statistics on statements that are still running are unavailable. Maintenance processes, such as vacuum and checkpointer, will update the statistics only on completion.

  • Resetting anyPostgres Pro statistics may affect the accuracy of the next sample.

  • Exclusive locks on relations conflict with calculation of the relation size. If thetake_sample() function is unable to acquire a lock for a short period of time (3 seconds), it will fail and no sample will be generated.

Prev Up Next
Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages Home G.2. pgpro_stats — a means for tracking planning and execution statistics of all SQL statements executed by a server
pdfepub
Go to Postgres Pro Standard 17
By continuing to browse this website, you agree to the use of cookies. Go toPrivacy Policy.
[8]ページ先頭
©2009-2025 Movatter.jp