Get query statistics using pg_stat_statements - Yugabyte Db

{{<tabs>}} {{<tabitem href="../pg-stat-statements/" text="YSQL" icon="postgres" active="true">}} {{<tabitem href="../ycql-stat-statements/" text="YCQL" icon="cassandra" >}} {{</tabs>}}

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.
plans	bigint	Number of times the statement was planned.
total_plan_time	double precision	Total time spent planning the statement, in milliseconds.
min_plan_time	double precision	Minimum time spent planning the statement, in milliseconds.
max_plan_time	double precision	Maximum time spent planning the statement, in milliseconds.
mean_plan_time	double precision	Mean time spent planning the statement, in milliseconds.
stddev_plan_time	double precision	Population standard deviation of time spent planning the statement, in milliseconds.
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	Not populated in YugabyteDB.
shared_blks_read	bigint	Not populated in YugabyteDB.
shared_blks_dirtied	bigint	Not populated in YugabyteDB.
shared_blks_written	bigint	Not populated in YugabyteDB.
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	Not populated in YugabyteDB.
blk_write_time	double precision	Not populated in YugabyteDB.
yb_latency_histogram	jsonb	List of key value pairs where key is the latency range and value is the count of times a query was executed.
docdb_read_rpcs	bigint	Number of roundtrip requests made to the local YB-TServer that include only read operations.
docdb_write_rpcs	bigint	Number of roundtrip requests issued to the local YB-TServer that include at least 1 write operation.
catalog_wait_time	bigint	Wall clock wait time (in ms) for catalog requests, including the network latency.
docdb_read_operations	bigint	Total number of read operations sent as part of RPC requests.
docdb_write_operations	bigint	Total number of write operations sent as part of RPC requests.
docdb_wait_time	double precision	Wall clock wait time (in ms) for storage requests in the YSQL layer, including the network latency.
docdb_rows_scanned	bigint	Rows scanned by DocDB.
docdb_rows_returned	bigint	Rows returned by DocDB layer to the YSQL layer.
conflict_retries	bigint	Number of internal query retries caused by transaction conflicts between overlapping transactions.
read_restart_retries	bigint	Number of internal query retries for reads (possibly because of a concurrent update).
total_retries	bigint	Total number of query retries of any type.

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.
`pg_stat_statements.track_planning`	boolean	off	Controls whether planning operations and duration are tracked.
`pg_stat_statements.track`	enum	top	Controls which statements are counted. 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.
`pg_stat_statements.yb_hdr_bucket_factor`	integer	16	Changes the number of latency range buckets.
`yb_enable_pg_stat_statements_rpc_stats`	boolean	false	Enables RPC execution time statistics for pg_stat_statements. When enabled, populates the DocDB-related columns `docdb_wait_time` and `catalog_wait_time`. This is a runtime flag that can be changed without restarting the server.

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
yb_enable_pg_stat_statements_rpc_stats = true

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:

sql

yugabyte=# CREATE EXTENSION pg_stat_statements;

sql

yugabyte=# DROP EXTENSION pg_stat_statements;

yb_latency_histogram column

Each row in the pg_stat_statements view in YugabyteDB includes the yb_latency_histogram column, of type JSONB.

yb_latency_histogram consists of a list of key value pairs where key is the latency range and value is the count of times a query was executed. After each query execution, based on the execution time of the query, the execution counter for a specific key (latency range) for that query is incremented. Each key field represents time span in milliseconds (ms). For the resolution of each latency range, refer to Latency range buckets.

The value associated with each key is the count of that query's execution latencies falling in that time span. The time spans are beginning-inclusive and end-exclusive. For example, {"[12.8, 25.6)": 4} means the query has been recorded as taking between 12.8 ms and 25.6 ms, 4 times.

Queries taking longer than the max query duration (default 1677721.5 ms or ~28 min) are recorded in the overflow bucket. The overflow bucket has the format {"[max_latency,)": n}, indicating that it covers all values beyond the scope of the histogram. Currently the max_latency tracked is 1677721.6 ms.

To view latency histograms, use the following command:

sql

yugabyte=# SELECT query, calls, yb_latency_histogram FROM pg_stat_statements;

output

-[ RECORD 1 ]-----------------------------------------------------------------
query                | insert into table1 values($1, $2)
calls                | 6
yb_latency_histogram | [{"[0.1,0.2)": 4}, {"[0.2,0.3)": 2}]
-[ RECORD 2 ]-----------------------------------------------------------------
query                | select pg_sleep($1)
calls                | 9
yb_latency_histogram | [{"[0.8,0.9)": 6}, {"[1677721.6,)": 3}]

In this example, Record 1 shows that the insert query took between 0.1 to 0.2 ms 4 times and 0.2 to 0.3 ms 2 times. Record 2 shows that the pg_sleep query took between 0.8 to 0.9 ms 6 times and 3 times it took more than 1677721.6 ms.

Latency range buckets

The following table shows the time resolution for each latency range. For example, for queries finishing between 25.6-51.2 the resolution of the bucket is 3.2ms. If the query finishes in 28 ms then it is recorded in the sub-bucket {25.6, 28.8).

Time Span (ms)	Resolution (ms)
0-1.6	0.1
1.6-3.2	0.2
3.2-6.4	0.4
6.4-12.8	0.8
12.8-25.6	1.6
25.6-51.2	3.2
51.2-102.4	6.4
102.4-204.8	12.8
204.8-409.6	25.6
409.6-819.2	51.2
819.2-1638.4	102.4
1638.4-3276.8	204.8
3276.8-6553.6	409.6
6553.6-13107.2	819.2
13107.2-26214.4	1638.4
26214.4-52428.8	3276.8
52428.8-104857.6	6553.6
104857.6-209715.2 (104.8-209.7 s)	13107.2 (~13s)
209715.2-419430.4 (3.49-6.99 min)	26214.4 (~26s)
419430.4-838860.8 (6.99-13.98 min)	52428.8 (~52s)
838860.8-1677721.6 (13.98-27.96 min)	104857.6 (~104s)

You can change the number of buckets using the yb_hdr_bucket_factor parameter in the postgresql.conf file. Valid values are 8, 16 (default), or 32. For example:

pg_stat_statements.yb_hdr_bucket_factor = 32

This changes the buckets as shown in the following table.

Time Span (ms)	Resolution (ms)
0-3.2	0.1
3.2-6.4	0.2
6.4-12.8	0.4
12.8-25.6	0.8
25.6-51.2	1.6
51.2-102.4	3.2
102.4-204.8	6.4
204.8-409.6	12.8
409.6-819.2	25.6
819.2-1638.4	51.2
...
209715.2-419430.4 (3.49-6.99 min)	13107.2
419430.4-838860.8 (6.99-13.98 min)	26214.4
838860.8-1677721.6 (13.98-27.96 min)	52428.8

Show latency percentiles

Use the yb_get_percentile function to show percentiles for a given histogram. This function takes a JSONB histogram (including yb_latency_histogram) and a target percentile (double precision) as input.

sql

\df yb_get_percentile

output

   Schema   |       Name        | Result data type |           Argument data types           | Type
----------- +-------------------+------------------+-----------------------------------------+------
 pg_catalog | yb_get_percentile | double precision | hist jsonb, percentile double precision | func
(1 row)

The following examples show how to display various percentile latencies for a given JSONB histogram.

sql

SELECT yb_get_percentile('[{"[0.1,0.2)": 4}, {"[0.2,0.3)": 1}, {"[0.8,0.9)": 1}, {"[1.1,1.2)": 3}, {"[1.5,1.6)": 1}, {"[2.0,2.2)": 4}, {"[3.2,3.6)": 1}]', 99);

output

 yb_get_percentile
-------------------
               3.6
(1 row)

sql

SELECT yb_get_percentile('[{"[0.1,0.2)": 4}, {"[0.2,0.3)": 1}, {"[0.8,0.9)": 1}, {"[1.1,1.2)": 3}, {"[1.5,1.6)": 1}, {"[2.0,2.2)": 4}, {"[3.2,3.6)": 1}]', 90);

output

 yb_get_percentile
-------------------
               2.2
(1 row)

sql

SELECT yb_get_percentile('[{"[0.1,0.2)": 4}, {"[0.2,0.3)": 1}, {"[0.8,0.9)": 1}, {"[1.1,1.2)": 3}, {"[1.5,1.6)": 1}, {"[2.0,2.2)": 4}, {"[3.2,3.6)": 1}]', 50);

output

 yb_get_percentile
-------------------
               1.2
(1 row)

The following example displays the P99, P95, and P90 latency by augmenting the pg_stat_statements output for a specific query:

sql

SELECT query, calls, total_time, min_time, max_time, mean_time, rows,
  yb_latency_histogram,
  yb_get_percentile(yb_latency_histogram, 99),
  yb_get_percentile(yb_latency_histogram, 95),
  yb_get_percentile(yb_latency_histogram, 90)
  FROM pg_stat_statements WHERE query like '%select v from foo where customer_id%';

output

query                | select v from foo where customer_id=$1 and day=$2::date for update
calls                | 33904964
total_time           | 1122539431.94119
min_time             | 1.005537
max_time             | 524.190576
mean_time            | 33.1084094925296
rows                 | 33904964
yb_latency_histogram | [{"[1.0,1.1)": 7658}, {"[1.1,1.2)": 326051}, {"[1.2,1.3)": 984139}, {"[1.3,1.4)": 862572}, {"[1.4,1.5)": 604798}, {"[1.5,1.6)": 662095}, {"[1.6,1.8)": 1258824}, {"[1.8,2.0)": 951168}, {"[2.0,2.2)": 373974}, {"[2.2,2.4)": 107085}, {"[2.4,2.6)": 64638}, {"[2.6,2.8)": 73508}, {"[2.8,3.0)": 81727}, {"[3.0,3.2)": 92673}, {"[3.2,3.6)": 199462}, {"[3.6,4.0)": 216870}, {"[4.0,4.4)": 188390}, {"[4.4,4.8)": 212072}, {"[4.8,5.2)": 249788}, {"[5.2,5.6)": 274928}, {"[5.6,6.0)": 329839}, {"[6.0,6.4)": 484011}, {"[6.4,7.2)": 1198917}, {"[7.2,8.0)": 830484}, {"[8.0,8.8)": 572708}, {"[8.8,9.6)": 451159}, {"[9.6,10.4)": 395841}, {"[10.4,11.2)": 373503}, {"[11.2,12.0)": 451987}, {"[12.0,12.8)": 531331}, {"[12.8,14.4)": 770149}, {"[14.4,16.0)": 632207}, {"[16.0,17.6)": 533134}, {"[17.6,19.2)": 486974}, {"[19.2,20.8)": 426922}, {"[20.8,22.4)": 517026}, {"[22.4,24.0)": 474835}, {"[24.0,25.6)": 419358}, {"[25.6,28.8)": 893763}, {"[28.8,32.0)": 852830}, {"[32.0,35.2)": 819699}, {"[35.2,38.4)": 809411}, {"[38.4,41.6)": 744626}, {"[41.6,44.8)": 769875}, {"[44.8,48.0)": 713671}, {"[48.0,51.2)": 704817}, {"[51.2,57.6)": 1373824}, {"[57.6,64.0)": 1376515}, {"[64.0,70.4)": 1402915}, {"[70.4,76.8)": 1434799}, {"[76.8,83.2)": 1426155}, {"[83.2,89.6)": 1263072}, {"[89.6,96.0)": 919922}, {"[96.0,102.4)": 492696}, {"[102.4,115.2)": 222437}, {"[115.2,128.0)": 10392}, {"[128.0,140.8)": 451}, {"[140.8,153.6)": 146}, {"[153.6,166.4)": 78}, {"[166.4,179.2)": 22}, {"[179.2,192.0)": 1}, {"[307.2,332.8)": 3}, {"[332.8,358.4)": 6}, {"[358.4,384.0)": 3}, {"[384.0,409.6)": 7}, {"[409.6,460.8)": 6}, {"[460.8,512.0)": 15}, {"[512.0,563.2)": 2}]
p99                  | 102.4
p95                  | 89.6
p90                  | 83.2

Reset statistics

The pg_stat_statements_reset(userid, dbid, queryid) function resets the statistics collected for all tracked SQL queries in the pg_stat_statements view. Use this to clear old or irrelevant statistics, start fresh for new performance monitoring, or manage resource usage.

The pg_stat_statements_reset function clears query statistics collected by pg_stat_statements based on the provided userid, dbid, and queryid parameters. Here's how it works:

If you specify any combination of userid, dbid, or queryid, only the matching statistics are reset.
If a parameter is not provided or is set to 0 (invalid), it acts as a wildcard, and statistics matching the other specified parameters are cleared. For example to clear all statistics for userid 2:
sql
```
select pg_stat_statements_reset(2);
```
If all parameters are omitted or set to 0 (invalid), the function resets all collected statistics. For example, to clear all stats you would need to just execute the following:
sql
```
select pg_stat_statements_reset();
```

By default, only superusers can execute this function. However, you can grant access to other users using the GRANT command.

Use pg_stat_statements_reset for the following scenarios:

After system maintenance - Clear old query statistics to track performance changes after upgrades, configuration changes, or schema modifications.
Isolating specific workloads - If you want to analyze the performance of a specific workload or test scenario, you can reset the statistics before running the workload and then examine the collected data.
Managing resource usage - If the shared memory allocated for pg_stat_statements is nearing capacity, resetting can help free space for new statistics.
Troubleshooting - When investigating performance issues, resetting the statistics can help to isolate the problem and focus on recent activity.

Examples

Describe the columns in the view:

sql

yugabyte=# \d pg_stat_statements;

caddyfile{.nocopy}

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

I/O-intensive queries

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY (blk_read_time+blk_write_time)/calls desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY (blk_read_time+blk_write_time) desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

Time-consuming queries

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY mean_time desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY total_time desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

Response-time outliers

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY stddev_time desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

Queries by memory usage

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY (shared_blks_hit+shared_blks_dirtied) desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

Consumers of temporary space

sql

yugabyte=# SELECT userid::regrole, dbid, query
    FROM pg_stat_statements
    ORDER BY temp_blks_written desc
    LIMIT 10;

caddyfile{.nocopy}

  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)

Learn more

Latency histogram and P99 latencies in YugabyteDB Anywhere
Active Session History