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 VIEW
andFETCH
commands (commit 6023b7ea) - now tracks the total number of rows processed by
REFRESH MATERIALIZED VIEW
(commit b62e6056) - view
pg_stat_statements_info
added to display statistics about pg_stat_statements operations (initial commit 9fbc3f31) - column
toplevel
added to view viewpg_stat_statements
indicating 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
queryid
expanded 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