Viswamitra's blog

This is used for tracking execution statistics of all SQL statements executed by a server. It will track all the SQL statements executed by the users on all the databases. But we created this extension in a single database and we can access the stats only by login into that database.

This is a contrib module (extension). For this we need to install the contrib modules, refer to http://oguridbtech.blogspot.in/2018/01/install-all-contrib-modules-in.html. 

create extension pg_stat_statements; — Execute this statement by login to the database where you want the views to be created.

Put the pg_stat_statements in shared_preload_libraries and restart the server to take the value effect.

shared_preload_libraries=’pg_stat_statements’

This will create a view called “pg_stat_statements” in the database. Now on wards what ever the queries are getting executed they are logged into the pg_stat_statements view. There will be an entry for “userid”,”dbid”,”queryid”. 

pg_stat_statements_reset() — function is used to reset the table or delete the old stats.

pg_stat_statements(showtext boolean) returns setof record — This function return all the columns except the query column.

pg_stat_statements.max:pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view).

pg_stat_statements.track:pg_stat_statements.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all too also track nested statements (such as statements invoked within functions), or none to disable statement statistics collection. The default value is top.

pg_stat_statements.track_utility:pg_stat_statements.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATEand DELETE. 

pg_stat_statements.save:pg_stat_statements.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on.

To delete all the old stats execute the below function :

select pg_stat_statements_reset();