pg_stat_statements
A contrib module for tracking execution statistics of SQL statements
pg_stat_statements is a contrib module implemented as an extension for tracking execution statistics of SQL statements.
pg_stat_statements was added in PostgreSQL 8.4.
Change history
- PostgreSQL 14
- now tracks the total number of rows retrieved or affected by
CREATE TABLE AS,SELECT INTO,CREATE MATERIALIZED VIEWandFETCHcommands (commit 6023b7ea) - now tracks the total number of rows processed by
REFRESH MATERIALIZED VIEW(commit b62e6056) - view
pg_stat_statements_infoadded to display statistics about pg_stat_statements operations (initial commit 9fbc3f31) - column
topleveladded to view viewpg_stat_statementsindicating whether the tracked statement is a top-level or nested statement (commit 6b4d23fe)
- now tracks the total number of rows retrieved or affected by
- PostgreSQL 13
- PostgreSQL 12
- statistics can now be reset for specific databases, users, and queries (commit 43cbedab)
- PostgreSQL 11
queryidexpanded to 64 bits to reduce the chance of collisions (commit cff440d3)
- PostgreSQL 10
- ignored constants shown as "
$N" rather than "?" (commit a6f22e83)
- ignored constants shown as "
- PostgreSQL 9.5
- statistics for minimum, maximum, mean, and standard deviation times added (commit 717f7095)
- PostgreSQL 9.4
- PostgreSQL 9.2
- PostgreSQL 9.1
- PostgreSQL 9.0
- buffer access counters added (commit 8964dbd5)
- PostgreSQL 8.4
- added (commit 7466eeac; note that several proceeding commits added infrastructure required for this extension)
Examples
Sample pg_stat_statements record for an arbitrary query:
proddb=# SELECT * FROM pg_stat_statements WHERE queryid='2070117699405581796'\gx
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------
userid | 16384
dbid | 16389
queryid | 2070117699405581796
query | /* getCommentsTotal */ +
| SELECT COUNT(*) AS total +
| FROM entry c +
| INNER JOIN object o +
| ON o.object_id = c.entry_reference_id +
| INNER JOIN object_version ov +
| ON (ov.object_id = o.object_id AND ov.object_status_id = $1 AND ov.version = $2)+
| WHERE c.entry_status = $3 +
| AND o.site_id IN (SELECT * FROM f_user_get_site_ids($4))
calls | 4
total_time | 7556.941424000001
min_time | 37.801478
max_time | 5922.666363
mean_time | 1889.2353560000001
stddev_time | 2349.3165152342754
rows | 4
shared_blks_hit | 204947
shared_blks_read | 305706
shared_blks_dirtied | 132062
shared_blks_written | 162
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
blk_write_time | 0
References
- PostgreSQL documentation: pg_stat_statements
Useful links
- A quick pg_stat_statements troubleshooting hack - August 2020 blog article from CyberTec