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.

The columns of the pg_stat_statements view are described in the following table.

Column	Type	Description
userid	oid	OID of user who executed the statement
dbid	oid	OID of database in which the statement was executed
queryid	bigint	Internal hash code, computed from the statement's parse tree
query	text	Text of a representative statement
calls	bigint	Number of times executed
total_time	double precision	Total time spent in the statement, in milliseconds
min_time	double precision	Minimum time spent in the statement, in milliseconds
max_time	double precision	Maximum time spent in the statement, in milliseconds
mean_time	double precision	Mean time spent in the statement, in milliseconds
stddev_time	double precision	Population standard deviation of time spent in 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 blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_time	double precision	Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

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

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

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 |           |          |

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

Refer to View live queries with pg_stat_activity to analyze live queries.
Refer to View COPY progress with pg_stat_progress_copy to track the COPY operation status.
Refer to Analyze queries with EXPLAIN to optimize YSQL's EXPLAIN and EXPLAIN ANALYZE queries.
Refer to Optimize YSQL queries using pg_hint_plan show the query execution plan generated by YSQL.