diff options
Diffstat (limited to 'doc/src/sgml/pgstatstatements.sgml')
| -rw-r--r-- | doc/src/sgml/pgstatstatements.sgml | 983 |
1 files changed, 983 insertions, 0 deletions
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml new file mode 100644 index 0000000..b1214ee --- /dev/null +++ b/doc/src/sgml/pgstatstatements.sgml @@ -0,0 +1,983 @@ +<!-- doc/src/sgml/pgstatstatements.sgml --> + +<sect1 id="pgstatstatements" xreflabel="pg_stat_statements"> + <title>pg_stat_statements — track statistics of SQL planning and execution</title> + + <indexterm zone="pgstatstatements"> + <primary>pg_stat_statements</primary> + </indexterm> + + <para> + The <filename>pg_stat_statements</filename> module provides a means for + tracking planning and execution statistics of all SQL statements executed by + a server. + </para> + + <para> + The module must be loaded by adding <literal>pg_stat_statements</literal> to + <xref linkend="guc-shared-preload-libraries"/> in + <filename>postgresql.conf</filename>, 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 <xref linkend="guc-compute-query-id"/> + is set to <literal>auto</literal> or <literal>on</literal>, or any third-party + module that calculates query identifiers is loaded. + </para> + + <para> + When <filename>pg_stat_statements</filename> is active, it tracks + statistics across all databases of the server. To access and manipulate + these statistics, the module provides views + <structname>pg_stat_statements</structname> and + <structname>pg_stat_statements_info</structname>, + and the utility functions <function>pg_stat_statements_reset</function> and + <function>pg_stat_statements</function>. These are not available globally but + can be enabled for a specific database with + <command>CREATE EXTENSION pg_stat_statements</command>. + </para> + + <sect2 id="pgstatstatements-pg-stat-statements"> + <title>The <structname>pg_stat_statements</structname> View</title> + + <para> + The statistics gathered by the module are made available via a + view named <structname>pg_stat_statements</structname>. 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 + <xref linkend="pgstatstatements-columns"/>. + </para> + + <table id="pgstatstatements-columns"> + <title><structname>pg_stat_statements</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>userid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of user who executed the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>dbid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of database in which the statement was executed + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>toplevel</structfield> <type>bool</type> + </para> + <para> + True if the query was executed as a top-level statement + (always true if <varname>pg_stat_statements.track</varname> is set to + <literal>top</literal>) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>queryid</structfield> <type>bigint</type> + </para> + <para> + Hash code to identify identical normalized queries. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>query</structfield> <type>text</type> + </para> + <para> + Text of a representative statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>plans</structfield> <type>bigint</type> + </para> + <para> + Number of times the statement was planned + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_plan_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent planning the statement, in milliseconds + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>min_plan_time</structfield> <type>double precision</type> + </para> + <para> + Minimum time spent planning the statement, in milliseconds + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>max_plan_time</structfield> <type>double precision</type> + </para> + <para> + Maximum time spent planning the statement, in milliseconds + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>mean_plan_time</structfield> <type>double precision</type> + </para> + <para> + Mean time spent planning the statement, in milliseconds + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stddev_plan_time</structfield> <type>double precision</type> + </para> + <para> + Population standard deviation of time spent planning the statement, + in milliseconds + (if <varname>pg_stat_statements.track_planning</varname> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>calls</structfield> <type>bigint</type> + </para> + <para> + Number of times the statement was executed + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_exec_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent executing the statement, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>min_exec_time</structfield> <type>double precision</type> + </para> + <para> + Minimum time spent executing the statement, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>max_exec_time</structfield> <type>double precision</type> + </para> + <para> + Maximum time spent executing the statement, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>mean_exec_time</structfield> <type>double precision</type> + </para> + <para> + Mean time spent executing the statement, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stddev_exec_time</structfield> <type>double precision</type> + </para> + <para> + Population standard deviation of time spent executing the statement, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rows</structfield> <type>bigint</type> + </para> + <para> + Total number of rows retrieved or affected by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>shared_blks_hit</structfield> <type>bigint</type> + </para> + <para> + Total number of shared block cache hits by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>shared_blks_read</structfield> <type>bigint</type> + </para> + <para> + Total number of shared blocks read by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>shared_blks_dirtied</structfield> <type>bigint</type> + </para> + <para> + Total number of shared blocks dirtied by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>shared_blks_written</structfield> <type>bigint</type> + </para> + <para> + Total number of shared blocks written by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>local_blks_hit</structfield> <type>bigint</type> + </para> + <para> + Total number of local block cache hits by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>local_blks_read</structfield> <type>bigint</type> + </para> + <para> + Total number of local blocks read by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>local_blks_dirtied</structfield> <type>bigint</type> + </para> + <para> + Total number of local blocks dirtied by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>local_blks_written</structfield> <type>bigint</type> + </para> + <para> + Total number of local blocks written by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>temp_blks_read</structfield> <type>bigint</type> + </para> + <para> + Total number of temp blocks read by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>temp_blks_written</structfield> <type>bigint</type> + </para> + <para> + Total number of temp blocks written by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_read_time</structfield> <type>double precision</type> + </para> + <para> + Total time the statement spent reading data file blocks, in milliseconds + (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_write_time</structfield> <type>double precision</type> + </para> + <para> + Total time the statement spent writing data file blocks, in milliseconds + (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>temp_blk_read_time</structfield> <type>double precision</type> + </para> + <para> + Total time the statement spent reading temporary file blocks, in + milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>temp_blk_write_time</structfield> <type>double precision</type> + </para> + <para> + Total time the statement spent writing temporary file blocks, in + milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_records</structfield> <type>bigint</type> + </para> + <para> + Total number of WAL records generated by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_fpi</structfield> <type>bigint</type> + </para> + <para> + Total number of WAL full page images generated by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_bytes</structfield> <type>numeric</type> + </para> + <para> + Total amount of WAL generated by the statement in bytes + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_functions</structfield> <type>bigint</type> + </para> + <para> + Total number of functions JIT-compiled by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_generation_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent by the statement on generating JIT code, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_inlining_count</structfield> <type>bigint</type> + </para> + <para> + Number of times functions have been inlined + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_inlining_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent by the statement on inlining functions, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_optimization_count</structfield> <type>bigint</type> + </para> + <para> + Number of times the statement has been optimized + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_optimization_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent by the statement on optimizing, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_emission_count</structfield> <type>bigint</type> + </para> + <para> + Number of times code has been emitted + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_emission_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent by the statement on emitting code, in milliseconds + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + For security reasons, only superusers and roles with privileges of the + <literal>pg_read_all_stats</literal> role are allowed to see the SQL text and + <structfield>queryid</structfield> of queries executed by other users. + Other users can see the statistics, however, if the view has been installed + in their database. + </para> + + <para> + Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>, + <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>) + and utility commands are combined into a single + <structname>pg_stat_statements</structname> 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. + </para> + + <note> + <para> + The following details about constant replacement and + <structfield>queryid</structfield> only apply when <xref + linkend="guc-compute-query-id"/> is enabled. If you use an external + module instead to compute <structfield>queryid</structfield>, you + should refer to its documentation for details. + </para> + </note> + + <para> + 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 <literal>$1</literal>, in the <structname>pg_stat_statements</structname> + display. + The rest of the query text is that of the first query that had the + particular <structfield>queryid</structfield> hash value associated with the + <structname>pg_stat_statements</structname> entry. + </para> + + <para> + Queries on which normalization can be applied may be observed with constant + values in <structname>pg_stat_statements</structname>, especially when there + is a high rate of entry deallocations. To reduce the likelihood of this + happening, consider increasing <varname>pg_stat_statements.max</varname>. + The <structname>pg_stat_statements_info</structname> view, discussed below + in <xref linkend="pgstatstatements-pg-stat-statements-info"/>, + provides statistics about entry deallocations. + </para> + + <para> + In some cases, queries with visibly different texts might get merged into a + single <structname>pg_stat_statements</structname> 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.) + </para> + + <para> + Since the <structfield>queryid</structfield> 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 <varname>search_path</varname> settings. + </para> + + <para> + Consumers of <structname>pg_stat_statements</structname> may wish to use + <structfield>queryid</structfield> (perhaps in combination with + <structfield>dbid</structfield> and <structfield>userid</structfield>) 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 <structfield>queryid</structfield> 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, + <filename>pg_stat_statements</filename> 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 <structfield>queryid</structfield> + will be stable across major versions of <productname>PostgreSQL</productname>. + </para> + + <para> + As a rule of thumb, <structfield>queryid</structfield> 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 <structfield>queryid</structfield> values for the same query. + However, logical replication schemes do not promise to keep replicas + identical in all relevant details, so <structfield>queryid</structfield> will + not be a useful identifier for accumulating costs across a set of logical + replicas. If in doubt, direct testing is recommended. + </para> + + <para> + The parameter symbols used to replace constants in + representative query texts start from the next number after the + highest <literal>$</literal><replaceable>n</replaceable> parameter in the original query + text, or <literal>$1</literal> if there was none. It's worth noting that in + some cases there may be hidden parameter symbols that affect this + numbering. For example, <application>PL/pgSQL</application> uses hidden parameter + symbols to insert values of function local variables into queries, so that + a <application>PL/pgSQL</application> statement like <literal>SELECT i + 1 INTO j</literal> + would have representative text like <literal>SELECT i + $2</literal>. + </para> + + <para> + 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, <filename>pg_stat_statements</filename> may + choose to discard the query texts, whereupon all existing entries in + the <structname>pg_stat_statements</structname> view will show + null <structfield>query</structfield> fields, though the statistics associated with + each <structfield>queryid</structfield> are preserved. If this happens, consider + reducing <varname>pg_stat_statements.max</varname> to prevent + recurrences. + </para> + + <para> + <structfield>plans</structfield> and <structfield>calls</structfield> 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. + </para> + </sect2> + + <sect2 id="pgstatstatements-pg-stat-statements-info"> + <title>The <structname>pg_stat_statements_info</structname> View</title> + + <indexterm> + <primary>pg_stat_statements_info</primary> + </indexterm> + + <para> + The statistics of the <filename>pg_stat_statements</filename> module + itself are tracked and made available via a view named + <structname>pg_stat_statements_info</structname>. This view contains + only a single row. The columns of the view are shown in + <xref linkend="pgstatstatementsinfo-columns"/>. + </para> + + <table id="pgstatstatementsinfo-columns"> + <title><structname>pg_stat_statements_info</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>dealloc</structfield> <type>bigint</type> + </para> + <para> + Total number of times <structname>pg_stat_statements</structname> + entries about the least-executed statements were deallocated + because more distinct statements than + <varname>pg_stat_statements.max</varname> were observed + </para></entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stats_reset</structfield> <type>timestamp with time zone</type> + </para> + <para> + Time at which all statistics in the + <structname>pg_stat_statements</structname> view were last reset. + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + </sect2> + + <sect2 id="pgstatstatements-funcs"> + <title>Functions</title> + + <variablelist> + <varlistentry> + <term> + <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void</function> + <indexterm> + <primary>pg_stat_statements_reset</primary> + </indexterm> + </term> + + <listitem> + <para> + <function>pg_stat_statements_reset</function> discards statistics + gathered so far by <filename>pg_stat_statements</filename> corresponding + to the specified <structfield>userid</structfield>, <structfield>dbid</structfield> + and <structfield>queryid</structfield>. If any of the parameters are not + specified, the default value <literal>0</literal>(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 + <literal>0</literal>(invalid), it will discard all statistics. + If all statistics in the <filename>pg_stat_statements</filename> + view are discarded, it will also reset the statistics in the + <structname>pg_stat_statements_info</structname> view. + By default, this function can only be executed by superusers. + Access may be granted to others using <command>GRANT</command>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pg_stat_statements(showtext boolean) returns setof record</function> + <indexterm> + <primary>pg_stat_statements</primary> + <secondary>function</secondary> + </indexterm> + </term> + + <listitem> + <para> + The <structname>pg_stat_statements</structname> view is defined in + terms of a function also named <function>pg_stat_statements</function>. + It is possible for clients to call + the <function>pg_stat_statements</function> function directly, and by + specifying <literal>showtext := false</literal> have query text be + omitted (that is, the <literal>OUT</literal> argument that corresponds + to the view's <structfield>query</structfield> 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 <filename>pg_stat_statements</filename> 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 <structname>pg_stat_statements</structname> data. + </para> + </listitem> + </varlistentry> + </variablelist> + </sect2> + + <sect2 id="pgstatstatements-config-params"> + <title>Configuration Parameters</title> + + <variablelist> + <varlistentry> + <term> + <varname>pg_stat_statements.max</varname> (<type>integer</type>) + <indexterm> + <primary><varname>pg_stat_statements.max</varname> configuration parameter</primary> + </indexterm> + </term> + + <listitem> + <para> + <varname>pg_stat_statements.max</varname> is the maximum number of + statements tracked by the module (i.e., the maximum number of rows + in the <structname>pg_stat_statements</structname> 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 + <structname>pg_stat_statements_info</structname> view. + The default value is 5000. + This parameter can only be set at server start. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <varname>pg_stat_statements.track</varname> (<type>enum</type>) + <indexterm> + <primary><varname>pg_stat_statements.track</varname> configuration parameter</primary> + </indexterm> + </term> + + <listitem> + <para> + <varname>pg_stat_statements.track</varname> controls which statements + are counted by the module. + Specify <literal>top</literal> to track top-level statements (those issued + directly by clients), <literal>all</literal> to also track nested statements + (such as statements invoked within functions), or <literal>none</literal> to + disable statement statistics collection. + The default value is <literal>top</literal>. + Only superusers can change this setting. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <varname>pg_stat_statements.track_utility</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>pg_stat_statements.track_utility</varname> configuration parameter</primary> + </indexterm> + </term> + + <listitem> + <para> + <varname>pg_stat_statements.track_utility</varname> controls whether + utility commands are tracked by the module. Utility commands are + all those other than <command>SELECT</command>, <command>INSERT</command>, + <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>. + The default value is <literal>on</literal>. + Only superusers can change this setting. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <varname>pg_stat_statements.track_planning</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>pg_stat_statements.track_planning</varname> configuration parameter</primary> + </indexterm> + </term> + + <listitem> + <para> + <varname>pg_stat_statements.track_planning</varname> 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 + <structname>pg_stat_statements</structname> entries. + The default value is <literal>off</literal>. + Only superusers can change this setting. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <varname>pg_stat_statements.save</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>pg_stat_statements.save</varname> configuration parameter</primary> + </indexterm> + </term> + + <listitem> + <para> + <varname>pg_stat_statements.save</varname> specifies whether to + save statement statistics across server shutdowns. + If it is <literal>off</literal> then statistics are not saved at + shutdown nor reloaded at server start. + The default value is <literal>on</literal>. + This parameter can only be set in the <filename>postgresql.conf</filename> + file or on the server command line. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + The module requires additional shared memory proportional to + <varname>pg_stat_statements.max</varname>. Note that this + memory is consumed whenever the module is loaded, even if + <varname>pg_stat_statements.track</varname> is set to <literal>none</literal>. + </para> + + <para> + These parameters must be set in <filename>postgresql.conf</filename>. + Typical usage might be: + +<programlisting> +# postgresql.conf +shared_preload_libraries = 'pg_stat_statements' + +compute_query_id = on +pg_stat_statements.max = 10000 +pg_stat_statements.track = all +</programlisting> + </para> + </sect2> + + <sect2 id="pgstatstatements-sample-output"> + <title>Sample Output</title> + +<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 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 4 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;------------------ +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_exec_time | 160.588563 +rows | 0 +hit_percent | 100.0000000000000000 +-[ RECORD 5 ]---+--------------------------------------------------&zwsp;------------------ +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 ]---+--------------------------------------------------&zwsp;--------------------------- +query | SELECT pg_stat_statements_reset(0,0,0) +calls | 1 +total_exec_time | 0.189497 +rows | 1 +hit_percent | +-[ RECORD 2 ]---+--------------------------------------------------&zwsp;--------------------------- +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 | + +</screen> + </sect2> + + <sect2 id="pgstatstatements-authors"> + <title>Authors</title> + + <para> + Takahiro Itagaki <email>itagaki.takahiro@oss.ntt.co.jp</email>. + Query normalization added by Peter Geoghegan <email>peter@2ndquadrant.com</email>. + </para> + </sect2> + +</sect1> |