Tracking SQL Statements with pg_stat_statements in PostgreSQL
Introduction
Enabling pg_stat_statements
Querying pg_stat_statements
Examples
Functions
Conclusion
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: 5000pg_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: toppg_stat_statements.track_utility: Tracks utility commands. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE.
Default: onpg_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: offpg_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 statementdbid: OID of the database in which the statement was executedqueryid: Internal hash code, computed from the statement’s parse treequery: Text of a representative statementcalls: Number of times executedtotal_time: Total time spent in the statement, in millisecondsmin_time: Minimum time spent in the statement, in millisecondsmax_time: Maximum time spent in the statement, in millisecondsmean_time: Mean time spent in the statement, in millisecondsstddev_time: Population standard deviation of time spent in the statement, in millisecondsrows: Total number of rows retrieved or affected by the statementshared_blks_hit: Total number of shared block cache hits by the statementshared_blks_read: Total number of shared blocks read by the statementshared_blks_dirtied: Total number of shared blocks dirtied by the statementshared_blks_written: Total number of shared blocks written by the statementlocal_blks_hit: Total number of local block cache hits by the statementlocal_blks_read: Total number of local blocks read by the statementlocal_blks_dirtied: Total number of local blocks dirtied by the statementlocal_blks_written: Total number of local blocks written by the statementtemp_blks_read: Total number of temp blocks read by the statementtemp_blks_written: Total number of temp blocks written by the statementblk_read_time: Total time the statement spent reading blocks, in millisecondsblk_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