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.
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:
Open the postgresql.conf file and make the following changes to enable the pg_stat_statements extension and configure its parameters:
postgresql.conf
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
pg_stat_statements.track
top
pg_stat_statements.track_utility
on
pg_stat_statements.track_planning
off
pg_stat_statements.save
After making these changes, restart your PostgreSQL server:
pg_ctl restart -D /path_to_pgdata/data
Finally, create the pg_stat_statements extension in your database:
create extension 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
dbid
queryid
query
calls
total_time
min_time
max_time
mean_time
stddev_time
rows
shared_blks_hit
shared_blks_read
shared_blks_dirtied
shared_blks_written
local_blks_hit
local_blks_read
local_blks_dirtied
local_blks_written
temp_blks_read
temp_blks_written
blk_read_time
blk_write_time
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.
dbdocs
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
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();
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:
showtext := false
select * from pg_stat_statements(showtext := false);
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.