1
<!-- doc/src/sgml/pgstatstatements.sgml -->
3
<sect1 id="pgstatstatements">
4
<title>pg_stat_statements</title>
6
<indexterm zone="pgstatstatements">
7
<primary>pg_stat_statements</primary>
11
The <filename>pg_stat_statements</filename> module provides a means for
12
tracking execution statistics of all SQL statements executed by a server.
16
The module must be loaded by adding <literal>pg_stat_statements</> to
17
<xref linkend="guc-shared-preload-libraries"> in
18
<filename>postgresql.conf</>, because it requires additional shared memory.
19
This means that a server restart is needed to add or remove the module.
23
<title>The <structname>pg_stat_statements</structname> View</title>
26
The statistics gathered by the module are made available via a system view
27
named <structname>pg_stat_statements</>. This view contains one row for
28
each distinct query text, database ID, and user ID (up to the maximum
29
number of distinct statements that the module can track). The columns
30
of the view are shown in <xref linkend="pgstatstatements-columns">.
33
<table id="pgstatstatements-columns">
34
<title><structname>pg_stat_statements</> Columns</title>
41
<entry>References</entry>
42
<entry>Description</entry>
47
<entry><structfield>userid</structfield></entry>
48
<entry><type>oid</type></entry>
49
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
50
<entry>OID of user who executed the statement</entry>
54
<entry><structfield>dbid</structfield></entry>
55
<entry><type>oid</type></entry>
56
<entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
57
<entry>OID of database in which the statement was executed</entry>
61
<entry><structfield>query</structfield></entry>
62
<entry><type>text</type></entry>
64
<entry>Text of the statement (up to <xref linkend="guc-track-activity-query-size"> bytes)</entry>
68
<entry><structfield>calls</structfield></entry>
69
<entry><type>bigint</type></entry>
71
<entry>Number of times executed</entry>
75
<entry><structfield>total_time</structfield></entry>
76
<entry><type>double precision</type></entry>
78
<entry>Total time spent in the statement, in seconds</entry>
82
<entry><structfield>rows</structfield></entry>
83
<entry><type>bigint</type></entry>
85
<entry>Total number of rows retrieved or affected by the statement</entry>
89
<entry><structfield>shared_blks_hit</structfield></entry>
90
<entry><type>bigint</type></entry>
92
<entry>Total number of shared blocks hits by the statement</entry>
96
<entry><structfield>shared_blks_read</structfield></entry>
97
<entry><type>bigint</type></entry>
99
<entry>Total number of shared blocks reads by the statement</entry>
103
<entry><structfield>shared_blks_written</structfield></entry>
104
<entry><type>bigint</type></entry>
106
<entry>Total number of shared blocks writes by the statement</entry>
110
<entry><structfield>local_blks_hit</structfield></entry>
111
<entry><type>bigint</type></entry>
113
<entry>Total number of local blocks hits by the statement</entry>
117
<entry><structfield>local_blks_read</structfield></entry>
118
<entry><type>bigint</type></entry>
120
<entry>Total number of local blocks reads by the statement</entry>
124
<entry><structfield>local_blks_written</structfield></entry>
125
<entry><type>bigint</type></entry>
127
<entry>Total number of local blocks writes by the statement</entry>
131
<entry><structfield>temp_blks_read</structfield></entry>
132
<entry><type>bigint</type></entry>
134
<entry>Total number of temp blocks reads by the statement</entry>
138
<entry><structfield>temp_blks_written</structfield></entry>
139
<entry><type>bigint</type></entry>
141
<entry>Total number of temp blocks writes by the statement</entry>
149
This view, and the function <function>pg_stat_statements_reset</>,
150
are available only in databases they have been specifically installed into
151
by installing the <literal>pg_stat_statements</> extension.
152
However, statistics are tracked across all databases of the server
153
whenever the <filename>pg_stat_statements</filename> module is loaded
154
into the server, regardless of presence of the view.
158
For security reasons, non-superusers are not allowed to see the text of
159
queries executed by other users. They can see the statistics, however,
160
if the view has been installed in their database.
164
Note that statements are considered the same if they have the same text,
165
regardless of the values of any out-of-line parameters used in the
166
statement. Using out-of-line parameters will help to group statements
167
together and may make the statistics more useful.
172
<title>Functions</title>
177
<function>pg_stat_statements_reset() returns void</function>
182
<function>pg_stat_statements_reset</function> discards all statistics
183
gathered so far by <filename>pg_stat_statements</>.
184
By default, this function can only be executed by superusers.
193
<title>Configuration Parameters</title>
198
<varname>pg_stat_statements.max</varname> (<type>integer</type>)
203
<varname>pg_stat_statements.max</varname> is the maximum number of
204
statements tracked by the module (i.e., the maximum number of rows
205
in the <structname>pg_stat_statements</> view). If more distinct
206
statements than that are observed, information about the least-executed
207
statements is discarded.
208
The default value is 1000.
209
This parameter can only be set at server start.
216
<varname>pg_stat_statements.track</varname> (<type>enum</type>)
221
<varname>pg_stat_statements.track</varname> controls which statements
222
are counted by the module.
223
Specify <literal>top</> to track top-level statements (those issued
224
directly by clients), <literal>all</> to also track nested statements
225
(such as statements invoked within functions), or <literal>none</> to
227
The default value is <literal>top</>.
228
Only superusers can change this setting.
235
<varname>pg_stat_statements.track_utility</varname> (<type>boolean</type>)
240
<varname>pg_stat_statements.track_utility</varname> controls whether
241
utility commands are tracked by the module. Utility commands are
242
all those other than <command>SELECT</>, <command>INSERT</>,
243
<command>UPDATE</> and <command>DELETE</>.
244
The default value is <literal>on</>.
245
Only superusers can change this setting.
252
<varname>pg_stat_statements.save</varname> (<type>boolean</type>)
257
<varname>pg_stat_statements.save</varname> specifies whether to
258
save statement statistics across server shutdowns.
259
If it is <literal>off</> then statistics are not saved at
260
shutdown nor reloaded at server start.
261
The default value is <literal>on</>.
262
This parameter can only be set in the <filename>postgresql.conf</>
263
file or on the server command line.
270
The module requires additional shared memory amounting to about
271
<varname>pg_stat_statements.max</varname> <literal>*</>
272
<xref linkend="guc-track-activity-query-size"> bytes. Note that this
273
memory is consumed whenever the module is loaded, even if
274
<varname>pg_stat_statements.track</> is set to <literal>none</>.
278
In order to set any of these parameters in your
279
<filename>postgresql.conf</> file,
280
you will need to add <literal>pg_stat_statements</> to
281
<xref linkend="guc-custom-variable-classes">. Typical usage might be:
285
shared_preload_libraries = 'pg_stat_statements'
287
custom_variable_classes = 'pg_stat_statements'
288
pg_stat_statements.max = 10000
289
pg_stat_statements.track = all
295
<title>Sample Output</title>
298
bench=# SELECT pg_stat_statements_reset();
301
$ pgbench -c10 -t300 -M prepared bench
304
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
305
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
306
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
307
-[ RECORD 1 ]---------------------------------------------------------------------
308
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
310
total_time | 9.60900100000002
312
hit_percent | 99.9778970000200936
313
-[ RECORD 2 ]---------------------------------------------------------------------
314
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
316
total_time | 8.015156
318
hit_percent | 99.9731126579631345
319
-[ RECORD 3 ]---------------------------------------------------------------------
320
query | copy pgbench_accounts from stdin
322
total_time | 0.310624
324
hit_percent | 0.30395136778115501520
325
-[ RECORD 4 ]---------------------------------------------------------------------
326
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
328
total_time | 0.271741999999997
330
hit_percent | 93.7968855088209426
331
-[ RECORD 5 ]---------------------------------------------------------------------
332
query | alter table pgbench_accounts add primary key (aid)
336
hit_percent | 34.4947735191637631
341
<title>Author</title>
344
Takahiro Itagaki <email>itagaki.takahiro@oss.ntt.co.jp</email>