Contents

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 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
  • 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
  • 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

Categories

Contrib module, Extension, Logging, Monitoring, Performance, Statements

See also

track_io_timing