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 15
- columns
temp_blk_read_time
andtemp_blk_write_time
added to viewpg_stat_statements
for tracking I/O timing for temporary file blocks (commit 76cbf7ed) - following JIT counter fields added (commit 57d6aea0):
jit_functions
jit_generation_time
jit_inlining_count
jit_inlining_time
jit_optimization_count
jit_optimization_time
jit_emission_count
jit_emission_time
- columns
- 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 aboutpg_stat_statements
operations (initial commit 9fbc3f31) - column
toplevel
added to 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
- configuration option
pg_stat_statements.track_utility
to track WAL usage statistics (commit 6b466bf5) - configuration option
pg_stat_statements.track_planning
to track planning statistics (commit 17e03282) - support for tracking of planning statistics added(commit 17e03282)
- following
pg_stat_statements
columns renamed:total_time
→total_exec_time
min_time
→min_exec_time
max_time
→max_exec_time
mean_time
→mean_exec_time
stddev_time
→stddev_exec_time
- following
pg_stat_statements
columns added:plans
total_plan_time
min_plan_time
max_plan_time
mean_plan_time
stddev_plan_time
- following
- support for WAL usage stastics tracking added (commit 6b466bf5)
- following additional columns added to the
pg_stat_statements
view:wal_records
wal_fpi
wal_bytes
- following additional columns added to the
- configuration option
- 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
- SQL text normalization (commit 7313cc01)
- dirtied buffer counts added (commit 22543674)
- columns
blk_read_time
andblk_write_time
added, which will be populated iftrack_io_timing
is enabled (commit 5b4f3466)
- 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