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 16.2 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 <a href="#RUNTIME-CONFIG-STATISTICS" class="id_link">#</a></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 <a href="#RUNTIME-CONFIG-CUMULATIVE-STATISTICS" class="id_link">#</a></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> <a href="#GUC-TRACK-ACTIVITIES" class="id_link">#</a></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> <a href="#GUC-TRACK-ACTIVITY-QUERY-SIZE" class="id_link">#</a></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> <a href="#GUC-TRACK-COUNTS" class="id_link">#</a></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> <a href="#GUC-TRACK-IO-TIMING" class="id_link">#</a></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.16. 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 — track statistics of SQL planning and execution">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> <a href="#GUC-TRACK-WAL-IO-TIMING" class="id_link">#</a></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.15. 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> <a href="#GUC-TRACK-FUNCTIONS" class="id_link">#</a></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> <a href="#GUC-STATS-FETCH-CONSISTENCY" class="id_link">#</a></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 <a href="#RUNTIME-CONFIG-STATISTICS-MONITOR" class="id_link">#</a></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> <a href="#GUC-COMPUTE-QUERY-ID" class="id_link">#</a></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 — track statistics of SQL planning and execution">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 — track statistics of SQL planning and execution">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 id="GUC-LOG-STATEMENT-STATS"><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> <a href="#GUC-LOG-STATEMENT-STATS" class="id_link">#</a></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 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.10. Automatic Vacuuming</td></tr></table></div></body></html>
|
