What is
pg_stat_statement : -
The pg_stat_statements module
provides a means for tracking execution statistics of all SQL statements
executed by a server.
It provides below
information for each individual SQL.
OID of user who executed the statement
OID of database in which the statement was
executed
Internal hash code, computed from the statement's
parse tree
Text of a representative statement
Number of times executed
Total time spent in the statement, in
milliseconds
Total number of rows retrieved or affected by the
statement
Total number of shared block cache hits by the
statement
Total number of shared blocks read by the
statement
Total number of shared blocks dirtied by the
statement
Total number of shared blocks written by the
statement
Total number of local block cache hits by the
statement
Total number of local blocks read by the
statement
Total number of local blocks dirtied by the
statement
Total number of local blocks written by the
statement
Total number of temp blocks read by the statement
Total number of temp blocks written by the
statement
Total time the statement spent reading blocks, in
milliseconds (if track_io_timing is enabled, otherwise zero)
Total time the statement spent writing blocks, in
milliseconds (if track_io_timing is enabled, otherwise zero)
Installation
of pg_stat_statement :-
Step 1
create extension pg_stat_statements ;
|
Step 2
Add below entries into postgresql.conf
shared_preload_libraries =
'pg_stat_statements'
pg_stat_statements.max =
10000
pg_stat_statements.track =
all
Then Restart PostgreSQL service.
Configuration
Parameters: -
A) pg_stat_statements.max
(integer)
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). If more distinct statements
than that are observed, information about the least-executed statements is
discarded. The default value is 5000. This parameter can only be set at server
start.
B) pg_stat_statements.track
(enum)
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 to also track
nested statements (such as statements invoked within functions), or none to
disable statement statistics collection. The default value is top. Only
superusers can change this setting.
C) pg_stat_statements.track_utility
(boolean)
pg_stat_statements.track_utility
controls whether utility commands are tracked by the module. Utility commands
are all those other than SELECT, INSERT, UPDATE and DELETE. The default value
is on. Only superusers can change this setting.
D) pg_stat_statements.save
(boolean)
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. This parameter can only be set in the postgresql.conf
file or on the server command line.
The module requires additional shared memory
proportional to pg_stat_statements.max. Note that this memory is consumed
whenever the module is loaded, even if pg_stat_statements.track is set to none.
Functions :
-
A) pg_stat_statements_reset()
returns void
pg_stat_statements_reset
discards all statistics gathered so far by pg_stat_statements. By default, this
function can only be executed by superusers.
B) pg_stat_statements(showtext
boolean) returns setof record
The
pg_stat_statements view is defined in terms of a function also named
pg_stat_statements.
The pg_stat_statements view: -
The statistics gathered by the module are made available
via a system view named pg_stat_statements. This view contains one
row for each distinct database ID, user ID and query ID (up to the maximum
number of distinct statements that the module can track).
Reference Queries -
1)
Select * from pg_stat_statements ;
2)
SELECT
substring(query, 1, 50) AS short_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
20;
3)
select userid,query,calls,total_time from pg_stat_statements;
4)
select * from pg_stat_statements(true);
