F.35. pgpro_pwr
Prev UpAppendix F. Additional Supplied ModulesHome Next

F.35. pgpro_pwr

F.35.1. pgpro_pwr Architecture
F.35.2. Prerequisites
F.35.3. Installation and Setup
F.35.4. Managing Servers
F.35.5. Managing Samples
F.35.6. Managing the Collection of Relation Sizes
F.35.7. Managing Baselines
F.35.8. Data Export and Import
F.35.9. Report Generation Functions
F.35.10. pgpro_pwr Report Sections
F.35.11. pgpro_pwr Diagnostic Tools
F.35.12. Important Notes

The pgpro_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 on Postgres Pro's Statistics Collector views and the pgpro_stats or pg_stat_statements extension.

Note

Although pgpro_pwr can work with the pg_stat_statements extension, it is recommended that you use the pgpro_stats extension since it provides statement plans and wait events sampling.

Below, use of pgpro_stats is assumed unless otherwise noted.

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

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() (see pgpro_stats for the function description) is called to ensure that statement statistics will not be lost when the statements count exceed pgpro_stats.max (see Section F.37.7.1). The report will also contain a section informing you of whether the count of captured statements in any sample reaches 90% of pgpro_stats.max.

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

F.35.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 to take samples and maintain the repository by removing obsolete sample data.

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

  • Administrative functions allow you to create and manage servers and baselines.

F.35.2. Prerequisites

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

F.35.2.1. For the pgpro_pwr Database

The pgpro_pwr extension depends on PL/pgSQL and the dblink extension.

F.35.2.2. For the Target Server

The target server must allow connections to all databases from the server where pgpro_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 for pgpro_pwr since the functionality of the pgpro_stats or pg_stat_statements extensions will be provided through this database. Note, however, that pgpro_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 of pgpro_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 the Postgres Pro's Statistics Collector as follows: 

            track_activities = on
        track_counts = on
        track_io_timing = on
        track_functions = all/pl

F.35.3. Installation and Setup

Note

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

Although the use of pgpro_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:

F.35.3.1. Simple Setup

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

Create a schema for the pgpro_pwr installation and create the extension: 

CREATE SCHEMA profile;
CREATE EXTENSION pgpro_pwr SCHEMA profile;

F.35.3.2. Complex Setup

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

F.35.3.2.1. In the Target Server Database

Create a user for pgpro_pwr on the target server: 

CREATE USER stat_user PASSWORD 'stat_user_pwd';

Make sure this user has permissions to connect to any database in the target cluster (by default, it is true) and that pg_hba.conf permits such a connection from the pgpro_pwr database host. Also, grant stat_user with membership in the pg_read_all_stats role and the EXECUTE privilege on the pgpro_stats_statements_reset() function: 

GRANT pg_read_all_stats TO stat_user;
GRANT EXECUTE ON FUNCTION pgpro_stats_statements_reset TO stat_user;
F.35.3.2.2. In the pgpro_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 the pgpro_pwr installation: 

CREATE SCHEMA profile AUTHORIZATION pwr_user;

Grant the USAGE privilege on the schema where the dblink extension resides: 

GRANT USAGE ON SCHEMA public TO pwr_user;

Create the extension using pwr_user account: 

\c - pwr_user
CREATE EXTENSION pgpro_pwr SCHEMA profile;

Define the connection parameters of the target server for pgpro_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 the dblink_connect() call while executing the take_sample() function.

Note

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

F.35.3.3. Setting Extension Parameters

In postgresql.conf, you can define the following pgpro_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 is 20.

pgpro_pwr.max_sample_age (integer)

Retention time of the sample, in days. Samples aged pgpro_pwr.max_sample_age days and older are automatically deleted on the next take_sample() call.

The default value is 7 days.

pgpro_pwr.track_sample_timings (boolean)

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

The default value is off.

F.35.4. Managing Servers

Once installed, pgpro_pwr creates one enabled local server for the current cluster. If a server is enabled, pgpro_pwr includes it in sampling when no server is explicitly specified (see take_sample() for details). A server that is not enabled is referred to as disabled.

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

F.35.4.1. Server Management Functions

Use the following pgpro_pwr functions for server management:

create_server(server name, connstr text, enabled boolean DEFAULT TRUE, max_sample_age integer DEFAULT NULL description text DEFAULT NULL)

Creates a server definition.

Arguments:

  • server — server name. Must be unique.

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

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

  • max_sample_age — retention time of the sample. Overrides the global pgpro_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(server name)

Drops a server and all its samples.

set_server_description(server name description text)

Sets a new server description.

enable_server(server name)

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

disable_server(server name)

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

rename_server(server name, new_name name)

Renames a server.

set_server_max_sample_age(server name, max_sample_age integer)

Sets the retention period for a server (in days). To reset the server retention, set the value of max_sample_age to NULL.

set_server_db_exclude(server name, exclude_db name[])

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

set_server_connstr(server name, server_connstr text)

Sets the connection string for a server.

show_servers()

Displays the list of configured servers.

F.35.5. Managing Samples

A sample contains the database workload statistics since the previous sample

F.35.5.1. Sampling Functions

The following pgpro_pwr functions relate to sampling:

take_sample()
 take_sample(server name [, skip_sizes boolean])

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 be OK 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. Returns 0 on success.

Arguments:

  • server — server name.

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

take_sample_subset([sets_cnt integer, current_set integer])

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. Although PL/pgSQL does not support parallel execution, you can call this function in parallel sessions. This function returns the same type as take_sample(). If both parameters are omitted, the function behaves like the take_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 through sets_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([server name,] [days integer])

Returns a table with information on server samples (local server is assumed if server is omitted) for the last days 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 the pg_stat_database view if the statistics were reset since the previous sample.

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

  • archstats_reset — NULL or the statistics reset timestamp of the pg_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.

F.35.5.2. Taking Samples

To take samples for all enabled servers, call the take_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 returns OK 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)

F.35.5.3. Sample Retention Policy

You can define sample retention at the following levels:

  1. Global

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

  2. Server

    Specifying the max_sample_age parameter while creating a server or calling the set_server_max_sample_age(server,max_sample_age) function for an existing server defines the retention for the server. A server retention setting overrides pgpro_pwr.max_sample_age for a specific server.

  3. Baseline

    A baseline created overrides all the other retention periods for included samples.

F.35.6. Managing the Collection of Relation Sizes

It may take considerable time to collect sizes of all relations in a database by Postgres Pro relation-size functions. Besides, those functions require AccessExclusiveLock 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 the size-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(server name, window_start time with time zone DEFAULT NULL, window_duration interval hour to second DEFAULT NULL, sample_interval interval day to minute DEFAULT NULL)

Defines the size-collection policy for a server. All the arguments are required.

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.

Note

When you build a report between samples either of which lacks relation-size data, relation-growth sections will not be included in the report. However, you can expand the report interval bounds to the nearest samples with relation sizes collected using the with_growth parameter of report generation functions.

Relation sizes are needed to calculate sequentially scanned volume for tables and explicit vacuum load for indexes. When rarely collecting relation sizes, the data in the corresponding report sections is based on linear interpolation.

Example:

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

The show_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
------------+--------------+-------------+-----------------+-----------------
local       | 23:00:00+03  | 01:00:00+03 | 02:00:00        | 08:00:00

F.35.7. Managing Baselines

A baseline 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.

F.35.7.1. Baseline Management Functions

Use the following pgpro_pwr functions for baseline management:

create_baseline([server name,] baseline varchar(25), start_id integer, end_id integer [, days integer])
 create_baseline([server name,] baseline varchar(25), time_range tstzrange [, days integer])

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 covers time_range.

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

drop_baseline([server name,] baseline varchar(25))

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

keep_baseline([server name,] baseline varchar(25) [, days integer])

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

show_baselines([server name])

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

F.35.8. Data Export and Import

Collected samples can be exported from one instance of the pgpro_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.

F.35.8.1. Data Export

The export_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 of psql to obtain a single csv file: 

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

F.35.8.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 TABLE
postgres=# \copy import from 'export.csv'
COPY 6437

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

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

After successful import, you can drop the import table.

Note

If server data is imported for the first time, your local pgpro_pwr servers with matching names will cause a conflict during import. To avoid this, you can temporarily rename such servers. 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 that pgpro_pwr sets servers being imported to the disabled state for take_sample() to bypass them.

F.35.8.3. Export and Import Functions

Use these functions to export or import data:

export_data([server name, [min_sample_id integer,] [max_sample_id integer,]] [, obfuscate_queries boolean])

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). If min_sample_id is omitted or set to null, all samples until max_sample_id sample are exported; if max_sample_id is omitted or set to null, all samples since min_sample_id sample are exported.

  • obfuscate_queries — if true, query texts are exported as MD5 hash.

import_data(data regclass)

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

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

F.35.9.1. Regular Reports

Use this function to generate regular reports:

get_report([server name,] start_id integer, end_id integer [, description text [, with_growth boolean]])
 get_report([server name,] time_range tstzrange [, description text [, with_growth boolean]])
get_report([server name,] baseline varchar(25) [, description text [, with_growth boolean]])

Generates a regular report.

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 is false.

F.35.9.2. Differential Reports

Use this function to generate differential reports:

get_diffreport([server name,] start1_id integer, end1_id integer, start2_id integer, end2_id integer [, description text [, with_growth boolean]])
 get_diffreport([server name,] time_range1 tstzrange, time_range2 tstzrange [, description text [, with_growth boolean]])
 get_diffreport([server name,] baseline1 varchar(25), baseline2 varchar(25) [, description text [, with_growth boolean]])
get_diffreport([server name,] baseline1 varchar(25), time_range2 tstzrange [, description text [, with_growth boolean]])
get_diffreport([server name,] time_range1 tstzrange, baseline2 varchar(25) [, description text [, with_growth boolean]])
get_diffreport([server name,] start1_id integer, end1_id integer, baseline2 varchar(25) [, description text [, with_growth boolean]])
get_diffreport([server name,] baseline1 varchar(25), start2_id integer, end2_id integer [, description text [, with_growth boolean]])

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 is false.

F.35.9.3. Report Generation Example

Generate a report for the local 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

F.35.10. pgpro_pwr Report Sections

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

F.35.10.1. Server statistics

Tables in this section of a pgpro_pwr report are described below.

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

Table F.21. Database statistics

ColumnDescriptionField/Calculation
Database Database name datname
Commits Number of committed transactions xact_commit
Rollbacks Number of rolled back transactions xact_rollback
Deadlocks Number of deadlocks detected deadlocks
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 database blks_read
Hit Number of times disk blocks were found already in the buffer cache blks_hit
Ret Number of returned tuples tup_returned
Fet Number of fetched tuples tup_fetched
Ins Number of inserted tuples tup_inserted
Upd Number of updated tuples tup_updated
Del Number of deleted tuples tup_deleted
Temp Size Total amount of data written to temporary files by queries in this database temp_bytes
Temp Files Number of temporary files created by queries in this database temp_files
Size Database size at the time of the last sample in the report interval pg_database_size()
GrowthDatabase growth during the report intervalpg_database_size() increment between interval bounds

If the pgpro_stats extension was available during the report interval, the "Statement statistics by database" table of the report provides per-database aggregated total statistics for the pgpro_stats_statements view data. Table F.22 lists columns of this report table. Times are provided in seconds.

Table F.22. Statement statistics by database

ColumnDescriptionField/Calculation
Database Database name  
Calls Number of times all statements in the database were executed calls
Plan Time Time spent planning all statements in the database Sum of total_plan_time
Exec Time Time spent executing all statements in the database Sum of total_exec_time
Read Time Time spent reading blocks by all statements in the database Sum of blk_read_time
Write Time Time spent writing blocks by all statements in the database Sum of blk_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 of shared_blks_dirtied
Local Dirtied Total number of local blocks dirtied by all statements in the database Sum of local_blks_dirtied
Read Temp Total number of temp blocks read by all statements in the database Sum of temp_blks_read
Write Temp Total number of temp blocks written by all statements in the database Sum of temp_blks_written
Read Local Total number of local blocks read by all statements in the database Sum of local_blks_read
Write Local Total number of local blocks written by all statements in the database Sum of local_blks_written
Statements Total number of captured statements  
WAL Size Total amount of WAL generated by all statements in the database Sum of wal_bytes

The report table “Cluster statistics” provides data from the pg_stat_bgwriter view. Table F.23 lists rows of this report table. Times are provided in seconds.

Table F.23. Cluster statistics

RowDescriptionField/Calculation
Scheduled checkpoints Number of scheduled checkpoints that have been performed checkpoints_timed
Requested checkpoints Number of requested checkpoints that have been performed checkpoints_req
Checkpoint write time (s) Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk checkpoint_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 disk checkpoint_sync_time
Checkpoint buffers written Number of buffers written during checkpoints buffers_checkpoint
Background buffers written Number of buffers written by the background writer buffers_clean
Backend buffers written Number of buffers written directly by a backend buffers_backend
Backend fsync count Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write) buffers_backend_fsync
Bgwriter interrupts (too many buffers) Number of times the background writer stopped a cleaning scan because it had written too many buffers maxwritten_clean
Number of buffers allocated Total number of buffers allocated buffers_alloc
WAL generated Total amount of WAL generated pg_current_wal_lsn() value increment
WAL segments archived Total number of archived WAL segments Based on pg_stat_archiver.archived_count
WAL segments archive failed Total number of WAL segment archiver failures Based on pg_stat_archiver.failed_count

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

Table F.24. Tablespace statistics

ColumnDescriptionField/Calculation
Tablespace Tablespace name pg_tablespace.spcname
Path Tablespace path pg_tablespace_location()
Size Tablespace size at the time of the last sample in the report interval pg_tablespace_size()
Growth Tablespace growth during the report interval pg_tablespace_size() increment between interval bounds

If the pgpro_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 F.25 lists columns of this report table.

Table F.25. 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 of Wait event type, in seconds
%Total Percentage of wait time spent in the database events of Wait event type in all wait time for the cluster

If the pgpro_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 F.26 lists columns of this report table.

Table F.26. 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 in Wait event of the database, in seconds
%Total Percentage of wait time spent in Wait event of the database in all wait time in the cluster

F.35.10.2. SQL query statistics

This section of a pgpro_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 of pgpro_stats and pg_stat_statements extensions that was available during the report interval, with the precedence of pgpro_stats_statements. Tables of this section of the report are described below.

The report table “Top SQL by elapsed time” shows top statements by the sum of total_plan_time and total_exec_time fields of the pgpro_stats_statements or pg_stat_statements view. Table F.27 lists columns of this report table. Times are provided in seconds.

Table F.27. Top SQL by elapsed time

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
%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 plan total_plan_time + total_exec_time
Plan Time (s) Total time spent in planning of the statement total_plan_time
Exec Time (s) Total time spent in execution of the statement plan total_exec_time
Read I/O time (s) Total time the statement spent reading blocks blk_read_time
Write I/O time (s) Total time the statement spent writing blocks blk_write_time
Usr CPU time (s) Time spent on CPU in the user space rusage.user_time
Sys CPU time (s) Time spent on CPU in the system space rusage.system_time
Plans Number of times the statement was planned plans
Executions Number of executions of the statement plan calls

The report table “Top SQL by planning time” shows top statements by the value of the total_plan_time field of the pgpro_stats_statements or pg_stat_statements view. Table F.28 lists columns of this report table.

Table F.28. Top SQL by planning time

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Plan elapsed(s) Total time spent in planning of the statement, in seconds total_plan_time
%Elapsed Percentage of total_plan_time in the sum of total_plan_time and total_exec_time of this statement plan  
Mean plan time Mean time spent planning the statement, in milliseconds mean_plan_time
Min plan time Minimum time spent planning the statement, in milliseconds min_plan_time
Max plan time Maximum time spent planning the statement, in milliseconds max_plan_time
StdErr plan time Population standard deviation of time spent planning the statement, in milliseconds stddev_plan_time
Plans Number of times the statement was planned plans
Executions Number of executions of the statement plan calls

The report table “Top SQL by execution time” shows top statements by the value of the total_time field of the pgpro_stats_statements or pg_stat_statements view. Table F.29 lists columns of this report table.

Table F.29. Top SQL by execution time

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Exec (s) Total time spent executing the statement plan, in seconds total_exec_time
%Total Percentage of total_exec_time of this statement plan in the total elapsed time of all statements in the cluster  
Read I/O time (s) Total time spent in reading pages while executing the statement plan, in seconds blk_read_time
Write I/O time (s) Total time spent in writing pages while executing the statement plan, in seconds blk_write_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Mean execution time Mean time spent executing the statement plan, in milliseconds mean_exec_time
Min execution time Minimum time spent executing the statement plan, in milliseconds min_exec_time
Max execution time Maximum time spent executing the statement plan, in milliseconds max_exec_time
StdErr execution time Population standard deviation of time spent executing the statement plan, in milliseconds stddev_exec_time
Executions Number of executions of this statement plan calls

The report table “Top SQL by executions” shows top statements by the value of the calls field of the pgpro_stats_statements or pg_stat_statements view. Table F.30 lists columns of this report table.

Table F.30. Top SQL by executions

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Executions Number of executions of the statement plan calls
%Total Percentage of calls of this statement plan in the total calls of all statements in the cluster  
Rows Number of rows retrieved or affected by execution of the statement plan rows
Mean (ms) Mean time spent executing the statement plan, in milliseconds mean_exec_time
Min (ms) Minimum time spent executing the statement plan, in milliseconds min_exec_time
Max (ms) Maximum time spent executing the statement plan, in milliseconds max_exec_time
StdErr (ms) Population standard deviation of time spent executing the statement plan, in milliseconds stddev_time
Elapsed(s) Total time spent executing the statement plan, in seconds total_exec_time

The report table “Top SQL by I/O wait time” shows top statements by read and write time, i.e., sum of values of blk_read_time and blk_write_time fields of the pgpro_stats_statements or pg_stat_statements view. Table F.31 lists columns of this report table. Times are provided in seconds.

Table F.31. Top SQL by I/O wait time

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
IO(s) Total time spent in reading and writing while executing this statement plan, i.e., I/O time blk_read_time + blk_write_time
R(s) Total time spent in reading while executing this statement plan blk_read_time
W(s) Total time spent in writing while executing this statement plan blk_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 plan shared_blks_read
Loc Reads Total number of local blocks read while executing the statement plan local_blks_read
Tmp Reads Total number of temp blocks read while executing the statement plan temp_blks_read
Shr Writes Total number of shared blocks written while executing the statement plan shared_blks_written
Loc Writes Total number of local blocks written while executing the statement plan local_blks_written
Tmp Writes Total number of temp blocks written while executing the statement plan temp_blks_written
Elapsed(s) Total time spent in execution of the statement plan total_plan_time + total_exec_time
Executions Number of executions of the statement plan calls

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 F.32 lists columns of this report table.

Table F.32. Top SQL by shared blocks fetched

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Blks fetched Number of blocks retrieved while executing the statement plan shared_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 seconds total_plan_time + total_exec_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Executions Number of executions of the statement plan calls

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 F.33 lists columns of this report table.

Table F.33. Top SQL by shared blocks read

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Reads Number of shared blocks read while executing this statement plan shared_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 seconds total_plan_time + total_exec_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Executions Number of executions of the statement plan calls

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 F.34 lists columns of this report table.

Table F.34. Top SQL by shared blocks dirtied

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Dirtied Number of shared buffers dirtied while executing this statement plan shared_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  
Elapsed(s) Total time spent in execution of the statement plan, in seconds total_plan_time + total_exec_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Executions Number of executions of the statement plan calls

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

Table F.35. Top SQL by shared blocks written

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Written Number of blocks written while executing this statement plan shared_blks_written
%Total Percentage of blocks written by this statement plan in all written blocks in the cluster Percentage of shared_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 of shared_blks_written in pg_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 seconds total_plan_time + total_exec_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Executions Number of executions of the statement plan calls

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

Table F.36. Top SQL by WAL size

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
WAL Total amount of WAL bytes generated by the statement plan wal_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 plan shared_blks_dirtied
WAL FPI Total number of WAL full page images generated by the statement plan wal_fpi
WAL records Total number of WAL records generated by the statement plan wal_records

The report table “Top SQL by temp usage” shows top statements by temporary I/O, which is calculated as the sum of temp_blks_read, temp_blks_written, local_blks_read and local_blks_written fields. Table F.37 lists columns of this report table.

Table F.37. Top SQL by temp usage

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Local fetched Number of local blocks retrieved local_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 tables local_blks_written
Write Local %Total Percentage of local_blks_written of this statement plan in the total of local_blks_written for all statements in the cluster  
Read Local (blk) Number of blocks read by this statement plan that are used in temporary tables local_blks_read
Read Local %Total Percentage of local_blks_read of this statement plan in the total of local_blks_read for all statements in the cluster  
Write Temp (blk) Number of temp blocks written by this statement plan temp_blks_written
Write Temp %Total Percentage of temp_blks_written of this statement plan in the total of temp_blks_written for all statements in the cluster  
Read Temp (blk) Number of temp blocks read by this statement plan temp_blks_read
Read Temp %Total Percentage of temp_blks_read of this statement plan in the total of temp_blks_read for all statements in the cluster  
Elapsed(s) Total time spent in execution of the statement plan, in seconds total_plan_time + total_exec_time
Rows Number of rows retrieved or affected by execution of the statement plan rows
Executions Number of executions of the statement plan calls

F.35.10.2.1. rusage statistics

This section is included in the report only if the pg_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 of user_time and system_time fields of pg_stat_kcache. Table F.38 lists columns of this report table.

Table F.38. Top SQL by system and user time

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
User Time (s) User CPU time used user_time
Plan User (s) User CPU time elapsed during planning, in seconds plan_user_time
Exec User (s) User CPU time elapsed during execution, in seconds exec_user_time
User %Total Percentage of user_time in the total user CPU time for all statements  
System Time (s) System CPU time used system_time
Plan System (s) System CPU time elapsed during planning, in seconds plan_system_time
Exec System (s) System CPU time elapsed during execution, in seconds exec_system_time
System %Total Percentage of 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 of reads and writes fields of pg_stat_kcache. Table F.39 lists columns of this report table.

Table F.39. Top SQL by reads/writes done by filesystem layer

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Internal hash code, computed from the tree of the statement plan planid
Database Database name for the statement Derived from dbid
Read Bytes Number of bytes read by the filesystem layer reads
Plan Reads Bytes read during planning plan_reads
Exec Reads Bytes read during execution exec_reads
Read %Total Percentage of reads in the total number of bytes read by the filesystem layer for all statements  
Write Bytes Number of bytes written by the filesystem layer writes
Plan Writes Bytes written during planning plan_writes
Exec Writes Bytes written during execution exec_writes
Write %Total Percentage of writes in the total number of bytes written by the filesystem layer for all statements  

F.35.10.3. SQL query wait statistics

If the pgpro_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 certain wait 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 F.40 lists columns of this report table. Times are provided in seconds.

Table F.40. SQL query wait statistics

ColumnDescriptionField/Calculation
Query ID Query identifier, as a hash of database ID, user ID and query text. Hex representation of queryid is in square brackets.  
Plan ID Hash of the statement plan planid
Database Database name for the statement Derived from dbid
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  

F.35.10.4. 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 appropriate Query ID/Plan ID link in any statistics table to see the corresponding query/plan text. Table F.41 lists columns of this report table.

Table F.41. Complete list of SQL texts

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

F.35.10.5. Schema object statistics

Tables in this section of the report show top database objects by statistics from the Postgres Pro's Statistics Collector views.

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. The data is based on the pg_stat_all_tables view. Table F.42 lists columns of this report table.

Table F.42. 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 scans Sum of (pg_relation_size() * seq_scan)
SeqScan Number of sequential scans performed on the table seq_scan
IxScan Number of index scans initiated on the table idx_scan
IxFet Number of live rows fetched by index scans idx_tup_fetch
Ins Number of rows inserted n_tup_ins
Upd Number of rows updated n_tup_upd
Del Number of rows deleted n_tup_del
Upd(HOT) Number of rows HOT updated n_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 the pg_statio_all_tables view. Table F.43 lists columns of this report table.

Table F.43. 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 heap heap_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 indexes idx_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 table toast_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 index tidx_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 the pg_statio_all_tables view. Table F.44 lists columns of this report table.

Table F.44. 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 heap heap_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 indexes idx_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 table toast_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 index tidx_blks_read
TOAST-Ix Blks %Total Percentage of blocks read from the table's TOAST index in all blocks read in the cluster  

The report table “Top DML tables” shows top tables by the number of DML-affected rows, i.e., by the sum of n_tup_ins, n_tup_upd and n_tup_del (including TOAST tables). The data is based on the pg_stat_all_tables view. Table F.45 lists columns of this report table.

Table F.45. 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 inserted n_tup_ins
Upd Number of rows updated, including HOT n_tup_upd
Del Number of rows deleted n_tup_del
Upd(HOT) Number of rows HOT updated n_tup_hot_upd
SeqScan Number of sequential scans performed on the table seq_scan
SeqFet Number of live rows fetched by sequential scans seq_tup_read
IxScan Number of index scans initiated on this table idx_scan
IxFet Number of live rows fetched by index scans idx_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 of n_tup_upd and n_tup_del (including TOAST tables). The data is based on the pg_stat_all_tables view. Table F.46 lists columns of this report table.

Table F.46. 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 HOT n_tup_upd
Upd(HOT) Number of rows HOT updated n_tup_hot_upd
Del Number of rows deleted n_tup_del
Vacuum Number of times this table has been manually vacuumed (not counting VACUUM FULL) vacuum_count
AutoVacuum Number of times this table has been vacuumed by the autovacuum daemon autovacuum_count
Analyze Number of times this table was manually analyzed analyze_count
AutoAnalyze Number of times this table was analyzed by the autovacuum daemon autoanalyze_count

The report table “Top growing tables” shows top tables by growth. The data is based on the pg_stat_all_tables view. Table F.47 lists columns of this report table.

Table F.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 interval pg_table_size() - pg_relation_size(toast)
Growth Table growth  
Ins Number of rows inserted n_tup_ins
Upd Number of rows updated, including HOT n_tup_upd
Del Number of rows deleted n_tup_del
Upd(HOT) Number of rows HOT updated n_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 the pg_statio_all_indexes view. Table F.48 lists columns of this report table.

Table F.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 index idx_scan
Blks Number of blocks fetched for this index idx_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 the pg_statio_all_indexes and pg_stat_all_indexes views. Table F.49 lists columns of this report table.

Table F.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 index idx_scan
Blk Reads Number of disk blocks read from this index idx_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 the pg_stat_all_tables and pg_stat_all_indexes views. Table F.50 lists columns of this report table.

Table F.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 interval pg_relation_size()
Index Growth Index growth during the report interval  
Table Ins Number of rows inserted into the underlying table n_tup_ins
Table Upd Number of rows updated in the underlying table, without HOT n_tup_upd - n_tup_hot_upd
Table Del Number of rows deleted from the underlying table n_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 the pg_stat_all_tables view. Table F.51 lists columns of this report table.

Table F.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 interval pg_relation_size()
Index Growth Index growth during the report interval  
Table Ins Number of rows inserted into the underlying table n_tup_ins
Table Upd Number of rows updated in the underlying table, without HOT n_tup_upd - n_tup_hot_upd
Table Del Number of rows deleted from the underlying table n_tup_del

F.35.10.6. User function statistics

Tables in this section of the report show top functions in the cluster by statistics from the pg_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 F.52 lists columns of these report tables.

Table F.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 called calls
Total Time (s) Total time spent in this function and all other functions called by it total_time
Self Time (s) Total time spent in this function itself, not including other functions called by it self_time
Mean Time (s) Mean time of a single function execution total_time/calls
Mean self Time (s) Mean self time of a single function execution self_time/calls

F.35.10.8. Cluster settings during the report interval

This section of the report contains a table with Postgres Pro GUC parameters, values of functions version(), pg_postmaster_start_time(), pg_conf_load_time() and the system_identifier field of the pg_control_system() function during the report interval. The data in the table is grouped under Defined settings and Default settings. Table F.58 lists columns of this report table.

Table F.58. Cluster settings during the report interval

ColumnDescription
Setting Name of the parameter
reset_valreset_val field of the pg_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

F.35.11. pgpro_pwr Diagnostic Tools

pgpro_pwr provides self-diagnostic tools.

F.35.11.1. Collecting Detailed Timing Statistics for Sampling Procedures

pgpro_pwr collects detailed timing statistics of taking samples when the pgpro_pwr.track_sample_timings parameter is on. You can get the results from the v_sample_timings view. Table F.59 lists columns of this view.

Table F.59. 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. See Table F.60 for descriptions of sampling stages.
time_spent Time spent in the event

Table F.60. sampling_event Description

EventDescription
total Taking the sample (all stages)
connect Making dblink connection to the server
get server environment Getting server GUC parameters, available extensions, etc.
collect database stats Querying the pg_stat_database view for statistics on databases
calculate database stats Calculating differential statistics on databases since the previous sample
collect tablespace stats Querying the pg_tablespace view for statistics on tablespaces
collect statement stats Collecting statistics on statements using the pgpro_stats and pg_stat_kcache extensions
query pg_stat_bgwriter Collecting cluster statistics using the pg_stat_bgwriter view
query pg_stat_archiver Collecting cluster statistics using the pg_stat_archiver view
collect object stats Collecting statistics on database objects. Includes events from Table F.61.
maintain repository Executing support routines
calculate tablespace stats Calculating differential statistics on tablespaces
calculate object stats Calculating differential statistics on database objects. Includes events from Table F.62.
calculate cluster stats Calculating cluster differential statistics
calculate archiver stats Calculating archiver differential statistics
delete obsolete samples Deleting obsolete baselines and samples

Table F.61. Events of Collecting Statistics on Database Objects

EventDescription
db:dbname collect tables stats Collecting statistics on tables for the dbname database
db:dbname collect indexes stats Collecting statistics on indexes for the dbname database
db:dbname collect functions stats Collecting statistics on functions for the dbname database

Table F.62. 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

F.35.12. Important Notes

When using the pgpro_pwr extension, be aware of the following:

  • Postgres Pro collects execution statistics after 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 any Postgres Pro statistics may affect the accuracy of the next sample.

  • Exclusive locks on relations conflict with calculation of the relation size. If the take_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
F.34. pg_pathman Home F.36. pg_prewarm