• Browse categories
• Random entry
• PostgreSQL releases
  • PostgreSQL 16 (dev)
  • PostgreSQL 15 (rc2)
  • PostgreSQL 14 (latest)
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6 (EOL)

Version charts

Useful overviews

Useful links

pg_stat_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 and temp_blk_write_time added to view pg_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
• PostgreSQL 14
  • now tracks the total number of rows retrieved or affected by CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW and FETCH 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 pg_stat_statements indicating whether the tracked statement is a top-level or nested statement (commit 6b4d23fe)
• 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
  • 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
• 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)
• PostgreSQL 9.5
  • statistics for minimum, maximum, mean, and standard deviation times added (commit 717f7095)
• PostgreSQL 9.4
  • query text stored in file (commit f0d6f202)
  • records can be returned without the query text (commit f0d6f202)
  • internal query hash identifier exposed (commit 91484409)
  • DEALLOCATE no longer tracked (commit 3cd934f6)
  • statistics file saved to pg_stat directory instead of global (commit 654e8e44)
• PostgreSQL 9.2
  • SQL text normalization (commit 7313cc01)
  • dirtied buffer counts added (commit 22543674)
  • columns blk_read_time and blk_write_time added, which will be populated if track_io_timing is enabled (commit 5b4f3466)
• PostgreSQL 9.1
  • converted to an extension (commit 629b3af2)
• 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

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

Useful links

Categories

See also