PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid

PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:

pg_stat_statements_reset() returns void

This means your only choice is to reset all the statistics. Today this commit landed and this will give you more control on which statistics to reset. The signature of the function now looks like this:

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

There are three new parameters for controlling what to reset: The user id, the database id and the id of a specific query. By default all of them are 0 meaning the the function will behave as in previous versions: Discarding all the statistics. Lets create two users, two databases and a table in each so we will have something in pg_stat_statements we can work with:

postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create user u1 with login password 'u1'" postgres
CREATE ROLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create user u2 with login password 'u2'" postgres
CREATE ROLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create database db1 with owner = u1" postgres
CREATE DATABASE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create database db2 with owner = u2" postgres
CREATE DATABASE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create table t1 (a int)" -U u1 db1
CREATE TABLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create table t1 (a int)" -U u2 db2
CREATE TABLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "insert into t1 select * from generate_series(1,100)" -U u1 db1
INSERT 0 100
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "insert into t1 select * from generate_series(1,100)" -U u2 db2
INSERT 0 100
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "select count(*) from t1" -U u1 db1
 count 
-------
   100
(1 row)
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "select count(*) from t1" -U u2 db2
 count 
-------
   100
(1 row)

We should be able to see the statements in pg_stat_statements but before doing that lets check the dbids:

postgres@pgbox:/home/postgres/ [PGDEV] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16394            db1  pg_default
  16395            db2  pg_default
  13569       postgres  pg_default
  13568      template0  pg_default
      1      template1  pg_default

What do we see for our two databases?

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395);
 userid | dbid  |       queryid        | calls |                        query                        
--------+-------+----------------------+-------+-----------------------------------------------------
  16392 | 16394 |  7490503619681577402 |     3 | set client_encoding to 'unicode'
  16393 | 16395 |   843119317166481275 |     1 | insert into t1 select * from generate_series($1,$2)
  16392 | 16394 | -3672942776844552312 |     1 | insert into t1 select * from generate_series($1,$2)
  16393 | 16395 |  7490503619681577402 |     3 | set client_encoding to 'unicode'
  16392 | 16394 |  5583984467630386743 |     1 | select count(*) from t1
  16393 | 16395 |  4983979802666994390 |     1 | select count(*) from t1
  16393 | 16395 |  6842879890091936614 |     1 | create table t1 (a int)
  16392 | 16394 |  6842879890091936614 |     1 | create table t1 (a int)

We should be able to reset the statistics for a specific query:

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;
 userid | dbid  |       queryid       | calls |          query          
--------+-------+---------------------+-------+-------------------------
  16393 | 16395 | 6842879890091936614 |     1 | create table t1 (a int)
  16392 | 16394 | 6842879890091936614 |     1 | create table t1 (a int)
(2 rows)
postgres=# select pg_stat_statements_reset(0, 0, 6842879890091936614);
 pg_stat_statements_reset 
--------------------------
 
(1 row)

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;
 userid | dbid | queryid | calls | query 
--------+------+---------+-------+-------
(0 rows)

Notice that this of course resets the statistics for both statements as they have the same queryid. You could specify the userid and/or dbid as well to reset just one of them. Nice new feature.