Waiting for PostgreSQL 14 – pg_stat_statements: track number of rows processed by some utility commands.

Title: Waiting for PostgreSQL 14 – pg_stat_statements: track number of rows processed by some utility commands.

On 29th of July 2020, Fujii Masao committed patch:

pg_stat_statements: track number of rows processed by some utility commands.
 
This commit makes pg_stat_statements track the total number
of rows retrieved or affected by CREATE TABLE AS, SELECT INTO,
CREATE MATERIALIZED VIEW and FETCH commands.
 
Suggested-by: Pascal Legrand
Author: Fujii Masao
Reviewed-by: Asif Rehman
Discussion: https://postgr.es/m/1584293755198-0.post@n3.nabble.com

Commit log seems to be clear, but just in case, let's see how it looks.

Of course, to have pg_stat_statements available, you have to load it by adding pg_stat_statements to your shared_preload_libraries:

$ show shared_preload_libraries;
 shared_preload_libraries 
──────────────────────────
 pg_stat_statements
(1 row)

If you don't have it, you can add it from SQL:

alter system set shared_preload_libraries = 'pg_stat_statements';

but it will require PostgreSQL restart anyway.

Afterwards, simple:

$ create extension pg_stat_statements ;
CREATE EXTENSION

And you can use it.

There are many columns in pg_stat_statements view, but the important for now are:

query – well, what query this is about
rows – how many rows were processed

So, let's see it. First, let's make sure the stats are empty, to avoid having too much data to display:

$ select pg_stat_statements_reset();
 pg_stat_statements_reset 
──────────────────────────
 
(1 row)

Now, let's make a table, and insert 10 sample rows:

$ create table test (i int4);
CREATE TABLE
 
$ insert into test (i) select generate_series(1,10);
INSERT 0 10

Stats now look like this:

$ select query, rows from pg_stat_statements where query !~ 'pg_catalog';
                       query                        │ rows 
────────────────────────────────────────────────────┼──────
 create table test (i int4)                         │    0
 select pg_stat_statements_reset()                  │    1
 insert into test (i) select generate_series($1,$2) │   10
(4 rows)

As you can see it correctly stored information that 10 rows were processed by insert into.

So, let's add some more:

$ insert into test (i) select generate_series(1,10);
INSERT 0 10
 
$ select query, rows from pg_stat_statements where query !~ 'pg_catalog';
                            query                             │ rows 
──────────────────────────────────────────────────────────────┼──────
 create table test (i int4)                                   │    0
 select pg_stat_statements_reset()                            │    1
 select query, rows from pg_stat_statements where query !~ $1 │    3
 insert into test (i) select generate_series($1,$2)           │   20
(5 rows)

Sweet. So, let's test the other commands too:

$ create table test2 as select i from generate_series(1,1231) i;
SELECT 1231
 
$ select generate_series(1,100) into test3;
SELECT 100
 
$ begin;
BEGIN
 
*$ declare tc cursor for select * from test;
DECLARE CURSOR
 
*$ fetch 5 from tc;
 i 
───
 1
 2
 3
 4
 5
(5 rows)
 
*$ rollback;
ROLLBACK

And now, stats look like:

$ select query, rows from pg_stat_statements where query !~ 'pg_catalog';
                             query                             │ rows 
───────────────────────────────────────────────────────────────┼──────
 create table test2 as select i from generate_series(1,1231) i │ 1231
 fetch 5 from tc                                               │    5
 rollback                                                      │    0
 create table test (i int4)                                    │    0
 select pg_stat_statements_reset()                             │    1
 begin                                                         │    0
 select query, rows from pg_stat_statements where query !~ $1  │    9
 select generate_series(1,100) into test3                      │  100
 declare tc cursor for select * from test                      │    0
 select query, rows from pg_stat_statements                    │    4
 insert into test (i) select generate_series($1,$2)            │   20
 create materialized view test4 as select * from test          │   20
(12 rows)

Great, all row counts are as expected.

That's definitely helpful for all admins, thanks a lot to all involved.