Get query statistics using pg_stat_statements

Databases can be resource-intensive, consuming a lot of memory CPU, IO, and network resources. Optimizing your SQL can be very helpful in minimizing resource utilization. The pg_stat_statements module helps you track planning and execution statistics for all the SQL statements executed by a server. It is installed by default.

Configuration parameters

You can configure the following parameters in postgresql.conf:

Column Type Default Description
pg_stat_statements.max integer 5000 Maximum number of statements tracked by the module.
pg_stat_statements.track enum top Controls which statements the module tracks. Valid values are top (track statements issued directly by clients), all (track top-level and nested statements), and none (disable statement statistics collection).
pg_stat_statements.track_utility boolean on Controls whether the module tracks utility commands.
pg_stat_statements.save boolean on Specifies whether to save statement statistics across server shutdowns.

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

pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

To track IO elapsed time, turn on the track_io_timing parameter in postgresql.conf:

track_io_timing = on

The track_activity_query_size parameter sets the number of characters to display when reporting a SQL query. Raise this value if you're not seeing longer queries in their entirety. For example:

track_activity_query_size = 2048

The extension is created by default. To add or remove it manually, use the following statements:

yugabyte=# create extension pg_stat_statements;
yugabyte=# drop extension pg_stat_statements;

Examples

Setup

Local single-node cluster or use the Sandbox cluster in YugabyteDB Managed. See Set up your YugabyteDB cluster.

Describe the columns in the view:

yugabyte=# \d pg_stat_statements;
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

Describe the view's columns, including storage type and description:

yugabyte=# \dS+ pg_stat_statements;
                                View "public.pg_stat_statements"
      Column        |       Type       | Collation | Nullable | Default | Storage  | Description
---------------------+------------------+-----------+----------+---------+----------+-------------
userid              | oid              |           |          |         | plain    |
dbid                | oid              |           |          |         | plain    |
queryid             | bigint           |           |          |         | plain    |
query               | text             |           |          |         | extended |
calls               | bigint           |           |          |         | plain    |
total_time          | double precision |           |          |         | plain    |
min_time            | double precision |           |          |         | plain    |
max_time            | double precision |           |          |         | plain    |
mean_time           | double precision |           |          |         | plain    |
stddev_time         | double precision |           |          |         | plain    |
rows                | bigint           |           |          |         | plain    |
shared_blks_hit     | bigint           |           |          |         | plain    |
shared_blks_read    | bigint           |           |          |         | plain    |
shared_blks_dirtied | bigint           |           |          |         | plain    |
shared_blks_written | bigint           |           |          |         | plain    |
local_blks_hit      | bigint           |           |          |         | plain    |
local_blks_read     | bigint           |           |          |         | plain    |
local_blks_dirtied  | bigint           |           |          |         | plain    |
local_blks_written  | bigint           |           |          |         | plain    |
temp_blks_read      | bigint           |           |          |         | plain    |
temp_blks_written   | bigint           |           |          |         | plain    |
blk_read_time       | double precision |           |          |         | plain    |
blk_write_time      | double precision |           |          |         | plain    |
View definition:
SELECT pg_stat_statements.userid,
   pg_stat_statements.dbid,
   pg_stat_statements.queryid,
   pg_stat_statements.query,
   pg_stat_statements.calls,
   pg_stat_statements.total_time,
   pg_stat_statements.min_time,
   pg_stat_statements.max_time,
   pg_stat_statements.mean_time,
   pg_stat_statements.stddev_time,
   pg_stat_statements.rows,
   pg_stat_statements.shared_blks_hit,
   pg_stat_statements.shared_blks_read,
   pg_stat_statements.shared_blks_dirtied,
   pg_stat_statements.shared_blks_written,
   pg_stat_statements.local_blks_hit,
   pg_stat_statements.local_blks_read,
   pg_stat_statements.local_blks_dirtied,
   pg_stat_statements.local_blks_written,
   pg_stat_statements.temp_blks_read,
   pg_stat_statements.temp_blks_written,
   pg_stat_statements.blk_read_time,
   pg_stat_statements.blk_write_time
  FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);

Top 10 I/O-intensive queries

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by (blk_read_time+blk_write_time)/calls desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
(3 rows)
yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by (blk_read_time+blk_write_time) desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
(4 rows)

Top 10 time-consuming queries

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by mean_time desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
(4 rows)
yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by total_time desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit $1
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
(5 rows)

Top 10 response-time outliers

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by stddev_time desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit $1
(5 rows)

Top 10 queries by memory usage

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by (shared_blks_hit+shared_blks_dirtied) desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit $1
(6 rows)

Top 10 consumers of temporary space

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by temp_blks_written desc
    limit 10;
  userid  | dbid  |                                                          query
----------+-------+--------------------------------------------------------------------------------------------------------
 yugabyte | 12463 | select pg_stat_statements_reset()
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/cal
ls desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) des
c limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit $1
 yugabyte | 12463 | select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirti
ed) desc limit $1
(7 rows)

Reset statistics

pg_stat_statements_reset discards all statistics gathered so far by pg_stat_statements. By default, this function can only be executed by superusers.

yugabyte=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)

Learn more