【PG】pg_stat_statements

create extension pg_stat_statements;


show shared_preload_libraries;

Useful commands

-- Flush data from pg_stat_statements:
> pg_stat_statements_reset() function will reset data from pg_stat_statements table.


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

-- Find the dbid for the database 
 select pg_stat_statements.dbid,datname,count(*) from pg_stat_statements join 
pg_database on pg_stat_statements.dbid=pg_database.oid  group by  pg_stat_statements.dbid,datname;
 dbid  | datname  | count
-------+----------+-------
 15846  | edb      |     3
 15845 | postgres |    18  <<<<<<<<<<<<<<<<<<<<<<<<<<
(2 rows)


 select pg_stat_statements_reset(0, 15845, 0);
pg_stat_statements_reset
--------------------------
(1 row)


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

-- Find the queryid 
select userid::regrole, dbid, queryid,query  from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
	    limit 10;
-[ RECORD 1 ]------+---

userid             | enterprisedb
dbid               | 15846
queryid            | 123573657  <<<<<<<<<<<<<<<<<<<<<<<<<<<<
query              | insert into test select  * from test
-[ RECORD 2 ]------+-----

select pg_stat_statements_reset(0, 0, 123573657);
pg_stat_statements_reset
--------------------------

(1 row)






-- Monitoring using pg_stat_statements:
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;


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

-- Top time consuming queries
 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;
-[ 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	


-- with high memory usage:
select userid::regrole, dbid, queryid,query  from pg_stat_statements 
            order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;