The cumulative statistics system (CSS) in PostgreSQL and pg_stat_statements in particular lack any timing information, all values are cumulative and the only way to figure out the difference between query executions is to reset the stats every time or work with averages.

With the pgstat_snap extension, you can create timestamped snapshots of pg_stat_statements and pg_stat_activity when needed. It also provides views that show the difference between every snapshot for every query and database.

pg_stat_statements must be loaded and tracking activated in the postgres config:

shared_preload_libraries = 'pg_stat_statements'

Recommended settings:

pg_stat_statements.track = all  
pg_stat_statements.track_utility = off

The extension has to be created in the database in which pgstat_snap will be installed:

create extension pg_stat_statements;

To install the extension, download these files:

pgstat_snap--1.0.sql
pgstat_snap.control

And copy them to the extension directory of PostgreSQL

sudo cp pgstat_snap* $(pg_config --sharedir)/extension/

You can then install the extension in any database that has the pg_stat_statements extension enabled, superuser right are NOT needed:

create extension pgstat_snap;

It can also be installed into a different schema but be sure to have it included in the search_path:

create extension pgstat_snap schema my_schema;

This will create the following tables and views:

  pgstat_snap_stat_history   -> pg_stat_statements history (complete snapshot)
  pgstat_snap_act_history    -> pg_stat_activity history (complete snapshot)
  pgstat_snap_diff_all       -> view containing the sum and difference of each statement between snapshots
  pgstat_snap_diff           -> view containing only the difference of each statement between snapshots

Start gathering snapshots with, e.g. every 1 second 60 times:

CALL pgstat_snap_collect(1, 60);

Or gather a snapshot every 5 seconds for 10 minutes:

CALL pgstat_snap_collect(5, 120);

IMPORTANT: on very busy clusters with many databases a lot of data can be collected, 500mb per minute or more. Don’t let it run for a very long time with short intervals, unless you have the disk space for it.

Because everything is timestamped, a reset is usually not needed between CALLs to create_snapshot. But you can to cleanup and keep the tables smaller. You can also reset pg_stats*.

Reset all pgstat_snap tables with:

  SELECT pgstat_snap_reset();   -> reset only pgstat_snap.pgstat*history tables
  SELECT pgstat_snap_reset(1);  -> also select pg_stat_statements_reset()
  SELECT pgstat_snap_reset(2);  -> also select pg_stat_reset()

The first argument to create_snapshot is the interval in seconds, the second argument is how many snapshots should be collected. Every interval seconds, select * from pg_stat_statements will be inserted into pgstat_snap_stat_history and select * from pgstat_act_statements into pgstat_snap_act_history.

For every row, a timestamp will be added. Only rows where the “rows” column has changed will be inserted into pgstat_snap_stat_history and always only one row per timestamp, dbid and queryid. Every insert is immediately committed to be able to live follow the tables/views.

The views have a _d column which displays the difference between the current row and the last row where the query was recorded in the pgstat_snap_stat_history table. NULL values in rows_d, calls_d and so on mean, that no previous row for this query was found because it was executed the first time since create_snapshot was running.

The views also contain the datname, wait events and the first 20 characters of the query, making it easier to identify queries of interest.

To completely uninstall pgstat_snap, run:

DROP EXTENSION pgstat_snap;

This view only contains the difference between the previous and next execution of a queryid/dbid pair:

select * from pgstat_snap_diff order by 1;

This view contains the difference between the previous and next execution of a queryid/dbid pair and the sum of the fields as recorded in pg_stat_statements at that time:

select * from pgstat_snap_diff_all order by 1;

Depending on screensize, you might want to set format to aligned, especially when querying pstat_snap_diff_all:

\pset format aligned

What was happening:

select * from pgstat_snap_diff order by 1;

What was every query doing:

select * from pgstat_snap_diff order by 2,1;

Which database touched the most rows:

select sum(rows_d),datname from pgstat_snap_diff group by datname;

Which query DML affected the most rows:

select sum(rows_d),queryid,query from pgstat_snap_diff where upper(query) not like 'SELECT%' group by queryid,query;

What wait events happened which weren’t of type Client:

select * from pgstat_snap_diff where wait_event_type is not null and wait_event_type <> 'Client' order by 2,1;

If needed you can access all columns for a particular query directly in the history tables:

select * from pgstat_snap_stat_history where queryid='123455678909876';