diff options
Diffstat (limited to 'doc/src/sgml/html/pgstatstatements.html')
| -rw-r--r-- | doc/src/sgml/html/pgstatstatements.html | 621 |
1 files changed, 621 insertions, 0 deletions
diff --git a/doc/src/sgml/html/pgstatstatements.html b/doc/src/sgml/html/pgstatstatements.html new file mode 100644 index 0000000..ac1affd --- /dev/null +++ b/doc/src/sgml/html/pgstatstatements.html @@ -0,0 +1,621 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.32. pg_stat_statements — track statistics of SQL planning and execution</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgrowlocks.html" title="F.31. pgrowlocks — show a table's row locking information" /><link rel="next" href="pgstattuple.html" title="F.33. pgstattuple — obtain tuple-level statistics" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.32. pg_stat_statements — track statistics of SQL planning and execution</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgrowlocks.html" title="F.31. pgrowlocks — show a table's row locking information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgstattuple.html" title="F.33. pgstattuple — obtain tuple-level statistics">Next</a></td></tr></table><hr /></div><div class="sect1" id="PGSTATSTATEMENTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.32. pg_stat_statements — track statistics of SQL planning and execution <a href="#PGSTATSTATEMENTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-PG-STAT-STATEMENTS">F.32.1. The <code class="structname">pg_stat_statements</code> View</a></span></dt><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-PG-STAT-STATEMENTS-INFO">F.32.2. The <code class="structname">pg_stat_statements_info</code> View</a></span></dt><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-FUNCS">F.32.3. Functions</a></span></dt><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS">F.32.4. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-SAMPLE-OUTPUT">F.32.5. Sample Output</a></span></dt><dt><span class="sect2"><a href="pgstatstatements.html#PGSTATSTATEMENTS-AUTHORS">F.32.6. Authors</a></span></dt></dl></div><a id="id-1.11.7.42.2" class="indexterm"></a><p> + The <code class="filename">pg_stat_statements</code> module provides a means for + tracking planning and execution statistics of all SQL statements executed by + a server. + </p><p> + The module must be loaded by adding <code class="literal">pg_stat_statements</code> to + <a class="xref" href="runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES">shared_preload_libraries</a> in + <code class="filename">postgresql.conf</code>, because it requires additional shared memory. + This means that a server restart is needed to add or remove the module. + In addition, query identifier calculation must be enabled in order for the + module to be active, which is done automatically if <a class="xref" href="runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID">compute_query_id</a> + is set to <code class="literal">auto</code> or <code class="literal">on</code>, or any third-party + module that calculates query identifiers is loaded. + </p><p> + When <code class="filename">pg_stat_statements</code> is active, it tracks + statistics across all databases of the server. To access and manipulate + these statistics, the module provides views + <code class="structname">pg_stat_statements</code> and + <code class="structname">pg_stat_statements_info</code>, + and the utility functions <code class="function">pg_stat_statements_reset</code> and + <code class="function">pg_stat_statements</code>. These are not available globally but + can be enabled for a specific database with + <code class="command">CREATE EXTENSION pg_stat_statements</code>. + </p><div class="sect2" id="PGSTATSTATEMENTS-PG-STAT-STATEMENTS"><div class="titlepage"><div><div><h3 class="title">F.32.1. The <code class="structname">pg_stat_statements</code> View <a href="#PGSTATSTATEMENTS-PG-STAT-STATEMENTS" class="id_link">#</a></h3></div></div></div><p> + The statistics gathered by the module are made available via a + view named <code class="structname">pg_stat_statements</code>. This view + contains one row for each distinct combination of database ID, user + ID, query ID and whether it's a top-level statement or not (up to + the maximum number of distinct statements that the module can track). + The columns of the view are shown in + <a class="xref" href="pgstatstatements.html#PGSTATSTATEMENTS-COLUMNS" title="Table F.22. pg_stat_statements Columns">Table F.22</a>. + </p><div class="table" id="PGSTATSTATEMENTS-COLUMNS"><p class="title"><strong>Table F.22. <code class="structname">pg_stat_statements</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_stat_statements Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition"> + Column Type + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">userid</code> <code class="type">oid</code> + (references <a class="link" href="catalog-pg-authid.html" title="53.8. pg_authid"><code class="structname">pg_authid</code></a>.<code class="structfield">oid</code>) + </p> + <p> + OID of user who executed the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">dbid</code> <code class="type">oid</code> + (references <a class="link" href="catalog-pg-database.html" title="53.15. pg_database"><code class="structname">pg_database</code></a>.<code class="structfield">oid</code>) + </p> + <p> + OID of database in which the statement was executed + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">toplevel</code> <code class="type">bool</code> + </p> + <p> + True if the query was executed as a top-level statement + (always true if <code class="varname">pg_stat_statements.track</code> is set to + <code class="literal">top</code>) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">queryid</code> <code class="type">bigint</code> + </p> + <p> + Hash code to identify identical normalized queries. + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">query</code> <code class="type">text</code> + </p> + <p> + Text of a representative statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">plans</code> <code class="type">bigint</code> + </p> + <p> + Number of times the statement was planned + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">total_plan_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent planning the statement, in milliseconds + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">min_plan_time</code> <code class="type">double precision</code> + </p> + <p> + Minimum time spent planning the statement, in milliseconds + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">max_plan_time</code> <code class="type">double precision</code> + </p> + <p> + Maximum time spent planning the statement, in milliseconds + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">mean_plan_time</code> <code class="type">double precision</code> + </p> + <p> + Mean time spent planning the statement, in milliseconds + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">stddev_plan_time</code> <code class="type">double precision</code> + </p> + <p> + Population standard deviation of time spent planning the statement, + in milliseconds + (if <code class="varname">pg_stat_statements.track_planning</code> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">calls</code> <code class="type">bigint</code> + </p> + <p> + Number of times the statement was executed + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">total_exec_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent executing the statement, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">min_exec_time</code> <code class="type">double precision</code> + </p> + <p> + Minimum time spent executing the statement, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">max_exec_time</code> <code class="type">double precision</code> + </p> + <p> + Maximum time spent executing the statement, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">mean_exec_time</code> <code class="type">double precision</code> + </p> + <p> + Mean time spent executing the statement, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">stddev_exec_time</code> <code class="type">double precision</code> + </p> + <p> + Population standard deviation of time spent executing the statement, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">rows</code> <code class="type">bigint</code> + </p> + <p> + Total number of rows retrieved or affected by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">shared_blks_hit</code> <code class="type">bigint</code> + </p> + <p> + Total number of shared block cache hits by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">shared_blks_read</code> <code class="type">bigint</code> + </p> + <p> + Total number of shared blocks read by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">shared_blks_dirtied</code> <code class="type">bigint</code> + </p> + <p> + Total number of shared blocks dirtied by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">shared_blks_written</code> <code class="type">bigint</code> + </p> + <p> + Total number of shared blocks written by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">local_blks_hit</code> <code class="type">bigint</code> + </p> + <p> + Total number of local block cache hits by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">local_blks_read</code> <code class="type">bigint</code> + </p> + <p> + Total number of local blocks read by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">local_blks_dirtied</code> <code class="type">bigint</code> + </p> + <p> + Total number of local blocks dirtied by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">local_blks_written</code> <code class="type">bigint</code> + </p> + <p> + Total number of local blocks written by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">temp_blks_read</code> <code class="type">bigint</code> + </p> + <p> + Total number of temp blocks read by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">temp_blks_written</code> <code class="type">bigint</code> + </p> + <p> + Total number of temp blocks written by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">blk_read_time</code> <code class="type">double precision</code> + </p> + <p> + Total time the statement spent reading data file blocks, in milliseconds + (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled, otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">blk_write_time</code> <code class="type">double precision</code> + </p> + <p> + Total time the statement spent writing data file blocks, in milliseconds + (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled, otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">temp_blk_read_time</code> <code class="type">double precision</code> + </p> + <p> + Total time the statement spent reading temporary file blocks, in + milliseconds (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">temp_blk_write_time</code> <code class="type">double precision</code> + </p> + <p> + Total time the statement spent writing temporary file blocks, in + milliseconds (if <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled, + otherwise zero) + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">wal_records</code> <code class="type">bigint</code> + </p> + <p> + Total number of WAL records generated by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">wal_fpi</code> <code class="type">bigint</code> + </p> + <p> + Total number of WAL full page images generated by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">wal_bytes</code> <code class="type">numeric</code> + </p> + <p> + Total amount of WAL generated by the statement in bytes + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_functions</code> <code class="type">bigint</code> + </p> + <p> + Total number of functions JIT-compiled by the statement + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_generation_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent by the statement on generating JIT code, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_inlining_count</code> <code class="type">bigint</code> + </p> + <p> + Number of times functions have been inlined + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_inlining_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent by the statement on inlining functions, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_optimization_count</code> <code class="type">bigint</code> + </p> + <p> + Number of times the statement has been optimized + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_optimization_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent by the statement on optimizing, in milliseconds + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_emission_count</code> <code class="type">bigint</code> + </p> + <p> + Number of times code has been emitted + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">jit_emission_time</code> <code class="type">double precision</code> + </p> + <p> + Total time spent by the statement on emitting code, in milliseconds + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + For security reasons, only superusers and roles with privileges of the + <code class="literal">pg_read_all_stats</code> role are allowed to see the SQL text and + <code class="structfield">queryid</code> of queries executed by other users. + Other users can see the statistics, however, if the view has been installed + in their database. + </p><p> + Plannable queries (that is, <code class="command">SELECT</code>, <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, <code class="command">DELETE</code>, and <code class="command">MERGE</code>) + and utility commands are combined into a single + <code class="structname">pg_stat_statements</code> entry whenever they have identical query + structures according to an internal hash calculation. Typically, two + queries will be considered the same for this purpose if they are + semantically equivalent except for the values of literal constants + appearing in the query. + </p><div class="note"><h3 class="title">Note</h3><p> + The following details about constant replacement and + <code class="structfield">queryid</code> only apply when <a class="xref" href="runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID">compute_query_id</a> is enabled. If you use an external + module instead to compute <code class="structfield">queryid</code>, you + should refer to its documentation for details. + </p></div><p> + When a constant's value has been ignored for purposes of matching the query + to other queries, the constant is replaced by a parameter symbol, such + as <code class="literal">$1</code>, in the <code class="structname">pg_stat_statements</code> + display. + The rest of the query text is that of the first query that had the + particular <code class="structfield">queryid</code> hash value associated with the + <code class="structname">pg_stat_statements</code> entry. + </p><p> + Queries on which normalization can be applied may be observed with constant + values in <code class="structname">pg_stat_statements</code>, especially when there + is a high rate of entry deallocations. To reduce the likelihood of this + happening, consider increasing <code class="varname">pg_stat_statements.max</code>. + The <code class="structname">pg_stat_statements_info</code> view, discussed below + in <a class="xref" href="pgstatstatements.html#PGSTATSTATEMENTS-PG-STAT-STATEMENTS-INFO" title="F.32.2. The pg_stat_statements_info View">Section F.32.2</a>, + provides statistics about entry deallocations. + </p><p> + In some cases, queries with visibly different texts might get merged into a + single <code class="structname">pg_stat_statements</code> entry. Normally this will happen + only for semantically equivalent queries, but there is a small chance of + hash collisions causing unrelated queries to be merged into one entry. + (This cannot happen for queries belonging to different users or databases, + however.) + </p><p> + Since the <code class="structfield">queryid</code> hash value is computed on the + post-parse-analysis representation of the queries, the opposite is + also possible: queries with identical texts might appear as + separate entries, if they have different meanings as a result of + factors such as different <code class="varname">search_path</code> settings. + </p><p> + Consumers of <code class="structname">pg_stat_statements</code> may wish to use + <code class="structfield">queryid</code> (perhaps in combination with + <code class="structfield">dbid</code> and <code class="structfield">userid</code>) as a more stable + and reliable identifier for each entry than its query text. + However, it is important to understand that there are only limited + guarantees around the stability of the <code class="structfield">queryid</code> hash + value. Since the identifier is derived from the + post-parse-analysis tree, its value is a function of, among other + things, the internal object identifiers appearing in this representation. + This has some counterintuitive implications. For example, + <code class="filename">pg_stat_statements</code> will consider two apparently-identical + queries to be distinct, if they reference a table that was dropped + and recreated between the executions of the two queries. + The hashing process is also sensitive to differences in + machine architecture and other facets of the platform. + Furthermore, it is not safe to assume that <code class="structfield">queryid</code> + will be stable across major versions of <span class="productname">PostgreSQL</span>. + </p><p> + As a rule of thumb, <code class="structfield">queryid</code> values can be assumed to be + stable and comparable only so long as the underlying server version and + catalog metadata details stay exactly the same. Two servers + participating in replication based on physical WAL replay can be expected + to have identical <code class="structfield">queryid</code> values for the same query. + However, logical replication schemes do not promise to keep replicas + identical in all relevant details, so <code class="structfield">queryid</code> will + not be a useful identifier for accumulating costs across a set of logical + replicas. If in doubt, direct testing is recommended. + </p><p> + The parameter symbols used to replace constants in + representative query texts start from the next number after the + highest <code class="literal">$</code><em class="replaceable"><code>n</code></em> parameter in the original query + text, or <code class="literal">$1</code> if there was none. It's worth noting that in + some cases there may be hidden parameter symbols that affect this + numbering. For example, <span class="application">PL/pgSQL</span> uses hidden parameter + symbols to insert values of function local variables into queries, so that + a <span class="application">PL/pgSQL</span> statement like <code class="literal">SELECT i + 1 INTO j</code> + would have representative text like <code class="literal">SELECT i + $2</code>. + </p><p> + The representative query texts are kept in an external disk file, and do + not consume shared memory. Therefore, even very lengthy query texts can + be stored successfully. However, if many long query texts are + accumulated, the external file might grow unmanageably large. As a + recovery method if that happens, <code class="filename">pg_stat_statements</code> may + choose to discard the query texts, whereupon all existing entries in + the <code class="structname">pg_stat_statements</code> view will show + null <code class="structfield">query</code> fields, though the statistics associated with + each <code class="structfield">queryid</code> are preserved. If this happens, consider + reducing <code class="varname">pg_stat_statements.max</code> to prevent + recurrences. + </p><p> + <code class="structfield">plans</code> and <code class="structfield">calls</code> aren't + always expected to match because planning and execution statistics are + updated at their respective end phase, and only for successful operations. + For example, if a statement is successfully planned but fails during + the execution phase, only its planning statistics will be updated. + If planning is skipped because a cached plan is used, only its execution + statistics will be updated. + </p></div><div class="sect2" id="PGSTATSTATEMENTS-PG-STAT-STATEMENTS-INFO"><div class="titlepage"><div><div><h3 class="title">F.32.2. The <code class="structname">pg_stat_statements_info</code> View <a href="#PGSTATSTATEMENTS-PG-STAT-STATEMENTS-INFO" class="id_link">#</a></h3></div></div></div><a id="id-1.11.7.42.7.2" class="indexterm"></a><p> + The statistics of the <code class="filename">pg_stat_statements</code> module + itself are tracked and made available via a view named + <code class="structname">pg_stat_statements_info</code>. This view contains + only a single row. The columns of the view are shown in + <a class="xref" href="pgstatstatements.html#PGSTATSTATEMENTSINFO-COLUMNS" title="Table F.23. pg_stat_statements_info Columns">Table F.23</a>. + </p><div class="table" id="PGSTATSTATEMENTSINFO-COLUMNS"><p class="title"><strong>Table F.23. <code class="structname">pg_stat_statements_info</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_stat_statements_info Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition"> + Column Type + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">dealloc</code> <code class="type">bigint</code> + </p> + <p> + Total number of times <code class="structname">pg_stat_statements</code> + entries about the least-executed statements were deallocated + because more distinct statements than + <code class="varname">pg_stat_statements.max</code> were observed + </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition"> + <code class="structfield">stats_reset</code> <code class="type">timestamp with time zone</code> + </p> + <p> + Time at which all statistics in the + <code class="structname">pg_stat_statements</code> view were last reset. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="PGSTATSTATEMENTS-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.32.3. Functions <a href="#PGSTATSTATEMENTS-FUNCS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term"> + <code class="function">pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void</code> + <a id="id-1.11.7.42.8.2.1.1.2" class="indexterm"></a> + </span></dt><dd><p> + <code class="function">pg_stat_statements_reset</code> discards statistics + gathered so far by <code class="filename">pg_stat_statements</code> corresponding + to the specified <code class="structfield">userid</code>, <code class="structfield">dbid</code> + and <code class="structfield">queryid</code>. If any of the parameters are not + specified, the default value <code class="literal">0</code>(invalid) is used for + each of them and the statistics that match with other parameters will be + reset. If no parameter is specified or all the specified parameters are + <code class="literal">0</code>(invalid), it will discard all statistics. + If all statistics in the <code class="filename">pg_stat_statements</code> + view are discarded, it will also reset the statistics in the + <code class="structname">pg_stat_statements_info</code> view. + By default, this function can only be executed by superusers. + Access may be granted to others using <code class="command">GRANT</code>. + </p></dd><dt><span class="term"> + <code class="function">pg_stat_statements(showtext boolean) returns setof record</code> + <a id="id-1.11.7.42.8.2.2.1.2" class="indexterm"></a> + </span></dt><dd><p> + The <code class="structname">pg_stat_statements</code> view is defined in + terms of a function also named <code class="function">pg_stat_statements</code>. + It is possible for clients to call + the <code class="function">pg_stat_statements</code> function directly, and by + specifying <code class="literal">showtext := false</code> have query text be + omitted (that is, the <code class="literal">OUT</code> argument that corresponds + to the view's <code class="structfield">query</code> column will return nulls). This + feature is intended to support external tools that might wish to avoid + the overhead of repeatedly retrieving query texts of indeterminate + length. Such tools can instead cache the first query text observed + for each entry themselves, since that is + all <code class="filename">pg_stat_statements</code> itself does, and then retrieve + query texts only as needed. Since the server stores query texts in a + file, this approach may reduce physical I/O for repeated examination + of the <code class="structname">pg_stat_statements</code> data. + </p></dd></dl></div></div><div class="sect2" id="PGSTATSTATEMENTS-CONFIG-PARAMS"><div class="titlepage"><div><div><h3 class="title">F.32.4. Configuration Parameters <a href="#PGSTATSTATEMENTS-CONFIG-PARAMS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term"> + <code class="varname">pg_stat_statements.max</code> (<code class="type">integer</code>) + <a id="id-1.11.7.42.9.2.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + <code class="varname">pg_stat_statements.max</code> is the maximum number of + statements tracked by the module (i.e., the maximum number of rows + in the <code class="structname">pg_stat_statements</code> view). If more distinct + statements than that are observed, information about the least-executed + statements is discarded. The number of times such information was + discarded can be seen in the + <code class="structname">pg_stat_statements_info</code> view. + The default value is 5000. + This parameter can only be set at server start. + </p></dd><dt><span class="term"> + <code class="varname">pg_stat_statements.track</code> (<code class="type">enum</code>) + <a id="id-1.11.7.42.9.2.2.1.3" class="indexterm"></a> + </span></dt><dd><p> + <code class="varname">pg_stat_statements.track</code> controls which statements + are counted by the module. + Specify <code class="literal">top</code> to track top-level statements (those issued + directly by clients), <code class="literal">all</code> to also track nested statements + (such as statements invoked within functions), or <code class="literal">none</code> to + disable statement statistics collection. + The default value is <code class="literal">top</code>. + Only superusers can change this setting. + </p></dd><dt><span class="term"> + <code class="varname">pg_stat_statements.track_utility</code> (<code class="type">boolean</code>) + <a id="id-1.11.7.42.9.2.3.1.3" class="indexterm"></a> + </span></dt><dd><p> + <code class="varname">pg_stat_statements.track_utility</code> controls whether + utility commands are tracked by the module. Utility commands are + all those other than <code class="command">SELECT</code>, <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, <code class="command">DELETE</code>, and <code class="command">MERGE</code>. + The default value is <code class="literal">on</code>. + Only superusers can change this setting. + </p></dd><dt><span class="term"> + <code class="varname">pg_stat_statements.track_planning</code> (<code class="type">boolean</code>) + <a id="id-1.11.7.42.9.2.4.1.3" class="indexterm"></a> + </span></dt><dd><p> + <code class="varname">pg_stat_statements.track_planning</code> controls whether + planning operations and duration are tracked by the module. + Enabling this parameter may incur a noticeable performance penalty, + especially when statements with identical query structure are executed + by many concurrent connections which compete to update a small number of + <code class="structname">pg_stat_statements</code> entries. + The default value is <code class="literal">off</code>. + Only superusers can change this setting. + </p></dd><dt><span class="term"> + <code class="varname">pg_stat_statements.save</code> (<code class="type">boolean</code>) + <a id="id-1.11.7.42.9.2.5.1.3" class="indexterm"></a> + </span></dt><dd><p> + <code class="varname">pg_stat_statements.save</code> specifies whether to + save statement statistics across server shutdowns. + If it is <code class="literal">off</code> then statistics are not saved at + shutdown nor reloaded at server start. + The default value is <code class="literal">on</code>. + This parameter can only be set in the <code class="filename">postgresql.conf</code> + file or on the server command line. + </p></dd></dl></div><p> + The module requires additional shared memory proportional to + <code class="varname">pg_stat_statements.max</code>. Note that this + memory is consumed whenever the module is loaded, even if + <code class="varname">pg_stat_statements.track</code> is set to <code class="literal">none</code>. + </p><p> + These parameters must be set in <code class="filename">postgresql.conf</code>. + Typical usage might be: + +</p><pre class="programlisting"> +# postgresql.conf +shared_preload_libraries = 'pg_stat_statements' + +compute_query_id = on +pg_stat_statements.max = 10000 +pg_stat_statements.track = all +</pre><p> + </p></div><div class="sect2" id="PGSTATSTATEMENTS-SAMPLE-OUTPUT"><div class="titlepage"><div><div><h3 class="title">F.32.5. Sample Output <a href="#PGSTATSTATEMENTS-SAMPLE-OUTPUT" class="id_link">#</a></h3></div></div></div><pre class="screen"> +bench=# SELECT pg_stat_statements_reset(); + +$ pgbench -i bench +$ pgbench -c10 -t300 bench + +bench=# \x +bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / + nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 +calls | 3000 +total_exec_time | 25565.855387 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 2 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 +calls | 3000 +total_exec_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]---+-------------------------------------------------------------------- +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 4 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 +calls | 3000 +total_exec_time | 271.232977 +rows | 3000 +hit_percent | 98.8454011741682975 +-[ RECORD 5 ]---+-------------------------------------------------------------------- +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_exec_time | 160.588563 +rows | 0 +hit_percent | 100.0000000000000000 + + +bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s + WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2'; + +bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / + nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 +calls | 3000 +total_exec_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 2 ]---+-------------------------------------------------------------------- +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 +calls | 3000 +total_exec_time | 271.232977 +rows | 3000 +hit_percent | 98.8454011741682975 +-[ RECORD 4 ]---+-------------------------------------------------------------------- +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_exec_time | 160.588563 +rows | 0 +hit_percent | 100.0000000000000000 +-[ RECORD 5 ]---+-------------------------------------------------------------------- +query | vacuum analyze pgbench_accounts +calls | 1 +total_exec_time | 136.448116 +rows | 0 +hit_percent | 99.9201915403032721 + +bench=# SELECT pg_stat_statements_reset(0,0,0); + +bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / + nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+----------------------------------------------------------------------------- +query | SELECT pg_stat_statements_reset(0,0,0) +calls | 1 +total_exec_time | 0.189497 +rows | 1 +hit_percent | +-[ RECORD 2 ]---+----------------------------------------------------------------------------- +query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / + + | nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+ + | FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3 +calls | 0 +total_exec_time | 0 +rows | 0 +hit_percent | + +</pre></div><div class="sect2" id="PGSTATSTATEMENTS-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.32.6. Authors <a href="#PGSTATSTATEMENTS-AUTHORS" class="id_link">#</a></h3></div></div></div><p> + Takahiro Itagaki <code class="email"><<a class="email" href="mailto:itagaki.takahiro@oss.ntt.co.jp">itagaki.takahiro@oss.ntt.co.jp</a>></code>. + Query normalization added by Peter Geoghegan <code class="email"><<a class="email" href="mailto:peter@2ndquadrant.com">peter@2ndquadrant.com</a>></code>. + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgrowlocks.html" title="F.31. pgrowlocks — show a table's row locking information">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgstattuple.html" title="F.33. pgstattuple — obtain tuple-level statistics">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.31. pgrowlocks — show a table's row locking information </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.33. pgstattuple — obtain tuple-level statistics</td></tr></table></div></body></html>
\ No newline at end of file |