Use pg_stat_statements

Eugenia Kuzina

Overview

The pg_stat_statements extension allows you to track planning and execution statistics of SQL queries. It collects statistics across all databases. To access statistics, the extension includes views and functions.

The package required for the installation of the pg_stat_statements extension is shipped with ADP, and the shared_preload_libraries parameter of the ADP configuration file already contains the pg_stat_statements value. You only need to execute the CREATE EXTENSION command to enable pg_stat_statements for the current database:

CREATE EXTENSION pg_stat_statements;
NOTE
If the pg_stat_statements extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 1.10 pg_stat_statements version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'pg_stat_statements';
 extversion
------------
1.10

Views

The extension includes two views:

pg_stat_statements

The collected statistics are available through the pg_stat_statements view. It contains one row for each distinct combination of database ID, user ID, query ID, and a value that determines whether the statement is a top-level statement (a statement that is executed directly by a client).

Use the pg_stat_statements.max configuration parameter to set the maximum number of statements tracked by pg_stat_statements and, consequently, the maximum number of rows in the pg_stat_statements view.

pg_stat_statements view fields
Name Type Description

userid

oid

OID of the user who executed the statement. It references the oid field of the pg_authid system catalog

dbid

oid

OID of the database in which the statement was executed. It references the oid field of the pg_database system catalog

toplevel

bool

Determines whether the query was executed as a top-level statement. It is always true if pg_stat_statements.track is set to top

queryid

bigint

Hash code to identify identical normalized queries

query

text

Text of a statement

plans

bigint

Number of times the statement was planned (if the pg_stat_statements.track_planning configuration parameter is enabled, otherwise 0)

total_plan_time

double precision

Total time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise 0)

min_plan_time

double precision

Minimum time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise 0)

max_plan_time

double precision

Maximum time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise 0)

mean_plan_time

double precision

Mean time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise 0)

stddev_plan_time

double precision

Population standard deviation of time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise 0)

calls

bigint

Number of times the statement was executed

total_exec_time

double precision

Total time spent executing the statement, in milliseconds

min_exec_time

double precision

Minimum time spent executing the statement, in milliseconds

max_exec_time

double precision

Maximum time spent executing the statement, in milliseconds

mean_exec_time

double precision

Mean time spent executing the statement, in milliseconds

stddev_exec_time

double precision

Population standard deviation of time spent executing the statement, in milliseconds

rows

bigint

Total number of rows retrieved or affected by the statement

shared_blks_hit

bigint

Total number of shared block cache hits by the statement

shared_blks_read

bigint

Total number of shared blocks read by the statement

shared_blks_dirtied

bigint

Total number of shared blocks dirtied by the statement

shared_blks_written

bigint

Total number of shared blocks written by the statement

local_blks_hit

bigint

Total number of local block cache hits by the statement

local_blks_read

bigint

Total number of local blocks read by the statement

local_blks_dirtied

bigint

Total number of local blocks dirtied by the statement

local_blks_written

bigint

Total number of local blocks written by the statement

temp_blks_read

bigint

Total number of temp blocks read by the statement

temp_blks_written

bigint

Total number of temp blocks written by the statement

blk_read_time

double precision

Total time the statement spent reading data file blocks, in milliseconds (if track_io_timing is enabled, otherwise 0)

blk_write_time

double precision

Total time the statement spent writing data file blocks, in milliseconds (if track_io_timing is enabled, otherwise 0)

temp_blk_read_time

double precision

Total time the statement spent reading temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise 0)

temp_blk_write_time

double precision

Total time the statement spent writing temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise 0)

wal_records

bigint

Total number of WAL records generated by the statement

wal_fpi

bigint

Total number of WAL full page images generated by the statement

wal_bytes

numeric

Total amount of WAL generated by the statement, in bytes

jit_functions

bigint

Total number of functions JIT-compiled by the statement

jit_generation_time

double precision

Total time spent by the statement on generating JIT code, in milliseconds

jit_inlining_count

bigint

Number of times functions have been inlined

jit_inlining_time

double precision

Total time spent by the statement on inlining functions, in milliseconds

jit_optimization_count

bigint

Number of times the statement has been optimized

jit_optimization_time

double precision

Total time spent by the statement on optimizing, in milliseconds

jit_emission_count

bigint

Number of times code has been emitted

jit_emission_time

double precision

Total time spent by the statement on emitting code, in milliseconds

The pg_stat_statements view provides information about the longest-running queries (the total_exec_time column), the queries that are executed most frequently (the calls column), and the number of rows they return (the rows column).

The stddev_exec_time column contains the standard deviation of execution times. If this deviation is large, some queries will be fast and others will be slow, which can lead to a poor user experience.

The ratio of the shared_blks_read (number of physical reads) and shared_blks_hit (number of cache hits) columns allows you to evaluate the performance of caching.

The blk_read_time and blk_write_time columns show how much time the query spends on I/O operations and indicate whether the system has problems related to these operations.

For security reasons, only superusers and users with the pg_read_all_stats role are allowed to see the SQL text and queryid of queries executed by other users. However, other users can see statistics if pg_stat_statements is installed in their database.

NOTE
ADP has the compute_query_id parameter set to auto, which allows pg_stat_statements to automatically enable in-core computation of a query identifier. If compute_query_id is disabled, the details described below may be incorrect.

Plannable queries (SELECT, INSERT, UPDATE, DELETE, and MERGE) and utility commands can be combined into a single pg_stat_statements entry whenever they have identical query structures according to an internal hash calculation. Two queries will be considered the same if they are semantically equivalent except for the values of constants appearing in the query.

In some cases, apparently distinct queries may be combined into a single pg_stat_statements entry. Normally this will happen only for semantically equivalent queries, but there is a small chance of hash collisions causing unrelated queries to be merged into one entry. This is not possible for queries that belong to different users or databases.

Since the queryid hash value is calculated based on the query representation after it is analyzed and parsed, the opposite situation is also possible: queries with identical text may appear as separate entries if they have a different representation for various reasons, for example, because of changes in search_path.

The query text is stored in an external file on the disk and does not consume shared memory. Therefore, even very large query texts can be saved successfully. However, if the file accumulates many long query texts, it can become unmanageably large. To resolve this problem, pg_stat_statements may discard the query texts. As a result, all existing records in the pg_stat_statements view will have NULL values in the query field, although the statistics associated with each queryid will be preserved. It may be worth reducing the pg_stat_statements.max configuration parameter to prevent such behavior.

pg_stat_statements_info

The pg_stat_statements_info view contains statistics of the pg_stat_statements extension. This view contains only a single row with two fields:

  • dealloc (bigint) — the total number of times pg_stat_statements discarded records of rarely executed statements because more distinct statements were received for processing than specified in the pg_stat_statements.max configuration parameter;

  • stats_reset (timestamp with time zone) — time at which all statistics in the pg_stat_statements view were reset.

Functions

pg_stat_statements_reset

The pg_stat_statements_reset function discards statistics collected up to this time by the pg_stat_statements extension for the given user (<userid>), database (<dbid>), and query (<queryid>). It has the following syntax:

pg_stat_statements_reset(<userid> Oid, <dbid> Oid, <queryid> bigint) returns void

If any of the parameters is not specified, the default value 0 will be used for it, and the statistics that match other parameters will be reset. If no parameter is specified or all the specified parameters are 0, it will discard all statistics.

By default, only superusers can execute pg_stat_statements_reset. If you need to grant access to other users, use the GRANT command.

The following code discards statistics completely:

SELECT pg_stat_statements_reset();

pg_stat_statements

The pg_stat_statements function returns rows of the pg_stat_statements view. It has the following syntax:

pg_stat_statements(<showtext> boolean) returns setof record

The <showtext> parameter allows you to omit the query text (the query column of the pg_stat_statements view). To do this, pass false as the <showtext> value. This feature is intended to support external tools that need to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Since the server stores query texts in a file, this approach can reduce physical I/O that occurs when constantly accessing pg_stat_statements data.

The following code displays one row from the pg_stat_statements view without the query text:

SELECT pg_stat_statements(false) LIMIT 1;
 pg_stat_statements
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (10,5,t,6910407680078390346,,0,0,0,0,0,0,2,0.043220999999999996,0.020992,0.022229,0.021610499999999998,0.0006184999999999984,12,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

Configuration parameters

The configuration parameters of the pg_stat_statements extension are listed in the table below.

Name Type Description

pg_stat_statements.max

integer

Maximum number of statements tracked by the extension (the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. You can find the number of times such information was discarded in the pg_stat_statements_info view. The default value is 5000. Changing the parameter requires a server restart

pg_stat_statements.track

enum

Specifies which statements are tracked by the extension. Possible values:

  • top — track top-level statements (issued directly by clients);

  • all — track top-level and nested statements (invoked within functions);

  • none — disables the collection of statistics on statements.

The default value is top. Only superusers can change this parameter

pg_stat_statements.track_utility

boolean

Controls whether utility commands are tracked. Utility commands are commands other than SELECT, INSERT, UPDATE, DELETE, and MERGE. The default value is on. Only superusers can change this parameter

pg_stat_statements.track_planning

boolean

Controls whether planning operations and duration are tracked by the extension. Enabling this parameter may incur a noticeable decrease in performance, especially when multiple concurrent sessions execute statements with identical query structure resulting in attempts to simultaneously modify the same records in pg_stat_statements. The default value is off. Only superusers can change this parameter

pg_stat_statements.save

boolean

Specifies whether to save statement statistics across server shutdowns. If the value is off, statistics are not saved at shutdown. The default value is on

The module requires additional shared memory proportional to the pg_stat_statements.max value. Note that this memory is consumed even if pg_stat_statements.track is set to none.

You can specify configuration parameters listed above in the postgresql.conf field (see Configuration parameters). For example, you can add the following rows at the end of the postgresql.conf field:

compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Examples

To test the pg_stat_statements extension, use the pgbench tool.

Create a new database and install the pg_stat_statements extension:

CREATE DATABASE test_db;

CREATE EXTENSION pg_stat_statements;

Run pgbench tests:

$ pgbench -i test_db;

$ pgbench -c10 -t300 test_db;

Get information about the queries with the longest execution times

Display 10 queries with the longest execution time:

SELECT queryid, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
  queryid             | calls |  total_exec_time
----------------------+-------+--------------------
  7383078919277633246 |  3000 |  20018.59277099997
  4073535880199856896 |  3000 | 16169.558812999983
  8862263855866986518 |    31 | 382.61634699999996
  3489562727129942967 |  2619 |  343.3107100000004
  8862263855866986518 |    31 | 174.61769699999996
  2951664667527397125 |    31 |         156.447891
  2951664667527397125 |    31 | 152.60754100000003
 -1864079628023708478 |     1 |         146.880246
   -38828163342769630 |    31 |         134.026149
  1331898319668487618 |  3000 |  115.9758540000001

Display the longest query text by passing the queryid value as a parameter:

SELECT query FROM pg_stat_statements WHERE queryid = 7383078919277633246;
                              query
---------------------------------------------------------------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2

Determine which user executed this query:

SELECT rolname FROM pg_authid
    WHERE oid = (SELECT userid FROM pg_stat_statements WHERE queryid = 7383078919277633246);
 rolname
----------
 postgres

Clear statistics

The following command deletes statistics for the longest query with queryid equal to 7383078919277633246:

SELECT pg_stat_statements_reset(0,0,7383078919277633246);

It is also possible to use query text to identify the query:

SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
    WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';

Check the result:

SELECT queryid, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
       queryid       | calls |  total_exec_time
---------------------+-------+--------------------
 4073535880199856896 |  3000 | 16169.558812999983
 8862263855866986518 |   415 |        5331.443348
 3489562727129942967 | 36053 |  4715.664210999961
 8862263855866986518 |   415 | 2367.5161030000013
 2951664667527397125 |   415 |        2133.705913
 2951664667527397125 |   415 |        2084.859443
  -38828163342769630 |   415 | 1821.4699970000001
 -848463905646043204 |   415 |        1342.723496
 5887070591661635964 |   415 |  1320.351474000002
 5309506800927155835 |   415 |  889.2803399999999

Statistics for the query with queryid equal to 7383078919277633246 are not displayed.

Estimate caching performance

Calculate the ratio of physical reads (shared_blks_read) to cache hits (shared_blks_hit). A cache hit rate close to 100% indicates effective caching:

SELECT
    queryid,
    round(total_exec_time::numeric, 0) AS total_time,
    round(mean_exec_time::numeric, 0) AS avg_time,
    shared_blks_hit,
    shared_blks_read,
    CASE
        WHEN (shared_blks_hit + shared_blks_read) = 0 THEN 0
        ELSE round((shared_blks_hit * 100.0 / (shared_blks_hit + shared_blks_read)), 0)
    END AS percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
       queryid       | total_time | avg_time | shared_blks_hit | shared_blks_read | percent
---------------------+------------+----------+-----------------+------------------+---------
 7383078919277633246 |      20019 |        7 |           60408 |                1 |     100
 4073535880199856896 |      16170 |        5 |           35252 |                1 |     100
 8862263855866986518 |       5127 |       13 |         4617883 |                0 |     100
 3489562727129942967 |       4548 |        0 |           69516 |                0 |     100
 8862263855866986518 |       2279 |        6 |         1023600 |                0 |     100
 2951664667527397125 |       2053 |        5 |          654800 |                0 |     100
 2951664667527397125 |       2006 |        5 |          682000 |                0 |     100
  -38828163342769630 |       1753 |        4 |          602800 |                0 |     100
 -848463905646043204 |       1291 |        3 |            3999 |                0 |     100
 5887070591661635964 |       1268 |        3 |            3999 |                0 |     100
Found a mistake? Seleсt text and press Ctrl+Enter to report it