PG_STAT_STATEMENTS module is useful in monitoring and tracking sql queries in postgres. By default this module , this option is disabled. We need to enable this feature explicitly.

In this article, we will explain how to enable pg_stat_statements module and how to use it.

1. Check whether pg_stat_statement module is present or not:



postgres=#  \d pg_stat_statements
Did not find any relation named "pg_stat_statements".


-- List down the extensions

postgres=#<span style="color: #ff0000;"><strong> \dx</strong></span>
                                  List of installed extensions
       Name       | Version |   Schema   |                     Description
------------------+---------+------------+------------------------------------------------------
 edb_dblink_libpq | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
 edb_dblink_oci   | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
 edbspl           | 1.0     | pg_catalog | EDB-SPL procedural language
 pldbgapi         | 1.1     | pg_catalog | server-side support for debugging PL/pgSQL functions
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)


pg_stat_statements extension is not present.

2. Create the extension:



postgres=# <span style="color: #ff0000;"><strong>create extension pg_stat_statements;</strong></span>
CREATE EXTENSION
postgres=# <span style="color: #ff0000;"><strong>\dx</strong></span>
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 edb_dblink_libpq   | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
 edb_dblink_oci     | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
 edbspl             | 1.0     | pg_catalog | EDB-SPL procedural language
<em><span style="color: #ff0000;"><strong> pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
</strong></span></em> pldbgapi           | 1.1     | pg_catalog | server-side support for debugging PL/pgSQL functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(6 rows)

3. update parameters in postgres.conf file:

Add pg_stat_statements to shared_preload_libraries parameter;

 

vi postgres.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max=20000
pg_stat_statements.track= top

If shared_preload_libraries parameter has already some value defined,  , then just append the value in postgres.conf file


shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,<span style="color: #ff0000;"><strong>$libdir/pg_stat_statements</strong></span>'

pg_stat_statements.max – sets the max number of statements PostgreSQL will keep track of. The Default is 5000.

pg_stat_statements.track – Default is top.

        all – Tracks all statements, including those inside function calls
       top – Tracks only statements issued by clients
       none – disable collection

 

If shared_preload_libraries parameter not defined properly, then below error will come.

postgres=# select * from pg_stat_statements ;
ERROR: pg_stat_statements must be loaded via shared_preload_libraries

 

5. Restart the postgres cluster service:

systemctl stop edb-as-11
systemctl start edb-as-11

6 . Check whether pg_stat_statements is available or not

postgres=#  <span style="color: #ff0000;"><strong>show shared_preload_libraries;</strong></span>
                           shared_preload_libraries
------------------------------------------------------------------------------
 $libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/pg_stat_statements
(1 row)

	
postgres=# <span style="color: #ff0000;"><strong>\d pg_stat_statements</strong></span>
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers
---------------------+------------------+-----------
 userid              | oid              |
 dbid                | oid              |
 queryid             | bigint           |
 query               | text             |
 calls               | bigint           |
 total_time          | double precision |
 min_time            | double precision |
 max_time            | double precision |
 mean_time           | double precision |
 stddev_time         | double precision |
 rows                | bigint           |
 shared_blks_hit     | bigint           |
 shared_blks_read    | bigint           |
 shared_blks_dirtied | bigint           |
 shared_blks_written | bigint           |
 local_blks_hit      | bigint           |
 local_blks_read     | bigint           |
 local_blks_dirtied  | bigint           |
 local_blks_written  | bigint           |
 temp_blks_read      | bigint           |
 temp_blks_written   | bigint           |
 blk_read_time       | double precision |
 blk_write_time      | double precision |


postgres=# <span style="color: #ff0000;"><strong>select count(*) from pg_stat_statements</strong></span>;
 count
-------
     1
(1 row)

7 . Monitoring using pg_stat_statements:

postgres# <span style="color: #ff0000;"><strong>SELECT  substring(query, 1, 50) AS query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM    pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;</strong></span>


                    query.                          | total_time | calls | mean | percentage_cpu
----------------------------------------------------+------------+-------+------+----------------
 select * from pg_stat_statements                   |       0.85 |     1 | 0.85 |          38.77
 SELECT e.extname AS "Name", e.extversion AS "Versi |       0.55 |     1 | 0.55 |          24.95
 WITH released_messages AS ( UPDATE sys.callback_qu |       0.17 |     2 | 0.08 |           7.57
 WITH released_messages AS ( UPDATE sys.callback_qu |       0.16 |     2 | 0.08 |           7.11
 show shared_preload_libraries                      |       0.10 |     1 | 0.10 |           4.56
 WITH released_messages AS ( UPDATE sys.callback_qu |       0.09 |     2 | 0.04 |           3.87
 WITH messages AS ( SELECT msgid   FROM sys.callbac |       0.04 |     2 | 0.02 |           1.71
 DELETE FROM sys.callback_queue_table qt  WHERE qt. |       0.04 |     2 | 0.02 |           1.60
 SELECT MIN(qt.next_event_time)   FROM sys.callback |       0.04 |     2 | 0.02 |           1.67

sql queries having high i/o activity

postgres# <span style="color: #ff0000;"><strong>select userid::regrole, dbid, query,queryid,mean_time/1000 as mean_time_seconds 
    from pg_stat_statements
    order by (blk_read_time+blk_write_time) desc
    limit 10;

</strong></span>

Top time consuming queries

<span style="color: #ff0000;"><strong><span style="color: #000000;">postgres#</span> select userid::regrole, dbid, query ,calls, total_time/1000 as total_time_seconds ,min_time/1000 as min_time_seconds,max_time/1000 as max_time_seconds,mean_time/1000 as mean_time_seconds
    from pg_stat_statements
    order by mean_time desc
    limit 10;</strong></span>
	
	
	
	    limit 10;
-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
userid             | enterprisedb
dbid               | 15846
query              | insert into test select  * from test
calls              | 9
total_time_seconds | 2.722928186
min_time_seconds   | 0.003885998
max_time_seconds   | 1.395848226
mean_time_seconds  | 0.302547576222222
-[ RECORD 2 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
userid             | enterprisedb
dbid               | 15846
query              | insert into test select  * from pg_tables
calls              | 5
total_time_seconds | 0.003757356
min_time_seconds   | 0.00065117
max_time_seconds   | 0.001032883
mean_time_seconds  | 0.0007514712
-[ RECORD 3 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------

Queries with high memory usage:

postgres=#<span style="color: #ff0000;"><strong> select userid::regrole, dbid, queryid,query  from pg_stat_statements 
            order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
</strong></span>

8 . Flush data from pg_stat_statements:

pg_stat_statements_reset() function will reset data from pg_stat_statements table.

To Flush all data from pg_stat_statements:


postgres=# select count(*) from pg_stat_statements;
 count
-------
    22
(1 row)

postgres=#  <span style="color: #ff0000;"><strong>SELECT pg_stat_statements_reset();</strong></span>
 pg_stat_statements_reset
--------------------------

(1 row)

postgres=# select count(*) from pg_stat_statements;
 count
-------
     1
(1 row)

postgres=#

To flush data of a particular database:( feature  available from postgres 12 onwards only)

-- Find the dbid for the database 
postgres=# <span style="color: #ff0000;"><strong>select pg_stat_statements.dbid,datname,count(*) from pg_stat_statements join </strong></span>
<span style="color: #ff0000;"><strong>pg_database on pg_stat_statements.dbid=pg_database.oid  group by  pg_stat_statements.dbid,datname;</strong></span>
 dbid  | datname  | count
-------+----------+-------
 15846  | edb      |     3
 <span style="background-color: #ffff99;"><em><span style="color: #ff0000; font-size: 20px; background-color: #ffff99;"><strong>15845</strong></span></em></span> | postgres |    18
(2 rows)


postgres# <strong><span style="color: #ff0000;">select pg_stat_statements_reset(0, <span style="background-color: #ffff99;"><em><span style="font-size: 20px; background-color: #ffff99;">15845</span></em></span>, 0);</span></strong>
pg_stat_statements_reset
--------------------------

(1 row)

To flush a particular query:( Available from postgres 12 onwards only)


-- Find the queryid 

postgres=# <span style="color: #ff0000;"><strong>select userid::regrole, dbid, queryid,query  from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
</strong></span>	    limit 10;
-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
userid             | enterprisedb
dbid               | 15846
queryid            | <span style="color: #ff0000;"><strong><em><span style="background-color: #ffff99; font-size: 20px;">123573657</span></em></strong></span>
query              | insert into test select  * from test
-[ RECORD 2 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------

postgres# <span style="color: #ff0000;"><strong>select pg_stat_statements_reset(0, 0, <em><span style="background-color: #ffff99; font-size: 20px;">123573657</span></em>);</strong></span>
pg_stat_statements_reset
--------------------------

(1 row)

More details regarding pg_stat_statements can be found here( CLICK LINK)

SEE ALSO: