doc/src/sgml/pgstatstatements.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="pgstatstatements">
   4  <title>pg_stat_statements</title>
   5
   6  <indexterm zone="pgstatstatements">
   7   <primary>pg_stat_statements</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pg_stat_statements</filename> module provides a means for
  12   tracking execution statistics of all SQL statements executed by a server.
  13  </para>
  14
  15  <para>
  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.
  20  </para>
  21
  22  <sect2>
  23   <title>The <structname>pg_stat_statements</structname> view</title>
  24
  25   <para>
  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">.
  31   </para>
  32
  33   <table id="pgstatstatements-columns">
  34    <title><structname>pg_stat_statements</> columns</title>
  35
  36    <tgroup cols="4">
  37     <thead>
  38      <row>
  39       <entry>Name</entry>
  40       <entry>Type</entry>
  41       <entry>References</entry>
  42       <entry>Description</entry>
  43      </row>
  44     </thead>
  45     <tbody>
  46      <row>
  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>
  51      </row>
  52
  53      <row>
  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>
  58      </row>
  59
  60     <row>
  61       <entry><structfield>query</structfield></entry>
  62       <entry><type>text</type></entry>
  63       <entry></entry>
  64       <entry>Text of the statement (up to <xref linkend="guc-track-activity-query-size"> bytes)</entry>
  65      </row>
  66
  67      <row>
  68       <entry><structfield>calls</structfield></entry>
  69       <entry><type>bigint</type></entry>
  70       <entry></entry>
  71       <entry>Number of times executed</entry>
  72      </row>
  73
  74      <row>
  75       <entry><structfield>total_time</structfield></entry>
  76       <entry><type>double precision</type></entry>
  77       <entry></entry>
  78       <entry>Total time spent in the statement, in seconds</entry>
  79      </row>
  80
  81      <row>
  82       <entry><structfield>rows</structfield></entry>
  83       <entry><type>bigint</type></entry>
  84       <entry></entry>
  85       <entry>Total number of rows retrieved or affected by the statement</entry>
  86      </row>
  87
  88     </tbody>
  89    </tgroup>
  90   </table>
  91
  92   <para>
  93    This view, and the function <function>pg_stat_statements_reset</>,
  94    are available only in databases they have been specifically installed into
  95    by running the <filename>pg_stat_statements.sql</> install script.
  96    However, statistics are tracked across all databases of the server
  97    whenever the <filename>pg_stat_statements</filename> module is loaded
  98    into the server, regardless of presence of the view.
  99   </para>
 100
 101   <para>
 102    For security reasons, non-superusers are not allowed to see the text of
 103    queries executed by other users.  They can see the statistics, however,
 104    if the view has been installed in their database.
 105   </para>
 106
 107   <para>
 108    Note that statements are considered the same if they have the same text,
 109    regardless of the values of any out-of-line parameters used in the
 110    statement.  Using out-of-line parameters will help to group statements
 111    together and may make the statistics more useful.
 112   </para>
 113  </sect2>
 114
 115  <sect2>
 116   <title>Functions</title>
 117
 118   <variablelist>
 119    <varlistentry>
 120     <term>
 121      <function>pg_stat_statements_reset() returns void</function>
 122     </term>
 123
 124     <listitem>
 125      <para>
 126       <function>pg_stat_statements_reset</function> discards all statistics
 127       gathered so far by <filename>pg_stat_statements</>.
 128       By default, this function can only be executed by superusers.
 129      </para>
 130     </listitem>
 131    </varlistentry>
 132
 133   </variablelist>
 134  </sect2>
 135
 136  <sect2>
 137   <title>Configuration parameters</title>
 138
 139   <variablelist>
 140    <varlistentry>
 141     <term>
 142      <varname>pg_stat_statements.max</varname> (<type>integer</type>)
 143     </term>
 144
 145     <listitem>
 146      <para>
 147       <varname>pg_stat_statements.max</varname> is the maximum number of
 148       statements tracked by the module (i.e., the maximum number of rows
 149       in the <structname>pg_stat_statements</> view).  If more distinct
 150       statements than that are observed, information about the least-executed
 151       statements is discarded.
 152       The default value is 1000.
 153       This parameter can only be set at server start.
 154      </para>
 155     </listitem>
 156    </varlistentry>
 157
 158    <varlistentry>
 159     <term>
 160      <varname>pg_stat_statements.track</varname> (<type>enum</type>)
 161     </term>
 162
 163     <listitem>
 164      <para>
 165       <varname>pg_stat_statements.track</varname> controls which statements
 166       are counted by the module.
 167       Specify <literal>top</> to track top-level statements (those issued
 168       directly by clients), <literal>all</> to also track nested statements
 169       (such as statements invoked within functions), or <literal>none</> to
 170       disable.
 171       The default value is <literal>top</>.
 172       Only superusers can change this setting.
 173      </para>
 174     </listitem>
 175    </varlistentry>
 176
 177    <varlistentry>
 178     <term>
 179      <varname>pg_stat_statements.save</varname> (<type>boolean</type>)
 180     </term>
 181
 182     <listitem>
 183      <para>
 184       <varname>pg_stat_statements.save</varname> specifies whether to
 185       save statement statistics across server shutdowns.
 186       If it is <literal>off</> then statistics are not saved at
 187       shutdown nor reloaded at server start.
 188       The default value is <literal>on</>.
 189       This parameter can only be set in the <filename>postgresql.conf</>
 190       file or on the server command line.
 191      </para>
 192     </listitem>
 193    </varlistentry>
 194   </variablelist>
 195
 196   <para>
 197    The module requires additional shared memory amounting to about
 198    <varname>pg_stat_statements.max</varname> <literal>*</>
 199    <xref linkend="guc-track-activity-query-size"> bytes.  Note that this
 200    memory is consumed whenever the module is loaded, even if
 201    <varname>pg_stat_statements.track</> is set to <literal>none</>.
 202   </para>
 203
 204   <para>
 205    In order to set any of these parameters in your
 206    <filename>postgresql.conf</> file,
 207    you will need to add <literal>pg_stat_statements</> to
 208    <xref linkend="guc-custom-variable-classes">.  Typical usage might be:
 209   </para>
 210
 211   <programlisting>
 212 # postgresql.conf
 213 shared_preload_libraries = 'pg_stat_statements'
 214
 215 custom_variable_classes = 'pg_stat_statements'
 216 pg_stat_statements.max = 10000
 217 pg_stat_statements.track = all
 218   </programlisting>
 219  </sect2>
 220
 221  <sect2>
 222   <title>Sample output</title>
 223
 224   <programlisting>
 225 $ pgbench -i bench
 226
 227 postgres=# SELECT pg_stat_statements_reset();
 228
 229 $ pgbench -c10 -t300 -M prepared bench
 230
 231 postgres=# \x
 232 postgres=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
 233 -[ RECORD 1 ]------------------------------------------------------------
 234 userid     | 10
 235 dbid       | 63781
 236 query      | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;
 237 calls      | 3000
 238 total_time | 20.716706
 239 rows       | 3000
 240 -[ RECORD 2 ]------------------------------------------------------------
 241 userid     | 10
 242 dbid       | 63781
 243 query      | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2;
 244 calls      | 3000
 245 total_time | 17.1107649999999
 246 rows       | 3000
 247 -[ RECORD 3 ]------------------------------------------------------------
 248 userid     | 10
 249 dbid       | 63781
 250 query      | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2;
 251 calls      | 3000
 252 total_time | 0.645601
 253 rows       | 3000
 254   </programlisting>
 255  </sect2>
 256
 257  <sect2>
 258   <title>Author</title>
 259
 260   <para>
 261    Takahiro Itagaki <email>itagaki.takahiro@oss.ntt.co.jp</email>
 262   </para>
 263  </sect2>
 264
 265 </sect1>