summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/runtime-config-statistics.html
blob: e9f9279cad6a6676afb9758e939b03a54433bdc3 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
<?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>20.9. Run-time Statistics</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="runtime-config-logging.html" title="20.8. Error Reporting and Logging" /><link rel="next" href="runtime-config-autovacuum.html" title="20.10. Automatic Vacuuming" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.9. Run-time Statistics</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-logging.html" title="20.8. Error Reporting and Logging">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-autovacuum.html" title="20.10. Automatic Vacuuming">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-STATISTICS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.9. Run-time Statistics</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-statistics.html#RUNTIME-CONFIG-CUMULATIVE-STATISTICS">20.9.1. Cumulative Query and Index Statistics</a></span></dt><dt><span class="sect2"><a href="runtime-config-statistics.html#RUNTIME-CONFIG-STATISTICS-MONITOR">20.9.2. Statistics Monitoring</a></span></dt></dl></div><div class="sect2" id="RUNTIME-CONFIG-CUMULATIVE-STATISTICS"><div class="titlepage"><div><div><h3 class="title">20.9.1. Cumulative Query and Index Statistics</h3></div></div></div><p>
      These parameters control the server-wide cumulative statistics system.
      When enabled, the data that is collected can be accessed via the
      <code class="structname">pg_stat</code> and <code class="structname">pg_statio</code>
      family of system views.  Refer to <a class="xref" href="monitoring.html" title="Chapter 28. Monitoring Database Activity">Chapter 28</a> for more
      information.
     </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-TRACK-ACTIVITIES"><span class="term"><code class="varname">track_activities</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.2.3.1.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables the collection of information on the currently
        executing command of each session, along with its identifier and the
        time when that command began execution. This parameter is on by
        default. Note that even when enabled, this information is only
        visible to superusers, roles with privileges of the
        <code class="literal">pg_read_all_stats</code> role and the user owning the
        sessions being reported on (including sessions belonging to a role they
        have the privileges of), so it should not represent a security risk.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change this setting.
       </p></dd><dt id="GUC-TRACK-ACTIVITY-QUERY-SIZE"><span class="term"><code class="varname">track_activity_query_size</code> (<code class="type">integer</code>)
      <a id="id-1.6.7.12.2.3.2.1.3" class="indexterm"></a>
      </span></dt><dd><p>
       Specifies the amount of memory reserved to store the text of the
       currently executing command for each active session, for the
       <code class="structname">pg_stat_activity</code>.<code class="structfield">query</code> field.
       If this value is specified without units, it is taken as bytes.
       The default value is 1024 bytes.
       This parameter can only be set at server start.
       </p></dd><dt id="GUC-TRACK-COUNTS"><span class="term"><code class="varname">track_counts</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.2.3.3.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables collection of statistics on database activity.
        This parameter is on by default, because the autovacuum
        daemon needs the collected information.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change this setting.
       </p></dd><dt id="GUC-TRACK-IO-TIMING"><span class="term"><code class="varname">track_io_timing</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.2.3.4.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables timing of database I/O calls.  This parameter is off by
        default, as it will repeatedly query the operating system for
        the current time, which may cause significant overhead on some
        platforms.  You can use the <a class="xref" href="pgtesttiming.html" title="pg_test_timing"><span class="refentrytitle"><span class="application">pg_test_timing</span></span></a> tool to
        measure the overhead of timing on your system.
        I/O timing information is
        displayed in <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW" title="28.2.15. pg_stat_database">
        <code class="structname">pg_stat_database</code></a>, in the output of
        <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a> when the <code class="literal">BUFFERS</code> option
        is used, in the output of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> when
        the <code class="literal">VERBOSE</code> option is used, by autovacuum
        for auto-vacuums and auto-analyzes, when <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a> is set and by
        <a class="xref" href="pgstatstatements.html" title="F.32. pg_stat_statements">pg_stat_statements</a>.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change this setting.
       </p></dd><dt id="GUC-TRACK-WAL-IO-TIMING"><span class="term"><code class="varname">track_wal_io_timing</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.2.3.5.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables timing of WAL I/O calls. This parameter is off by default,
        as it will repeatedly query the operating system for the current time,
        which may cause significant overhead on some platforms.
        You can use the <span class="application">pg_test_timing</span> tool to
        measure the overhead of timing on your system.
        I/O timing information is
        displayed in <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW" title="28.2.14. pg_stat_wal">
        <code class="structname">pg_stat_wal</code></a>.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change this setting.
       </p></dd><dt id="GUC-TRACK-FUNCTIONS"><span class="term"><code class="varname">track_functions</code> (<code class="type">enum</code>)
      <a id="id-1.6.7.12.2.3.6.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables tracking of function call counts and time used. Specify
        <code class="literal">pl</code> to track only procedural-language functions,
        <code class="literal">all</code> to also track SQL and C language functions.
        The default is <code class="literal">none</code>, which disables function
        statistics tracking.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change this setting.
       </p><div class="note"><h3 class="title">Note</h3><p>
         SQL-language functions that are simple enough to be <span class="quote"><span class="quote">inlined</span></span>
         into the calling query will not be tracked, regardless of this
         setting.
        </p></div></dd><dt id="GUC-STATS-FETCH-CONSISTENCY"><span class="term"><code class="varname">stats_fetch_consistency</code> (<code class="type">enum</code>)
      <a id="id-1.6.7.12.2.3.7.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Determines the behavior when cumulative statistics are accessed
        multiple times within a transaction. When set to
        <code class="literal">none</code>, each access re-fetches counters from shared
        memory. When set to <code class="literal">cache</code>, the first access to
        statistics for an object caches those statistics until the end of the
        transaction unless <code class="function">pg_stat_clear_snapshot()</code> is
        called. When set to <code class="literal">snapshot</code>, the first statistics
        access caches all statistics accessible in the current database, until
        the end of the transaction unless
        <code class="function">pg_stat_clear_snapshot()</code> is called. Changing this
        parameter in a transaction discards the statistics snapshot.
        The default is <code class="literal">cache</code>.
       </p><div class="note"><h3 class="title">Note</h3><p>
         <code class="literal">none</code> is most suitable for monitoring systems. If
         values are only accessed once, it is the most
         efficient. <code class="literal">cache</code> ensures repeat accesses yield the
         same values, which is important for queries involving
         e.g. self-joins. <code class="literal">snapshot</code> can be useful when
         interactively inspecting statistics, but has higher overhead,
         particularly if many database objects exist.
        </p></div></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-STATISTICS-MONITOR"><div class="titlepage"><div><div><h3 class="title">20.9.2. Statistics Monitoring</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-COMPUTE-QUERY-ID"><span class="term"><code class="varname">compute_query_id</code> (<code class="type">enum</code>)
      <a id="id-1.6.7.12.3.2.1.1.3" class="indexterm"></a>
      </span></dt><dd><p>
        Enables in-core computation of a query identifier.
        Query identifiers can be displayed in the <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="28.2.3. pg_stat_activity"><code class="structname">pg_stat_activity</code></a>
        view, using <code class="command">EXPLAIN</code>, or emitted in the log if
        configured via the <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> parameter.
        The <a class="xref" href="pgstatstatements.html" title="F.32. pg_stat_statements">pg_stat_statements</a> extension also requires a query
        identifier to be computed.  Note that an external module can
        alternatively be used if the in-core query identifier computation
        method is not acceptable.  In this case, in-core computation
        must be always disabled.
        Valid values are <code class="literal">off</code> (always disabled),
        <code class="literal">on</code> (always enabled), <code class="literal">auto</code>,
        which lets modules such as <a class="xref" href="pgstatstatements.html" title="F.32. pg_stat_statements">pg_stat_statements</a>
        automatically enable it, and <code class="literal">regress</code> which
        has the same effect as <code class="literal">auto</code>, except that the
        query identifier is not shown in the <code class="literal">EXPLAIN</code> output
        in order to facilitate automated regression testing.
        The default is <code class="literal">auto</code>.
       </p><div class="note"><h3 class="title">Note</h3><p>
         To ensure that only one query identifier is calculated and
         displayed, extensions that calculate query identifiers should
         throw an error if a query identifier has already been computed.
        </p></div></dd><dt><span class="term"><code class="varname">log_statement_stats</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.3.2.2.1.3" class="indexterm"></a>
      <br /></span><span class="term"><code class="varname">log_parser_stats</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.3.2.2.2.3" class="indexterm"></a>
      <br /></span><span class="term"><code class="varname">log_planner_stats</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.3.2.2.3.3" class="indexterm"></a>
      <br /></span><span class="term"><code class="varname">log_executor_stats</code> (<code class="type">boolean</code>)
      <a id="id-1.6.7.12.3.2.2.4.3" class="indexterm"></a>
      </span></dt><dd><p>
        For each query, output performance statistics of the respective
        module to the server log. This is a crude profiling
        instrument, similar to the Unix <code class="function">getrusage()</code> operating
        system facility.  <code class="varname">log_statement_stats</code> reports total
        statement statistics, while the others report per-module statistics.
        <code class="varname">log_statement_stats</code> cannot be enabled together with
        any of the per-module options.  All of these options are disabled by
        default.
        Only superusers and users with the appropriate <code class="literal">SET</code>
        privilege can change these settings.
       </p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-logging.html" title="20.8. Error Reporting and Logging">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-autovacuum.html" title="20.10. Automatic Vacuuming">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.8. Error Reporting and Logging </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.10. Automatic Vacuuming</td></tr></table></div></body></html>