begriffs open source - ai-pg/blob - full-docs/txt/pgstatstatements.txt

   1
   2 F.32. pg_stat_statements — track statistics of SQL planning and execution #
   3
   4    F.32.1. The pg_stat_statements View
   5    F.32.2. The pg_stat_statements_info View
   6    F.32.3. Functions
   7    F.32.4. Configuration Parameters
   8    F.32.5. Sample Output
   9    F.32.6. Authors
  10
  11    The pg_stat_statements module provides a means for tracking planning
  12    and execution statistics of all SQL statements executed by a server.
  13
  14    The module must be loaded by adding pg_stat_statements to
  15    shared_preload_libraries in postgresql.conf, because it requires
  16    additional shared memory. This means that a server restart is needed to
  17    add or remove the module. In addition, query identifier calculation
  18    must be enabled in order for the module to be active, which is done
  19    automatically if compute_query_id is set to auto or on, or any
  20    third-party module that calculates query identifiers is loaded.
  21
  22    When pg_stat_statements is active, it tracks statistics across all
  23    databases of the server. To access and manipulate these statistics, the
  24    module provides views pg_stat_statements and pg_stat_statements_info,
  25    and the utility functions pg_stat_statements_reset and
  26    pg_stat_statements. These are not available globally but can be enabled
  27    for a specific database with CREATE EXTENSION pg_stat_statements.
  28
  29 F.32.1. The pg_stat_statements View #
  30
  31    The statistics gathered by the module are made available via a view
  32    named pg_stat_statements. This view contains one row for each distinct
  33    combination of database ID, user ID, query ID and whether it's a
  34    top-level statement or not (up to the maximum number of distinct
  35    statements that the module can track). The columns of the view are
  36    shown in Table F.22.
  37
  38    Table F.22. pg_stat_statements Columns
  39
  40    Column Type
  41
  42    Description
  43
  44    userid oid (references pg_authid.oid)
  45
  46    OID of user who executed the statement
  47
  48    dbid oid (references pg_database.oid)
  49
  50    OID of database in which the statement was executed
  51
  52    toplevel bool
  53
  54    True if the query was executed as a top-level statement (always true if
  55    pg_stat_statements.track is set to top)
  56
  57    queryid bigint
  58
  59    Hash code to identify identical normalized queries.
  60
  61    query text
  62
  63    Text of a representative statement
  64
  65    plans bigint
  66
  67    Number of times the statement was planned (if
  68    pg_stat_statements.track_planning is enabled, otherwise zero)
  69
  70    total_plan_time double precision
  71
  72    Total time spent planning the statement, in milliseconds (if
  73    pg_stat_statements.track_planning is enabled, otherwise zero)
  74
  75    min_plan_time double precision
  76
  77    Minimum time spent planning the statement, in milliseconds. This field
  78    will be zero if pg_stat_statements.track_planning is disabled, or if
  79    the counter has been reset using the pg_stat_statements_reset function
  80    with the minmax_only parameter set to true and never been planned
  81    since.
  82
  83    max_plan_time double precision
  84
  85    Maximum time spent planning the statement, in milliseconds. This field
  86    will be zero if pg_stat_statements.track_planning is disabled, or if
  87    the counter has been reset using the pg_stat_statements_reset function
  88    with the minmax_only parameter set to true and never been planned
  89    since.
  90
  91    mean_plan_time double precision
  92
  93    Mean time spent planning the statement, in milliseconds (if
  94    pg_stat_statements.track_planning is enabled, otherwise zero)
  95
  96    stddev_plan_time double precision
  97
  98    Population standard deviation of time spent planning the statement, in
  99    milliseconds (if pg_stat_statements.track_planning is enabled,
 100    otherwise zero)
 101
 102    calls bigint
 103
 104    Number of times the statement was executed
 105
 106    total_exec_time double precision
 107
 108    Total time spent executing the statement, in milliseconds
 109
 110    min_exec_time double precision
 111
 112    Minimum time spent executing the statement, in milliseconds, this field
 113    will be zero until this statement is executed first time after reset
 114    performed by the pg_stat_statements_reset function with the minmax_only
 115    parameter set to true
 116
 117    max_exec_time double precision
 118
 119    Maximum time spent executing the statement, in milliseconds, this field
 120    will be zero until this statement is executed first time after reset
 121    performed by the pg_stat_statements_reset function with the minmax_only
 122    parameter set to true
 123
 124    mean_exec_time double precision
 125
 126    Mean time spent executing the statement, in milliseconds
 127
 128    stddev_exec_time double precision
 129
 130    Population standard deviation of time spent executing the statement, in
 131    milliseconds
 132
 133    rows bigint
 134
 135    Total number of rows retrieved or affected by the statement
 136
 137    shared_blks_hit bigint
 138
 139    Total number of shared block cache hits by the statement
 140
 141    shared_blks_read bigint
 142
 143    Total number of shared blocks read by the statement
 144
 145    shared_blks_dirtied bigint
 146
 147    Total number of shared blocks dirtied by the statement
 148
 149    shared_blks_written bigint
 150
 151    Total number of shared blocks written by the statement
 152
 153    local_blks_hit bigint
 154
 155    Total number of local block cache hits by the statement
 156
 157    local_blks_read bigint
 158
 159    Total number of local blocks read by the statement
 160
 161    local_blks_dirtied bigint
 162
 163    Total number of local blocks dirtied by the statement
 164
 165    local_blks_written bigint
 166
 167    Total number of local blocks written by the statement
 168
 169    temp_blks_read bigint
 170
 171    Total number of temp blocks read by the statement
 172
 173    temp_blks_written bigint
 174
 175    Total number of temp blocks written by the statement
 176
 177    shared_blk_read_time double precision
 178
 179    Total time the statement spent reading shared blocks, in milliseconds
 180    (if track_io_timing is enabled, otherwise zero)
 181
 182    shared_blk_write_time double precision
 183
 184    Total time the statement spent writing shared blocks, in milliseconds
 185    (if track_io_timing is enabled, otherwise zero)
 186
 187    local_blk_read_time double precision
 188
 189    Total time the statement spent reading local blocks, in milliseconds
 190    (if track_io_timing is enabled, otherwise zero)
 191
 192    local_blk_write_time double precision
 193
 194    Total time the statement spent writing local blocks, in milliseconds
 195    (if track_io_timing is enabled, otherwise zero)
 196
 197    temp_blk_read_time double precision
 198
 199    Total time the statement spent reading temporary file blocks, in
 200    milliseconds (if track_io_timing is enabled, otherwise zero)
 201
 202    temp_blk_write_time double precision
 203
 204    Total time the statement spent writing temporary file blocks, in
 205    milliseconds (if track_io_timing is enabled, otherwise zero)
 206
 207    wal_records bigint
 208
 209    Total number of WAL records generated by the statement
 210
 211    wal_fpi bigint
 212
 213    Total number of WAL full page images generated by the statement
 214
 215    wal_bytes numeric
 216
 217    Total amount of WAL generated by the statement in bytes
 218
 219    wal_buffers_full bigint
 220
 221    Number of times the WAL buffers became full
 222
 223    jit_functions bigint
 224
 225    Total number of functions JIT-compiled by the statement
 226
 227    jit_generation_time double precision
 228
 229    Total time spent by the statement on generating JIT code, in
 230    milliseconds
 231
 232    jit_inlining_count bigint
 233
 234    Number of times functions have been inlined
 235
 236    jit_inlining_time double precision
 237
 238    Total time spent by the statement on inlining functions, in
 239    milliseconds
 240
 241    jit_optimization_count bigint
 242
 243    Number of times the statement has been optimized
 244
 245    jit_optimization_time double precision
 246
 247    Total time spent by the statement on optimizing, in milliseconds
 248
 249    jit_emission_count bigint
 250
 251    Number of times code has been emitted
 252
 253    jit_emission_time double precision
 254
 255    Total time spent by the statement on emitting code, in milliseconds
 256
 257    jit_deform_count bigint
 258
 259    Total number of tuple deform functions JIT-compiled by the statement
 260
 261    jit_deform_time double precision
 262
 263    Total time spent by the statement on JIT-compiling tuple deform
 264    functions, in milliseconds
 265
 266    parallel_workers_to_launch bigint
 267
 268    Number of parallel workers planned to be launched
 269
 270    parallel_workers_launched bigint
 271
 272    Number of parallel workers actually launched
 273
 274    stats_since timestamp with time zone
 275
 276    Time at which statistics gathering started for this statement
 277
 278    minmax_stats_since timestamp with time zone
 279
 280    Time at which min/max statistics gathering started for this statement
 281    (fields min_plan_time, max_plan_time, min_exec_time and max_exec_time)
 282
 283    For security reasons, only superusers and roles with privileges of the
 284    pg_read_all_stats role are allowed to see the SQL text and queryid of
 285    queries executed by other users. Other users can see the statistics,
 286    however, if the view has been installed in their database.
 287
 288    Plannable queries (that is, SELECT, INSERT, UPDATE, DELETE, and MERGE)
 289    and utility commands are combined into a single pg_stat_statements
 290    entry whenever they have identical query structures according to an
 291    internal hash calculation. Typically, two queries will be considered
 292    the same for this purpose if they are semantically equivalent except
 293    for the values of literal constants appearing in the query.
 294
 295 Note
 296
 297    The following details about constant replacement and queryid only apply
 298    when compute_query_id is enabled. If you use an external module instead
 299    to compute queryid, you should refer to its documentation for details.
 300
 301    When a constant's value has been ignored for purposes of matching the
 302    query to other queries, the constant is replaced by a parameter symbol,
 303    such as $1, in the pg_stat_statements display. The rest of the query
 304    text is that of the first query that had the particular queryid hash
 305    value associated with the pg_stat_statements entry.
 306
 307    Queries on which normalization can be applied may be observed with
 308    constant values in pg_stat_statements, especially when there is a high
 309    rate of entry deallocations. To reduce the likelihood of this
 310    happening, consider increasing pg_stat_statements.max. The
 311    pg_stat_statements_info view, discussed below in Section F.32.2,
 312    provides statistics about entry deallocations.
 313
 314    In some cases, queries with visibly different texts might get merged
 315    into a single pg_stat_statements entry; as explained above, this is
 316    expected to happen for semantically equivalent queries. In addition, if
 317    the only difference between queries is the number of elements in a list
 318    of constants, the list will get squashed down to a single element but
 319    shown with a commented-out list indicator:
 320 =# SELECT pg_stat_statements_reset();
 321 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7);
 322 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8);
 323 =# SELECT query, calls FROM pg_stat_statements
 324    WHERE query LIKE 'SELECT%';
 325 -[ RECORD 1 ]------------------------------
 326 query | SELECT * FROM test WHERE a IN ($1 /*, ... */)
 327 calls | 2
 328
 329    In addition to these cases, there is a small chance of hash collisions
 330    causing unrelated queries to be merged into one entry. (This cannot
 331    happen for queries belonging to different users or databases, however.)
 332
 333    Since the queryid hash value is computed on the post-parse-analysis
 334    representation of the queries, the opposite is also possible: queries
 335    with identical texts might appear as separate entries, if they have
 336    different meanings as a result of factors such as different search_path
 337    settings.
 338
 339    Consumers of pg_stat_statements may wish to use queryid (perhaps in
 340    combination with dbid and userid) as a more stable and reliable
 341    identifier for each entry than its query text. However, it is important
 342    to understand that there are only limited guarantees around the
 343    stability of the queryid hash value. Since the identifier is derived
 344    from the post-parse-analysis tree, its value is a function of, among
 345    other things, the internal object identifiers appearing in this
 346    representation. This has some counterintuitive implications. For
 347    example, pg_stat_statements will consider two apparently-identical
 348    queries to be distinct, if they reference for example a function that
 349    was dropped and recreated between the executions of the two queries.
 350    Conversely, if a table is dropped and recreated between the executions
 351    of queries, two apparently-identical queries may be considered the
 352    same. However, if the alias for a table is different for
 353    otherwise-similar queries, these queries will be considered distinct.
 354    The hashing process is also sensitive to differences in machine
 355    architecture and other facets of the platform. Furthermore, it is not
 356    safe to assume that queryid will be stable across major versions of
 357    PostgreSQL.
 358
 359    Two servers participating in replication based on physical WAL replay
 360    can be expected to have identical queryid values for the same query.
 361    However, logical replication schemes do not promise to keep replicas
 362    identical in all relevant details, so queryid will not be a useful
 363    identifier for accumulating costs across a set of logical replicas. If
 364    in doubt, direct testing is recommended.
 365
 366    Generally, it can be assumed that queryid values are stable between
 367    minor version releases of PostgreSQL, providing that instances are
 368    running on the same machine architecture and the catalog metadata
 369    details match. Compatibility will only be broken between minor versions
 370    as a last resort.
 371
 372    The parameter symbols used to replace constants in representative query
 373    texts start from the next number after the highest $n parameter in the
 374    original query text, or $1 if there was none. It's worth noting that in
 375    some cases there may be hidden parameter symbols that affect this
 376    numbering. For example, PL/pgSQL uses hidden parameter symbols to
 377    insert values of function local variables into queries, so that a
 378    PL/pgSQL statement like SELECT i + 1 INTO j would have representative
 379    text like SELECT i + $2.
 380
 381    The representative query texts are kept in an external disk file, and
 382    do not consume shared memory. Therefore, even very lengthy query texts
 383    can be stored successfully. However, if many long query texts are
 384    accumulated, the external file might grow unmanageably large. As a
 385    recovery method if that happens, pg_stat_statements may choose to
 386    discard the query texts, whereupon all existing entries in the
 387    pg_stat_statements view will show null query fields, though the
 388    statistics associated with each queryid are preserved. If this happens,
 389    consider reducing pg_stat_statements.max to prevent recurrences.
 390
 391    plans and calls aren't always expected to match because planning and
 392    execution statistics are updated at their respective end phase, and
 393    only for successful operations. For example, if a statement is
 394    successfully planned but fails during the execution phase, only its
 395    planning statistics will be updated. If planning is skipped because a
 396    cached plan is used, only its execution statistics will be updated.
 397
 398 F.32.2. The pg_stat_statements_info View #
 399
 400    The statistics of the pg_stat_statements module itself are tracked and
 401    made available via a view named pg_stat_statements_info. This view
 402    contains only a single row. The columns of the view are shown in
 403    Table F.23.
 404
 405    Table F.23. pg_stat_statements_info Columns
 406
 407    Column Type
 408
 409    Description
 410
 411    dealloc bigint
 412
 413    Total number of times pg_stat_statements entries about the
 414    least-executed statements were deallocated because more distinct
 415    statements than pg_stat_statements.max were observed
 416
 417    stats_reset timestamp with time zone
 418
 419    Time at which all statistics in the pg_stat_statements view were last
 420    reset.
 421
 422 F.32.3. Functions #
 423
 424    pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint,
 425           minmax_only boolean) returns timestamp with time zone
 426           pg_stat_statements_reset discards statistics gathered so far by
 427           pg_stat_statements corresponding to the specified userid, dbid
 428           and queryid. If any of the parameters are not specified, the
 429           default value 0(invalid) is used for each of them and the
 430           statistics that match with other parameters will be reset. If no
 431           parameter is specified or all the specified parameters are
 432           0(invalid), it will discard all statistics. If all statistics in
 433           the pg_stat_statements view are discarded, it will also reset
 434           the statistics in the pg_stat_statements_info view. When
 435           minmax_only is true only the values of minimum and maximum
 436           planning and execution time will be reset (i.e. min_plan_time,
 437           max_plan_time, min_exec_time and max_exec_time fields). The
 438           default value for minmax_only parameter is false. Time of last
 439           min/max reset performed is shown in minmax_stats_since field of
 440           the pg_stat_statements view. This function returns the time of a
 441           reset. This time is saved to stats_reset field of
 442           pg_stat_statements_info view or to minmax_stats_since field of
 443           the pg_stat_statements view if the corresponding reset was
 444           actually performed. By default, this function can only be
 445           executed by superusers. Access may be granted to others using
 446           GRANT.
 447
 448    pg_stat_statements(showtext boolean) returns setof record
 449           The pg_stat_statements view is defined in terms of a function
 450           also named pg_stat_statements. It is possible for clients to
 451           call the pg_stat_statements function directly, and by specifying
 452           showtext := false have query text be omitted (that is, the OUT
 453           argument that corresponds to the view's query column will return
 454           nulls). This feature is intended to support external tools that
 455           might wish to avoid the overhead of repeatedly retrieving query
 456           texts of indeterminate length. Such tools can instead cache the
 457           first query text observed for each entry themselves, since that
 458           is all pg_stat_statements itself does, and then retrieve query
 459           texts only as needed. Since the server stores query texts in a
 460           file, this approach may reduce physical I/O for repeated
 461           examination of the pg_stat_statements data.
 462
 463 F.32.4. Configuration Parameters #
 464
 465    pg_stat_statements.max (integer)
 466           pg_stat_statements.max is the maximum number of statements
 467           tracked by the module (i.e., the maximum number of rows in the
 468           pg_stat_statements view). If more distinct statements than that
 469           are observed, information about the least-executed statements is
 470           discarded. The number of times such information was discarded
 471           can be seen in the pg_stat_statements_info view. The default
 472           value is 5000. This parameter can only be set at server start.
 473
 474    pg_stat_statements.track (enum)
 475           pg_stat_statements.track controls which statements are counted
 476           by the module. Specify top to track top-level statements (those
 477           issued directly by clients), all to also track nested statements
 478           (such as statements invoked within functions), or none to
 479           disable statement statistics collection. The default value is
 480           top. Only superusers can change this setting.
 481
 482    pg_stat_statements.track_utility (boolean)
 483           pg_stat_statements.track_utility controls whether utility
 484           commands are tracked by the module. Utility commands are all
 485           those other than SELECT, INSERT, UPDATE, DELETE, and MERGE. The
 486           default value is on. Only superusers can change this setting.
 487
 488    pg_stat_statements.track_planning (boolean)
 489           pg_stat_statements.track_planning controls whether planning
 490           operations and duration are tracked by the module. Enabling this
 491           parameter may incur a noticeable performance penalty, especially
 492           when statements with identical query structure are executed by
 493           many concurrent connections which compete to update a small
 494           number of pg_stat_statements entries. The default value is off.
 495           Only superusers can change this setting.
 496
 497    pg_stat_statements.save (boolean)
 498           pg_stat_statements.save specifies whether to save statement
 499           statistics across server shutdowns. If it is off then statistics
 500           are not saved at shutdown nor reloaded at server start. The
 501           default value is on. This parameter can only be set in the
 502           postgresql.conf file or on the server command line.
 503
 504    The module requires additional shared memory proportional to
 505    pg_stat_statements.max. Note that this memory is consumed whenever the
 506    module is loaded, even if pg_stat_statements.track is set to none.
 507
 508    These parameters must be set in postgresql.conf. Typical usage might
 509    be:
 510 # postgresql.conf
 511 shared_preload_libraries = 'pg_stat_statements'
 512
 513 compute_query_id = on
 514 pg_stat_statements.max = 10000
 515 pg_stat_statements.track = all
 516
 517 F.32.5. Sample Output #
 518
 519 bench=# SELECT pg_stat_statements_reset();
 520
 521 $ pgbench -i bench
 522 $ pgbench -c10 -t300 bench
 523
 524 bench=# \x
 525 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
 526                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
 527           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
 528 -[ RECORD 1 ]---+--------------------------------------------------------------
 529 ------
 530 query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid
 531  = $2
 532 calls           | 3000
 533 total_exec_time | 25565.855387
 534 rows            | 3000
 535 hit_percent     | 100.0000000000000000
 536 -[ RECORD 2 ]---+--------------------------------------------------------------
 537 ------
 538 query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid
 539 = $2
 540 calls           | 3000
 541 total_exec_time | 20756.669379
 542 rows            | 3000
 543 hit_percent     | 100.0000000000000000
 544 -[ RECORD 3 ]---+--------------------------------------------------------------
 545 ------
 546 query           | copy pgbench_accounts from stdin
 547 calls           | 1
 548 total_exec_time | 291.865911
 549 rows            | 100000
 550 hit_percent     | 100.0000000000000000
 551 -[ RECORD 4 ]---+--------------------------------------------------------------
 552 ------
 553 query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid
 554  = $2
 555 calls           | 3000
 556 total_exec_time | 271.232977
 557 rows            | 3000
 558 hit_percent     | 98.8454011741682975
 559 -[ RECORD 5 ]---+--------------------------------------------------------------
 560 ------
 561 query           | alter table pgbench_accounts add primary key (aid)
 562 calls           | 1
 563 total_exec_time | 160.588563
 564 rows            | 0
 565 hit_percent     | 100.0000000000000000
 566
 567
 568 bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements A
 569 S s
 570             WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $
 571 1 WHERE bid = $2';
 572
 573 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
 574                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
 575           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
 576 -[ RECORD 1 ]---+--------------------------------------------------------------
 577 ------
 578 query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid
 579 = $2
 580 calls           | 3000
 581 total_exec_time | 20756.669379
 582 rows            | 3000
 583 hit_percent     | 100.0000000000000000
 584 -[ RECORD 2 ]---+--------------------------------------------------------------
 585 ------
 586 query           | copy pgbench_accounts from stdin
 587 calls           | 1
 588 total_exec_time | 291.865911
 589 rows            | 100000
 590 hit_percent     | 100.0000000000000000
 591 -[ RECORD 3 ]---+--------------------------------------------------------------
 592 ------
 593 query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid
 594  = $2
 595 calls           | 3000
 596 total_exec_time | 271.232977
 597 rows            | 3000
 598 hit_percent     | 98.8454011741682975
 599 -[ RECORD 4 ]---+--------------------------------------------------------------
 600 ------
 601 query           | alter table pgbench_accounts add primary key (aid)
 602 calls           | 1
 603 total_exec_time | 160.588563
 604 rows            | 0
 605 hit_percent     | 100.0000000000000000
 606 -[ RECORD 5 ]---+--------------------------------------------------------------
 607 ------
 608 query           | vacuum analyze pgbench_accounts
 609 calls           | 1
 610 total_exec_time | 136.448116
 611 rows            | 0
 612 hit_percent     | 99.9201915403032721
 613
 614 bench=# SELECT pg_stat_statements_reset(0,0,0);
 615
 616 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
 617                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
 618           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
 619 -[ RECORD 1 ]---+--------------------------------------------------------------
 620 ---------------
 621 query           | SELECT pg_stat_statements_reset(0,0,0)
 622 calls           | 1
 623 total_exec_time | 0.189497
 624 rows            | 1
 625 hit_percent     |
 626 -[ RECORD 2 ]---+--------------------------------------------------------------
 627 ---------------
 628 query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_h
 629 it /          +
 630                 |                nullif(shared_blks_hit + shared_blks_read, $2)
 631 AS hit_percent+
 632                 |           FROM pg_stat_statements ORDER BY total_exec_time DES
 633 C LIMIT $3
 634 calls           | 0
 635 total_exec_time | 0
 636 rows            | 0
 637 hit_percent     |
 638
 639
 640 F.32.6. Authors #
 641
 642    Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>. Query normalization
 643    added by Peter Geoghegan <peter@2ndquadrant.com>.