edit-icon download-icon

Locate the SQL statements with the greatest resource consumption

Last Updated: Feb 07, 2018

A database is a relatively large application. Busy databases consume many resources such as memory, CPU, I/O, and network resources. SQL optimization is a means of optimizing your databases. To achieve the best result of optimization, you must first locate the SQL statements with the greatest resource consumption, such as the SQL statements that consume the most I/O resources.

Database resources include CPU, memory, and I/O. To locate the SQL statements that consume the most database resources in each dimension, you can use the pg_stat_statements plugin to collect statistics on the resource overhead of the database and analyze the top SQL statements listed by resource consumption.

This article provides examples to illustrate how to create the pg_stat_statements plugin, analyze the top SQL statements, and reset statistics.

Create the pg_stat_statements plugin

Run the following command to create the pg_stat_statements plugin in the database for which you want to list the top SQL statements by resource consumption.

  1. create extension pg_stat_statements;

Analyze top SQL statements

Output results of pg_stat_statements

You can perform a query on the pg_stat_statements view to obtain statistics on the resource overhead of the database. Some filter conditions of SQL statements are replaced with variables in pg_stat_statements to reduce repeated display.

The pg_stat_statements view contains important information such as:

  • Information about SQL statement execution, including the call times, total time consumption, shortest execution time, longest execution time, average execution time, execution time variance which reflects jitter, and total number of rows that are scanned, returned, or processed.

  • Usage of the shared buffer, including the hit ratio, miss ratio, generated dirty data blocks, and evicted dirty data blocks.

  • Usage of the local buffer, including the hit ratio, miss ratio, generated dirty data blocks, and evicted dirty data blocks.

  • Usage of the temp buffer, including the numbers of read dirty data blocks and evicted dirty data blocks.

  • Time of data block read/write

The following table lists the parameters in the output results of pg_stat_statements.

Name Type References Description
userid oid pg_authid.oid OID of user who executed the statement.
dbid oid pg_database.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).

SQL statements with the greatest I/O consumption

Run the following command to list the top five SQL statements which consume the most I/O resources in one call.

  1. select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

Run the following command to list the top five SQL statements which consume the most I/O resources in total.

  1. select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

SQL statements with the greatest time consumption

Run the following command to list the top five SQL statements which consume the most time in one call.

  1. select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

Run the following command to list the top five SQL statements which consume the most time in total.

  1. select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

SQL statements with the most severe response jitter

Run the following command to list the top five SQL statements with the most severe response jitter.

  1. select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

SQL statements with the greatest consumption of shared memory

Run the following command to list the top five SQL statements which consume the most shared memory resources.

  1. select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

SQL statements with the greatest consumption of temporary space

Run the following command to list the top five SQL statements which consume the most temporary space.

  1. select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

Reset statistics

pg_stat_statements collects statistics accumulatively. To view the statistics in a time period, you must print snapshots. For more information, see the PostgreSQL AWR Report (for ApsaraDB for PostgreSQL).

You can run the following command to clear historical statistics on a regular basis.

  1. select pg_stat_statements_reset();

References

PostgreSQL 9.6.2 Documentation — F.29. pg_stat_statements

Thank you! We've received your feedback.