doc/src/sgml/pgstatstatements.sgml

   1 <!-- doc/src/sgml/pgstatstatements.sgml -->
   2
   3 <sect1 id="pgstatstatements" xreflabel="pg_stat_statements">
   4  <title>pg_stat_statements &mdash; track statistics of SQL planning and execution</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 planning and execution statistics of all SQL statements executed by
  13   a server.
  14  </para>
  15
  16  <para>
  17   The module must be loaded by adding <literal>pg_stat_statements</literal> to
  18   <xref linkend="guc-shared-preload-libraries"/> in
  19   <filename>postgresql.conf</filename>, because it requires additional shared memory.
  20   This means that a server restart is needed to add or remove the module.
  21   In addition, query identifier calculation must be enabled in order for the
  22   module to be active, which is done automatically if <xref linkend="guc-compute-query-id"/>
  23   is set to <literal>auto</literal> or <literal>on</literal>, or any third-party
  24   module that calculates query identifiers is loaded.
  25  </para>
  26
  27  <para>
  28    When <filename>pg_stat_statements</filename> is active, it tracks
  29    statistics across all databases of the server.  To access and manipulate
  30    these statistics, the module provides views
  31    <structname>pg_stat_statements</structname> and
  32    <structname>pg_stat_statements_info</structname>,
  33    and the utility functions <function>pg_stat_statements_reset</function> and
  34    <function>pg_stat_statements</function>.  These are not available globally but
  35    can be enabled for a specific database with
  36    <command>CREATE EXTENSION pg_stat_statements</command>.
  37  </para>
  38
  39  <sect2 id="pgstatstatements-pg-stat-statements">
  40   <title>The <structname>pg_stat_statements</structname> View</title>
  41
  42   <para>
  43    The statistics gathered by the module are made available via a
  44    view named <structname>pg_stat_statements</structname>.  This view
  45    contains one row for each distinct combination of database ID, user
  46    ID, query ID and whether it's a top-level statement or not (up to
  47    the maximum number of distinct statements that the module can track).
  48    The columns of the view are shown in
  49    <xref linkend="pgstatstatements-columns"/>.
  50   </para>
  51
  52   <table id="pgstatstatements-columns">
  53    <title><structname>pg_stat_statements</structname> Columns</title>
  54    <tgroup cols="1">
  55     <thead>
  56      <row>
  57       <entry role="catalog_table_entry"><para role="column_definition">
  58        Column Type
  59       </para>
  60       <para>
  61        Description
  62       </para></entry>
  63      </row>
  64     </thead>
  65
  66     <tbody>
  67      <row>
  68       <entry role="catalog_table_entry"><para role="column_definition">
  69        <structfield>userid</structfield> <type>oid</type>
  70        (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
  71       </para>
  72       <para>
  73        OID of user who executed the statement
  74       </para></entry>
  75      </row>
  76
  77      <row>
  78       <entry role="catalog_table_entry"><para role="column_definition">
  79        <structfield>dbid</structfield> <type>oid</type>
  80        (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
  81       </para>
  82       <para>
  83        OID of database in which the statement was executed
  84       </para></entry>
  85      </row>
  86
  87      <row>
  88       <entry role="catalog_table_entry"><para role="column_definition">
  89        <structfield>toplevel</structfield> <type>bool</type>
  90       </para>
  91       <para>
  92        True if the query was executed as a top-level statement
  93        (always true if <varname>pg_stat_statements.track</varname> is set to
  94        <literal>top</literal>)
  95       </para></entry>
  96      </row>
  97
  98      <row>
  99       <entry role="catalog_table_entry"><para role="column_definition">
 100        <structfield>queryid</structfield> <type>bigint</type>
 101       </para>
 102       <para>
 103        Hash code to identify identical normalized queries.
 104       </para></entry>
 105      </row>
 106
 107      <row>
 108       <entry role="catalog_table_entry"><para role="column_definition">
 109        <structfield>query</structfield> <type>text</type>
 110       </para>
 111       <para>
 112        Text of a representative statement
 113       </para></entry>
 114      </row>
 115
 116      <row>
 117       <entry role="catalog_table_entry"><para role="column_definition">
 118        <structfield>plans</structfield> <type>bigint</type>
 119       </para>
 120       <para>
 121        Number of times the statement was planned
 122        (if <varname>pg_stat_statements.track_planning</varname> is enabled,
 123        otherwise zero)
 124       </para></entry>
 125      </row>
 126
 127      <row>
 128       <entry role="catalog_table_entry"><para role="column_definition">
 129        <structfield>total_plan_time</structfield> <type>double precision</type>
 130       </para>
 131       <para>
 132        Total time spent planning the statement, in milliseconds
 133        (if <varname>pg_stat_statements.track_planning</varname> is enabled,
 134        otherwise zero)
 135       </para></entry>
 136      </row>
 137
 138      <row>
 139       <entry role="catalog_table_entry"><para role="column_definition">
 140        <structfield>min_plan_time</structfield> <type>double precision</type>
 141       </para>
 142       <para>
 143        Minimum time spent planning the statement, in milliseconds.
 144        This field will be zero if <varname>pg_stat_statements.track_planning</varname>
 145        is disabled, or if the counter has been reset using the
 146        <function>pg_stat_statements_reset</function> function with the
 147        <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
 148        and never been planned since.
 149       </para></entry>
 150      </row>
 151
 152      <row>
 153       <entry role="catalog_table_entry"><para role="column_definition">
 154        <structfield>max_plan_time</structfield> <type>double precision</type>
 155       </para>
 156       <para>
 157        Maximum time spent planning the statement, in milliseconds.
 158        This field will be zero if <varname>pg_stat_statements.track_planning</varname>
 159        is disabled, or if the counter has been reset using the
 160        <function>pg_stat_statements_reset</function> function with the
 161        <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
 162        and never been planned since.
 163       </para></entry>
 164      </row>
 165
 166      <row>
 167       <entry role="catalog_table_entry"><para role="column_definition">
 168        <structfield>mean_plan_time</structfield> <type>double precision</type>
 169       </para>
 170       <para>
 171        Mean time spent planning the statement, in milliseconds
 172        (if <varname>pg_stat_statements.track_planning</varname> is enabled,
 173        otherwise zero)
 174       </para></entry>
 175      </row>
 176
 177      <row>
 178       <entry role="catalog_table_entry"><para role="column_definition">
 179        <structfield>stddev_plan_time</structfield> <type>double precision</type>
 180       </para>
 181       <para>
 182        Population standard deviation of time spent planning the statement,
 183        in milliseconds
 184        (if <varname>pg_stat_statements.track_planning</varname> is enabled,
 185        otherwise zero)
 186       </para></entry>
 187      </row>
 188
 189      <row>
 190       <entry role="catalog_table_entry"><para role="column_definition">
 191        <structfield>calls</structfield> <type>bigint</type>
 192       </para>
 193       <para>
 194        Number of times the statement was executed
 195       </para></entry>
 196      </row>
 197
 198      <row>
 199       <entry role="catalog_table_entry"><para role="column_definition">
 200        <structfield>total_exec_time</structfield> <type>double precision</type>
 201       </para>
 202       <para>
 203        Total time spent executing the statement, in milliseconds
 204       </para></entry>
 205      </row>
 206
 207      <row>
 208       <entry role="catalog_table_entry"><para role="column_definition">
 209        <structfield>min_exec_time</structfield> <type>double precision</type>
 210       </para>
 211       <para>
 212        Minimum time spent executing the statement, in milliseconds,
 213        this field will be zero until this statement
 214        is executed first time after reset performed by the
 215        <function>pg_stat_statements_reset</function> function with the
 216        <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
 217       </para></entry>
 218      </row>
 219
 220      <row>
 221       <entry role="catalog_table_entry"><para role="column_definition">
 222        <structfield>max_exec_time</structfield> <type>double precision</type>
 223       </para>
 224       <para>
 225        Maximum time spent executing the statement, in milliseconds,
 226        this field will be zero until this statement
 227        is executed first time after reset performed by the
 228        <function>pg_stat_statements_reset</function> function with the
 229        <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
 230       </para></entry>
 231      </row>
 232
 233      <row>
 234       <entry role="catalog_table_entry"><para role="column_definition">
 235        <structfield>mean_exec_time</structfield> <type>double precision</type>
 236       </para>
 237       <para>
 238        Mean time spent executing the statement, in milliseconds
 239       </para></entry>
 240      </row>
 241
 242      <row>
 243       <entry role="catalog_table_entry"><para role="column_definition">
 244        <structfield>stddev_exec_time</structfield> <type>double precision</type>
 245       </para>
 246       <para>
 247        Population standard deviation of time spent executing the statement, in milliseconds
 248       </para></entry>
 249      </row>
 250
 251      <row>
 252       <entry role="catalog_table_entry"><para role="column_definition">
 253        <structfield>rows</structfield> <type>bigint</type>
 254       </para>
 255       <para>
 256        Total number of rows retrieved or affected by the statement
 257       </para></entry>
 258      </row>
 259
 260      <row>
 261       <entry role="catalog_table_entry"><para role="column_definition">
 262        <structfield>shared_blks_hit</structfield> <type>bigint</type>
 263       </para>
 264       <para>
 265        Total number of shared block cache hits by the statement
 266       </para></entry>
 267      </row>
 268
 269      <row>
 270       <entry role="catalog_table_entry"><para role="column_definition">
 271        <structfield>shared_blks_read</structfield> <type>bigint</type>
 272       </para>
 273       <para>
 274        Total number of shared blocks read by the statement
 275       </para></entry>
 276      </row>
 277
 278      <row>
 279       <entry role="catalog_table_entry"><para role="column_definition">
 280        <structfield>shared_blks_dirtied</structfield> <type>bigint</type>
 281       </para>
 282       <para>
 283        Total number of shared blocks dirtied by the statement
 284       </para></entry>
 285      </row>
 286
 287      <row>
 288       <entry role="catalog_table_entry"><para role="column_definition">
 289        <structfield>shared_blks_written</structfield> <type>bigint</type>
 290       </para>
 291       <para>
 292        Total number of shared blocks written by the statement
 293       </para></entry>
 294      </row>
 295
 296      <row>
 297       <entry role="catalog_table_entry"><para role="column_definition">
 298        <structfield>local_blks_hit</structfield> <type>bigint</type>
 299       </para>
 300       <para>
 301        Total number of local block cache hits by the statement
 302       </para></entry>
 303      </row>
 304
 305      <row>
 306       <entry role="catalog_table_entry"><para role="column_definition">
 307        <structfield>local_blks_read</structfield> <type>bigint</type>
 308       </para>
 309       <para>
 310        Total number of local blocks read by the statement
 311       </para></entry>
 312      </row>
 313
 314      <row>
 315       <entry role="catalog_table_entry"><para role="column_definition">
 316        <structfield>local_blks_dirtied</structfield> <type>bigint</type>
 317       </para>
 318       <para>
 319        Total number of local blocks dirtied by the statement
 320       </para></entry>
 321      </row>
 322
 323      <row>
 324       <entry role="catalog_table_entry"><para role="column_definition">
 325        <structfield>local_blks_written</structfield> <type>bigint</type>
 326       </para>
 327       <para>
 328        Total number of local blocks written by the statement
 329       </para></entry>
 330      </row>
 331
 332      <row>
 333       <entry role="catalog_table_entry"><para role="column_definition">
 334        <structfield>temp_blks_read</structfield> <type>bigint</type>
 335       </para>
 336       <para>
 337        Total number of temp blocks read by the statement
 338       </para></entry>
 339      </row>
 340
 341      <row>
 342       <entry role="catalog_table_entry"><para role="column_definition">
 343        <structfield>temp_blks_written</structfield> <type>bigint</type>
 344       </para>
 345       <para>
 346        Total number of temp blocks written by the statement
 347       </para></entry>
 348      </row>
 349
 350      <row>
 351       <entry role="catalog_table_entry"><para role="column_definition">
 352        <structfield>shared_blk_read_time</structfield> <type>double precision</type>
 353       </para>
 354       <para>
 355        Total time the statement spent reading shared blocks, in milliseconds
 356        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
 357       </para></entry>
 358      </row>
 359
 360      <row>
 361       <entry role="catalog_table_entry"><para role="column_definition">
 362        <structfield>shared_blk_write_time</structfield> <type>double precision</type>
 363       </para>
 364       <para>
 365        Total time the statement spent writing shared blocks, in milliseconds
 366        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
 367       </para></entry>
 368      </row>
 369
 370      <row>
 371       <entry role="catalog_table_entry"><para role="column_definition">
 372        <structfield>local_blk_read_time</structfield> <type>double precision</type>
 373       </para>
 374       <para>
 375        Total time the statement spent reading local blocks, in milliseconds
 376        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
 377       </para></entry>
 378      </row>
 379
 380      <row>
 381       <entry role="catalog_table_entry"><para role="column_definition">
 382        <structfield>local_blk_write_time</structfield> <type>double precision</type>
 383       </para>
 384       <para>
 385        Total time the statement spent writing local blocks, in milliseconds
 386        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
 387       </para></entry>
 388      </row>
 389
 390      <row>
 391       <entry role="catalog_table_entry"><para role="column_definition">
 392        <structfield>temp_blk_read_time</structfield> <type>double precision</type>
 393       </para>
 394       <para>
 395        Total time the statement spent reading temporary file blocks, in
 396        milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
 397        otherwise zero)
 398       </para></entry>
 399      </row>
 400
 401      <row>
 402       <entry role="catalog_table_entry"><para role="column_definition">
 403        <structfield>temp_blk_write_time</structfield> <type>double precision</type>
 404       </para>
 405       <para>
 406        Total time the statement spent writing temporary file blocks, in
 407        milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
 408        otherwise zero)
 409       </para></entry>
 410      </row>
 411
 412      <row>
 413       <entry role="catalog_table_entry"><para role="column_definition">
 414        <structfield>wal_records</structfield> <type>bigint</type>
 415       </para>
 416       <para>
 417        Total number of WAL records generated by the statement
 418       </para></entry>
 419      </row>
 420
 421      <row>
 422       <entry role="catalog_table_entry"><para role="column_definition">
 423        <structfield>wal_fpi</structfield> <type>bigint</type>
 424       </para>
 425       <para>
 426        Total number of WAL full page images generated by the statement
 427       </para></entry>
 428      </row>
 429
 430      <row>
 431       <entry role="catalog_table_entry"><para role="column_definition">
 432        <structfield>wal_bytes</structfield> <type>numeric</type>
 433       </para>
 434       <para>
 435        Total amount of WAL generated by the statement in bytes
 436       </para></entry>
 437      </row>
 438
 439      <row>
 440       <entry role="catalog_table_entry"><para role="column_definition">
 441        <structfield>jit_functions</structfield> <type>bigint</type>
 442       </para>
 443       <para>
 444        Total number of functions JIT-compiled by the statement
 445       </para></entry>
 446      </row>
 447
 448      <row>
 449       <entry role="catalog_table_entry"><para role="column_definition">
 450        <structfield>jit_generation_time</structfield> <type>double precision</type>
 451       </para>
 452       <para>
 453        Total time spent by the statement on generating JIT code, in milliseconds
 454       </para></entry>
 455      </row>
 456
 457      <row>
 458       <entry role="catalog_table_entry"><para role="column_definition">
 459        <structfield>jit_inlining_count</structfield> <type>bigint</type>
 460       </para>
 461       <para>
 462        Number of times functions have been inlined
 463       </para></entry>
 464      </row>
 465
 466      <row>
 467       <entry role="catalog_table_entry"><para role="column_definition">
 468        <structfield>jit_inlining_time</structfield> <type>double precision</type>
 469       </para>
 470       <para>
 471        Total time spent by the statement on inlining functions, in milliseconds
 472       </para></entry>
 473      </row>
 474
 475      <row>
 476       <entry role="catalog_table_entry"><para role="column_definition">
 477        <structfield>jit_optimization_count</structfield> <type>bigint</type>
 478       </para>
 479       <para>
 480        Number of times the statement has been optimized
 481       </para></entry>
 482      </row>
 483
 484      <row>
 485       <entry role="catalog_table_entry"><para role="column_definition">
 486        <structfield>jit_optimization_time</structfield> <type>double precision</type>
 487       </para>
 488       <para>
 489        Total time spent by the statement on optimizing, in milliseconds
 490       </para></entry>
 491      </row>
 492
 493      <row>
 494       <entry role="catalog_table_entry"><para role="column_definition">
 495        <structfield>jit_emission_count</structfield> <type>bigint</type>
 496       </para>
 497       <para>
 498        Number of times code has been emitted
 499       </para></entry>
 500      </row>
 501
 502      <row>
 503       <entry role="catalog_table_entry"><para role="column_definition">
 504        <structfield>jit_emission_time</structfield> <type>double precision</type>
 505       </para>
 506       <para>
 507        Total time spent by the statement on emitting code, in milliseconds
 508       </para></entry>
 509      </row>
 510
 511      <row>
 512       <entry role="catalog_table_entry"><para role="column_definition">
 513        <structfield>jit_deform_count</structfield> <type>bigint</type>
 514       </para>
 515       <para>
 516        Total number of tuple deform functions JIT-compiled by the statement
 517       </para></entry>
 518      </row>
 519
 520      <row>
 521       <entry role="catalog_table_entry"><para role="column_definition">
 522        <structfield>jit_deform_time</structfield> <type>double precision</type>
 523       </para>
 524       <para>
 525        Total time spent by the statement on JIT-compiling tuple deform
 526        functions, in milliseconds
 527       </para></entry>
 528      </row>
 529
 530      <row>
 531       <entry role="catalog_table_entry"><para role="column_definition">
 532        <structfield>stats_since</structfield> <type>timestamp with time zone</type>
 533       </para>
 534       <para>
 535        Time at which statistics gathering started for this statement
 536       </para></entry>
 537      </row>
 538
 539      <row>
 540       <entry role="catalog_table_entry"><para role="column_definition">
 541        <structfield>minmax_stats_since</structfield> <type>timestamp with time zone</type>
 542       </para>
 543       <para>
 544        Time at which min/max statistics gathering started for this
 545        statement (fields <structfield>min_plan_time</structfield>,
 546        <structfield>max_plan_time</structfield>,
 547        <structfield>min_exec_time</structfield> and
 548        <structfield>max_exec_time</structfield>)
 549       </para></entry>
 550      </row>
 551     </tbody>
 552    </tgroup>
 553   </table>
 554
 555   <para>
 556    For security reasons, only superusers and roles with privileges of the
 557    <literal>pg_read_all_stats</literal> role are allowed to see the SQL text and
 558    <structfield>queryid</structfield> of queries executed by other users.
 559    Other users can see the statistics, however, if the view has been installed
 560    in their database.
 561   </para>
 562
 563   <para>
 564    Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
 565    <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>)
 566    and utility commands are combined into a single
 567    <structname>pg_stat_statements</structname> entry whenever they have identical query
 568    structures according to an internal hash calculation.  Typically, two
 569    queries will be considered the same for this purpose if they are
 570    semantically equivalent except for the values of literal constants
 571    appearing in the query.
 572   </para>
 573
 574   <note>
 575    <para>
 576     The following details about constant replacement and
 577     <structfield>queryid</structfield> only apply when <xref
 578     linkend="guc-compute-query-id"/> is enabled.  If you use an external
 579     module instead to compute <structfield>queryid</structfield>, you
 580     should refer to its documentation for details.
 581    </para>
 582   </note>
 583
 584   <para>
 585    When a constant's value has been ignored for purposes of matching the query
 586    to other queries, the constant is replaced by a parameter symbol, such
 587    as <literal>$1</literal>, in the <structname>pg_stat_statements</structname>
 588    display.
 589    The rest of the query text is that of the first query that had the
 590    particular <structfield>queryid</structfield> hash value associated with the
 591    <structname>pg_stat_statements</structname> entry.
 592   </para>
 593
 594   <para>
 595    Queries on which normalization can be applied may be observed with constant
 596    values in <structname>pg_stat_statements</structname>, especially when there
 597    is a high rate of entry deallocations. To reduce the likelihood of this
 598    happening, consider increasing <varname>pg_stat_statements.max</varname>.
 599    The <structname>pg_stat_statements_info</structname> view, discussed below
 600    in <xref linkend="pgstatstatements-pg-stat-statements-info"/>,
 601    provides statistics about entry deallocations.
 602   </para>
 603
 604   <para>
 605    In some cases, queries with visibly different texts might get merged into a
 606    single <structname>pg_stat_statements</structname> entry.  Normally this will happen
 607    only for semantically equivalent queries, but there is a small chance of
 608    hash collisions causing unrelated queries to be merged into one entry.
 609    (This cannot happen for queries belonging to different users or databases,
 610    however.)
 611   </para>
 612
 613   <para>
 614    Since the <structfield>queryid</structfield> hash value is computed on the
 615    post-parse-analysis representation of the queries, the opposite is
 616    also possible: queries with identical texts might appear as
 617    separate entries, if they have different meanings as a result of
 618    factors such as different <varname>search_path</varname> settings.
 619   </para>
 620
 621   <para>
 622    Consumers of <structname>pg_stat_statements</structname> may wish to use
 623    <structfield>queryid</structfield> (perhaps in combination with
 624    <structfield>dbid</structfield> and <structfield>userid</structfield>) as a more stable
 625    and reliable identifier for each entry than its query text.
 626    However, it is important to understand that there are only limited
 627    guarantees around the stability of the <structfield>queryid</structfield> hash
 628    value.  Since the identifier is derived from the
 629    post-parse-analysis tree, its value is a function of, among other
 630    things, the internal object identifiers appearing in this representation.
 631    This has some counterintuitive implications.  For example,
 632    <filename>pg_stat_statements</filename> will consider two apparently-identical
 633    queries to be distinct, if they reference a table that was dropped
 634    and recreated between the executions of the two queries.
 635    The hashing process is also sensitive to differences in
 636    machine architecture and other facets of the platform.
 637    Furthermore, it is not safe to assume that <structfield>queryid</structfield>
 638    will be stable across major versions of <productname>PostgreSQL</productname>.
 639   </para>
 640
 641   <para>
 642    Two servers participating in replication based on physical WAL replay can
 643    be expected to have identical <structfield>queryid</structfield> values for
 644    the same query.  However, logical replication schemes do not promise to
 645    keep replicas identical in all relevant details, so
 646    <structfield>queryid</structfield> will not be a useful identifier for
 647    accumulating costs across a set of logical replicas.
 648    If in doubt, direct testing is recommended.
 649   </para>
 650
 651   <para>
 652    Generally, it can be assumed that <structfield>queryid</structfield> values
 653    are stable between minor version releases of <productname>PostgreSQL</productname>,
 654    providing that instances are running on the same machine architecture and
 655    the catalog metadata details match.  Compatibility will only be broken
 656    between minor versions as a last resort.
 657   </para>
 658
 659   <para>
 660    The parameter symbols used to replace constants in
 661    representative query texts start from the next number after the
 662    highest <literal>$</literal><replaceable>n</replaceable> parameter in the original query
 663    text, or <literal>$1</literal> if there was none.  It's worth noting that in
 664    some cases there may be hidden parameter symbols that affect this
 665    numbering.  For example, <application>PL/pgSQL</application> uses hidden parameter
 666    symbols to insert values of function local variables into queries, so that
 667    a <application>PL/pgSQL</application> statement like <literal>SELECT i + 1 INTO j</literal>
 668    would have representative text like <literal>SELECT i + $2</literal>.
 669   </para>
 670
 671   <para>
 672    The representative query texts are kept in an external disk file, and do
 673    not consume shared memory.  Therefore, even very lengthy query texts can
 674    be stored successfully.  However, if many long query texts are
 675    accumulated, the external file might grow unmanageably large.  As a
 676    recovery method if that happens, <filename>pg_stat_statements</filename> may
 677    choose to discard the query texts, whereupon all existing entries in
 678    the <structname>pg_stat_statements</structname> view will show
 679    null <structfield>query</structfield> fields, though the statistics associated with
 680    each <structfield>queryid</structfield> are preserved.  If this happens, consider
 681    reducing <varname>pg_stat_statements.max</varname> to prevent
 682    recurrences.
 683   </para>
 684
 685   <para>
 686    <structfield>plans</structfield> and <structfield>calls</structfield> aren't
 687    always expected to match because planning and execution statistics are
 688    updated at their respective end phase, and only for successful operations.
 689    For example, if a statement is successfully planned but fails during
 690    the execution phase, only its planning statistics will be updated.
 691    If planning is skipped because a cached plan is used, only its execution
 692    statistics will be updated.
 693   </para>
 694  </sect2>
 695
 696  <sect2 id="pgstatstatements-pg-stat-statements-info">
 697   <title>The <structname>pg_stat_statements_info</structname> View</title>
 698
 699   <indexterm>
 700    <primary>pg_stat_statements_info</primary>
 701   </indexterm>
 702
 703   <para>
 704    The statistics of the <filename>pg_stat_statements</filename> module
 705    itself are tracked and made available via a view named
 706    <structname>pg_stat_statements_info</structname>.  This view contains
 707    only a single row.  The columns of the view are shown in
 708    <xref linkend="pgstatstatementsinfo-columns"/>.
 709   </para>
 710
 711   <table id="pgstatstatementsinfo-columns">
 712    <title><structname>pg_stat_statements_info</structname> Columns</title>
 713    <tgroup cols="1">
 714     <thead>
 715      <row>
 716       <entry role="catalog_table_entry"><para role="column_definition">
 717        Column Type
 718       </para>
 719       <para>
 720        Description
 721       </para></entry>
 722      </row>
 723     </thead>
 724
 725     <tbody>
 726      <row>
 727       <entry role="catalog_table_entry"><para role="column_definition">
 728        <structfield>dealloc</structfield> <type>bigint</type>
 729       </para>
 730       <para>
 731        Total number of times <structname>pg_stat_statements</structname>
 732        entries about the least-executed statements were deallocated
 733        because more distinct statements than
 734        <varname>pg_stat_statements.max</varname> were observed
 735       </para></entry>
 736      </row>
 737      <row>
 738       <entry role="catalog_table_entry"><para role="column_definition">
 739        <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
 740       </para>
 741       <para>
 742        Time at which all statistics in the
 743        <structname>pg_stat_statements</structname> view were last reset.
 744       </para></entry>
 745      </row>
 746
 747     </tbody>
 748    </tgroup>
 749   </table>
 750  </sect2>
 751
 752  <sect2 id="pgstatstatements-funcs">
 753   <title>Functions</title>
 754
 755   <variablelist>
 756    <varlistentry>
 757     <term>
 758      <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid
 759      bigint, minmax_only boolean) returns timestamp with time zone</function>
 760      <indexterm>
 761       <primary>pg_stat_statements_reset</primary>
 762      </indexterm>
 763     </term>
 764
 765     <listitem>
 766      <para>
 767       <function>pg_stat_statements_reset</function> discards statistics
 768       gathered so far by <filename>pg_stat_statements</filename> corresponding
 769       to the specified <structfield>userid</structfield>, <structfield>dbid</structfield>
 770       and <structfield>queryid</structfield>.  If any of the parameters are not
 771       specified, the default value <literal>0</literal>(invalid) is used for
 772       each of them and the statistics that match with other parameters will be
 773       reset.  If no parameter is specified or all the specified parameters are
 774       <literal>0</literal>(invalid), it will discard all statistics.
 775       If all statistics in the <filename>pg_stat_statements</filename>
 776       view are discarded, it will also reset the statistics in the
 777       <structname>pg_stat_statements_info</structname> view.
 778       When <structfield>minmax_only</structfield> is <literal>true</literal> only the
 779       values of minimum and maximum planning and execution time will be reset (i.e.
 780       <structfield>min_plan_time</structfield>, <structfield>max_plan_time</structfield>,
 781       <structfield>min_exec_time</structfield> and <structfield>max_exec_time</structfield>
 782       fields). The default value for <structfield>minmax_only</structfield> parameter is
 783       <literal>false</literal>. Time of last min/max reset performed is shown in
 784       <structfield>minmax_stats_since</structfield> field of the
 785       <structname>pg_stat_statements</structname> view.
 786       This function returns the time of a reset. This time is saved to
 787       <structfield>stats_reset</structfield> field of
 788       <structname>pg_stat_statements_info</structname> view or to
 789       <structfield>minmax_stats_since</structfield> field of the
 790       <structname>pg_stat_statements</structname> view if the corresponding reset was
 791       actually performed.
 792       By default, this function can only be executed by superusers.
 793       Access may be granted to others using <command>GRANT</command>.
 794      </para>
 795     </listitem>
 796    </varlistentry>
 797
 798    <varlistentry>
 799     <term>
 800      <function>pg_stat_statements(showtext boolean) returns setof record</function>
 801      <indexterm>
 802       <primary>pg_stat_statements</primary>
 803       <secondary>function</secondary>
 804      </indexterm>
 805     </term>
 806
 807     <listitem>
 808      <para>
 809       The <structname>pg_stat_statements</structname> view is defined in
 810       terms of a function also named <function>pg_stat_statements</function>.
 811       It is possible for clients to call
 812       the <function>pg_stat_statements</function> function directly, and by
 813       specifying <literal>showtext := false</literal> have query text be
 814       omitted (that is, the <literal>OUT</literal> argument that corresponds
 815       to the view's <structfield>query</structfield> column will return nulls).  This
 816       feature is intended to support external tools that might wish to avoid
 817       the overhead of repeatedly retrieving query texts of indeterminate
 818       length.  Such tools can instead cache the first query text observed
 819       for each entry themselves, since that is
 820       all <filename>pg_stat_statements</filename> itself does, and then retrieve
 821       query texts only as needed.  Since the server stores query texts in a
 822       file, this approach may reduce physical I/O for repeated examination
 823       of the <structname>pg_stat_statements</structname> data.
 824      </para>
 825     </listitem>
 826    </varlistentry>
 827   </variablelist>
 828  </sect2>
 829
 830  <sect2 id="pgstatstatements-config-params">
 831   <title>Configuration Parameters</title>
 832
 833   <variablelist>
 834    <varlistentry>
 835     <term>
 836      <varname>pg_stat_statements.max</varname> (<type>integer</type>)
 837      <indexterm>
 838       <primary><varname>pg_stat_statements.max</varname> configuration parameter</primary>
 839      </indexterm>
 840     </term>
 841
 842     <listitem>
 843      <para>
 844       <varname>pg_stat_statements.max</varname> is the maximum number of
 845       statements tracked by the module (i.e., the maximum number of rows
 846       in the <structname>pg_stat_statements</structname> view).  If more distinct
 847       statements than that are observed, information about the least-executed
 848       statements is discarded.  The number of times such information was
 849       discarded can be seen in the
 850       <structname>pg_stat_statements_info</structname> view.
 851       The default value is 5000.
 852       This parameter can only be set at server start.
 853      </para>
 854     </listitem>
 855    </varlistentry>
 856
 857    <varlistentry>
 858     <term>
 859      <varname>pg_stat_statements.track</varname> (<type>enum</type>)
 860      <indexterm>
 861       <primary><varname>pg_stat_statements.track</varname> configuration parameter</primary>
 862      </indexterm>
 863     </term>
 864
 865     <listitem>
 866      <para>
 867       <varname>pg_stat_statements.track</varname> controls which statements
 868       are counted by the module.
 869       Specify <literal>top</literal> to track top-level statements (those issued
 870       directly by clients), <literal>all</literal> to also track nested statements
 871       (such as statements invoked within functions), or <literal>none</literal> to
 872       disable statement statistics collection.
 873       The default value is <literal>top</literal>.
 874       Only superusers can change this setting.
 875      </para>
 876     </listitem>
 877    </varlistentry>
 878
 879    <varlistentry>
 880     <term>
 881      <varname>pg_stat_statements.track_utility</varname> (<type>boolean</type>)
 882      <indexterm>
 883       <primary><varname>pg_stat_statements.track_utility</varname> configuration parameter</primary>
 884      </indexterm>
 885     </term>
 886
 887     <listitem>
 888      <para>
 889       <varname>pg_stat_statements.track_utility</varname> controls whether
 890       utility commands are tracked by the module.  Utility commands are
 891       all those other than <command>SELECT</command>, <command>INSERT</command>,
 892       <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>.
 893       The default value is <literal>on</literal>.
 894       Only superusers can change this setting.
 895      </para>
 896     </listitem>
 897    </varlistentry>
 898
 899    <varlistentry>
 900     <term>
 901      <varname>pg_stat_statements.track_planning</varname> (<type>boolean</type>)
 902      <indexterm>
 903       <primary><varname>pg_stat_statements.track_planning</varname> configuration parameter</primary>
 904      </indexterm>
 905     </term>
 906
 907     <listitem>
 908      <para>
 909       <varname>pg_stat_statements.track_planning</varname> controls whether
 910       planning operations and duration are tracked by the module.
 911       Enabling this parameter may incur a noticeable performance penalty,
 912       especially when statements with identical query structure are executed
 913       by many concurrent connections which compete to update a small number of
 914       <structname>pg_stat_statements</structname> entries.
 915       The default value is <literal>off</literal>.
 916       Only superusers can change this setting.
 917      </para>
 918     </listitem>
 919    </varlistentry>
 920
 921    <varlistentry>
 922     <term>
 923      <varname>pg_stat_statements.save</varname> (<type>boolean</type>)
 924      <indexterm>
 925       <primary><varname>pg_stat_statements.save</varname> configuration parameter</primary>
 926      </indexterm>
 927     </term>
 928
 929     <listitem>
 930      <para>
 931       <varname>pg_stat_statements.save</varname> specifies whether to
 932       save statement statistics across server shutdowns.
 933       If it is <literal>off</literal> then statistics are not saved at
 934       shutdown nor reloaded at server start.
 935       The default value is <literal>on</literal>.
 936       This parameter can only be set in the <filename>postgresql.conf</filename>
 937       file or on the server command line.
 938      </para>
 939     </listitem>
 940    </varlistentry>
 941   </variablelist>
 942
 943   <para>
 944    The module requires additional shared memory proportional to
 945    <varname>pg_stat_statements.max</varname>.  Note that this
 946    memory is consumed whenever the module is loaded, even if
 947    <varname>pg_stat_statements.track</varname> is set to <literal>none</literal>.
 948   </para>
 949
 950   <para>
 951    These parameters must be set in <filename>postgresql.conf</filename>.
 952    Typical usage might be:
 953
 954 <programlisting>
 955 # postgresql.conf
 956 shared_preload_libraries = 'pg_stat_statements'
 957
 958 compute_query_id = on
 959 pg_stat_statements.max = 10000
 960 pg_stat_statements.track = all
 961 </programlisting>
 962   </para>
 963  </sect2>
 964
 965  <sect2 id="pgstatstatements-sample-output">
 966   <title>Sample Output</title>
 967
 968 <screen>
 969 bench=# SELECT pg_stat_statements_reset();
 970
 971 $ pgbench -i bench
 972 $ pgbench -c10 -t300 bench
 973
 974 bench=# \x
 975 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
 976                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
 977           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
 978 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;------------------
 979 query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
 980 calls           | 3000
 981 total_exec_time | 25565.855387
 982 rows            | 3000
 983 hit_percent     | 100.0000000000000000
 984 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;------------------
 985 query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 986 calls           | 3000
 987 total_exec_time | 20756.669379
 988 rows            | 3000
 989 hit_percent     | 100.0000000000000000
 990 -[ RECORD 3 ]---+--------------------------------------------------&zwsp;------------------
 991 query           | copy pgbench_accounts from stdin
 992 calls           | 1
 993 total_exec_time | 291.865911
 994 rows            | 100000
 995 hit_percent     | 100.0000000000000000
 996 -[ RECORD 4 ]---+--------------------------------------------------&zwsp;------------------
 997 query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
 998 calls           | 3000
 999 total_exec_time | 271.232977
1000 rows            | 3000
1001 hit_percent     | 98.8454011741682975
1002 -[ RECORD 5 ]---+--------------------------------------------------&zwsp;------------------
1003 query           | alter table pgbench_accounts add primary key (aid)
1004 calls           | 1
1005 total_exec_time | 160.588563
1006 rows            | 0
1007 hit_percent     | 100.0000000000000000
1008
1009
1010 bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
1011             WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
1012
1013 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
1014                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
1015           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
1016 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;------------------
1017 query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
1018 calls           | 3000
1019 total_exec_time | 20756.669379
1020 rows            | 3000
1021 hit_percent     | 100.0000000000000000
1022 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;------------------
1023 query           | copy pgbench_accounts from stdin
1024 calls           | 1
1025 total_exec_time | 291.865911
1026 rows            | 100000
1027 hit_percent     | 100.0000000000000000
1028 -[ RECORD 3 ]---+--------------------------------------------------&zwsp;------------------
1029 query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
1030 calls           | 3000
1031 total_exec_time | 271.232977
1032 rows            | 3000
1033 hit_percent     | 98.8454011741682975
1034 -[ RECORD 4 ]---+--------------------------------------------------&zwsp;------------------
1035 query           | alter table pgbench_accounts add primary key (aid)
1036 calls           | 1
1037 total_exec_time | 160.588563
1038 rows            | 0
1039 hit_percent     | 100.0000000000000000
1040 -[ RECORD 5 ]---+--------------------------------------------------&zwsp;------------------
1041 query           | vacuum analyze pgbench_accounts
1042 calls           | 1
1043 total_exec_time | 136.448116
1044 rows            | 0
1045 hit_percent     | 99.9201915403032721
1046
1047 bench=# SELECT pg_stat_statements_reset(0,0,0);
1048
1049 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
1050                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
1051           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
1052 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;---------------------------
1053 query           | SELECT pg_stat_statements_reset(0,0,0)
1054 calls           | 1
1055 total_exec_time | 0.189497
1056 rows            | 1
1057 hit_percent     |
1058 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;---------------------------
1059 query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit /          +
1060                 |                nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
1061                 |           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
1062 calls           | 0
1063 total_exec_time | 0
1064 rows            | 0
1065 hit_percent     |
1066
1067 </screen>
1068  </sect2>
1069
1070  <sect2 id="pgstatstatements-authors">
1071   <title>Authors</title>
1072
1073   <para>
1074    Takahiro Itagaki <email>itagaki.takahiro@oss.ntt.co.jp</email>.
1075    Query normalization added by Peter Geoghegan <email>peter@2ndquadrant.com</email>.
1076   </para>
1077  </sect2>
1078
1079 </sect1>