Tracking SQL Statements with pg_stat_statements in PostgreSQL

Introduction

The pg_stat_statements module in PostgreSQL is a powerful tool that helps you track and analyze SQL statements executed by your database server. This can be incredibly useful for identifying performance bottlenecks, understanding query patterns, and optimizing your database performance.

Enabling pg_stat_statements

To use pg_stat_statements, you need to enable it in your PostgreSQL configuration and set the appropriate parameters. Follow these steps:

Step 1: Edit postgresql.conf

Open the postgresql.conf file and make the following changes to enable the pg_stat_statements extension and configure its parameters:

shared_preload_libraries = 'pg_stat_statements'

# Default parameters values.
pg_stat_statements.max = 5000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = off
pg_stat_statements.save = on
        

• pg_stat_statements.max: Maximum number of statements tracked. Default: 5000
• pg_stat_statements.track: Controls which statements are tracked.
  Options:
  top [Track top-level statements (those issued directly by clients)]
  all [Track nested statements (such as statements invoked within functions)]
  none [Disable statement statistics collection].
  Default: top
• pg_stat_statements.track_utility: Tracks utility commands. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE.
  Default: on
• pg_stat_statements.track_planning: Tracks planning operations and duration. Enabling this parameter may incur a noticeable performance penalty, especially when a fewer kinds of queries are executed on many concurrent connections.
  Default: off
• pg_stat_statements.save: Saves statistics across server shutdowns. If it is `off` then statistics are not saved at shutdown nor reloaded at server start.
  Default: on

Step 2: Restart PostgreSQL

After making these changes, restart your PostgreSQL server:

pg_ctl restart -D /path_to_pgdata/data

Step 3: Create the Extension

Finally, create the pg_stat_statements extension in your database:

create extension pg_stat_statements;

Querying pg_stat_statements

Once enabled, pg_stat_statements collects performance data for all SQL statements executed by the server. You can query this data using the pg_stat_statements view. Here are some of the key columns:

• userid: OID of the user who executed the statement
• dbid: OID of the database in which the statement was executed
• queryid: Internal hash code, computed from the statement’s parse tree
• query: Text of a representative statement
• calls: Number of times executed
• total_time: Total time spent in the statement, in milliseconds
• min_time: Minimum time spent in the statement, in milliseconds
• max_time: Maximum time spent in the statement, in milliseconds
• mean_time: Mean time spent in the statement, in milliseconds
• stddev_time: Population standard deviation of time spent in the statement, in milliseconds
• rows: Total number of rows retrieved or affected by the statement
• shared_blks_hit: Total number of shared block cache hits by the statement
• shared_blks_read: Total number of shared blocks read by the statement
• shared_blks_dirtied: Total number of shared blocks dirtied by the statement
• shared_blks_written: Total number of shared blocks written by the statement
• local_blks_hit: Total number of local block cache hits by the statement
• local_blks_read: Total number of local blocks read by the statement
• local_blks_dirtied: Total number of local blocks dirtied by the statement
• local_blks_written: Total number of local blocks written by the statement
• temp_blks_read: Total number of temp blocks read by the statement
• temp_blks_written: Total number of temp blocks written by the statement
• blk_read_time: Total time the statement spent reading blocks, in milliseconds
• blk_write_time: Total time the statement spent writing blocks, in milliseconds

Examples

Show Queries from a Particular Database

To show queries from a specific database, use the following query:

select
    s.*
from
    pg_stat_statements s
    inner join
    pg_database d
        on s.dbid = d.oid
where
    d.datname = 'dbdocs'
order by
    total_time desc;
        

This query retrieves all SQL statements executed in the dbdocs database, ordered by total execution time.

Example Output

Name                | Value
--------------------|----------------------------------------------------
userid              | 16415
dbid                | 16396
queryid             | 3005214073
query               | UPDATE Auditlog SET Event = 'UserRegistration' WHERE EventID = $1
calls               | 1844
total_time          | 1866.8169900000003
min_time            | 0.052079
max_time            | 224.07873
mean_time           | 1.0123736388286355
stddev_time         | 7.445661083133956
rows                | 1844
shared_blks_hit     | 92077
shared_blks_read    | 690
shared_blks_dirtied | 830
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0.0
blk_write_time      | 0.0
        

Functions

pg_stat_statements_reset()

This function discards all statistics gathered so far by pg_stat_statements. By default, only superusers can execute this function:

select pg_stat_statements_reset();

pg_stat_statements(showtext boolean)

The pg_stat_statements view is defined in terms of a function also named pg_stat_statements. You can call this function directly and specify showtext := false to omit the query text:

select * from pg_stat_statements(showtext := false);

Conclusion

The pg_stat_statements module is a valuable addition to any PostgreSQL database, providing deep insights into query performance and helping you identify and address performance bottlenecks. By enabling and utilizing this module, you can ensure that your database runs efficiently and effectively.

Related content

• Troubleshooting Long-Running or Blocked Queries in PostgreSQL
• How to Analyze PostgreSQL Performance with Logging and pgBadger
• How to Retrieve Settings in PostgreSQL
• Optimizing PostgreSQL Performance: Tuning Shared Buffers
• PostgreSQL Vacuuming Best Practices
• Analyzing and Vacuuming Tables in PostgreSQL
• Best Practices for Managing PostgreSQL Statistics
• Understanding and Improving Table and Index Data Density in PostgreSQL
• Tracking SQL Statements with pg_stat_statements in PostgreSQL
• Retrieving Index Information in PostgreSQL
• Understanding and Analyzing Index Usage in PostgreSQL
• Understanding PostgreSQL Cache Hit Ratio

Rate Your Experience