begriffs open source - ai-pg/blob - full-docs/src/sgml/html/runtime-config-statistics.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!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>19.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="19.8. Error Reporting and Logging" /><link rel="next" href="runtime-config-vacuum.html" title="19.10. 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">19.9. Run-time Statistics</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-logging.html" title="19.8. Error Reporting and Logging">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-vacuum.html" title="19.10. 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">19.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">19.9.1. Cumulative Query and Index Statistics</a></span></dt><dt><span class="sect2"><a href="runtime-config-statistics.html#RUNTIME-CONFIG-STATISTICS-MONITOR">19.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">19.9.1. Cumulative Query and Index Statistics <a href="#RUNTIME-CONFIG-CUMULATIVE-STATISTICS" class="id_link">#</a></h3></div></div></div><p>
   3       These parameters control the server-wide cumulative statistics system.
   4       When enabled, the data that is collected can be accessed via the
   5       <code class="structname">pg_stat</code> and <code class="structname">pg_statio</code>
   6       family of system views.  Refer to <a class="xref" href="monitoring.html" title="Chapter 27. Monitoring Database Activity">Chapter 27</a> for more
   7       information.
   8      </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>)
   9       <a id="id-1.6.6.12.2.3.1.1.3" class="indexterm"></a>
  10       </span> <a href="#GUC-TRACK-ACTIVITIES" class="id_link">#</a></dt><dd><p>
  11         Enables the collection of information on the currently
  12         executing command of each session, along with its identifier and the
  13         time when that command began execution. This parameter is on by
  14         default. Note that even when enabled, this information is only
  15         visible to superusers, roles with privileges of the
  16         <code class="literal">pg_read_all_stats</code> role and the user owning the
  17         sessions being reported on (including sessions belonging to a role they
  18         have the privileges of), so it should not represent a security risk.
  19         Only superusers and users with the appropriate <code class="literal">SET</code>
  20         privilege can change this setting.
  21        </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>)
  22       <a id="id-1.6.6.12.2.3.2.1.3" class="indexterm"></a>
  23       </span> <a href="#GUC-TRACK-ACTIVITY-QUERY-SIZE" class="id_link">#</a></dt><dd><p>
  24        Specifies the amount of memory reserved to store the text of the
  25        currently executing command for each active session, for the
  26        <code class="structname">pg_stat_activity</code>.<code class="structfield">query</code> field.
  27        If this value is specified without units, it is taken as bytes.
  28        The default value is 1024 bytes.
  29        This parameter can only be set at server start.
  30        </p></dd><dt id="GUC-TRACK-COUNTS"><span class="term"><code class="varname">track_counts</code> (<code class="type">boolean</code>)
  31       <a id="id-1.6.6.12.2.3.3.1.3" class="indexterm"></a>
  32       </span> <a href="#GUC-TRACK-COUNTS" class="id_link">#</a></dt><dd><p>
  33         Enables collection of statistics on database activity.
  34         This parameter is on by default, because the autovacuum
  35         daemon needs the collected information.
  36         Only superusers and users with the appropriate <code class="literal">SET</code>
  37         privilege can change this setting.
  38        </p></dd><dt id="GUC-TRACK-COST-DELAY-TIMING"><span class="term"><code class="varname">track_cost_delay_timing</code> (<code class="type">boolean</code>)
  39       <a id="id-1.6.6.12.2.3.4.1.3" class="indexterm"></a>
  40       </span> <a href="#GUC-TRACK-COST-DELAY-TIMING" class="id_link">#</a></dt><dd><p>
  41         Enables timing of cost-based vacuum delay (see
  42         <a class="xref" href="runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="19.10.2. Cost-based Vacuum Delay">Section 19.10.2</a>).  This parameter
  43         is off by default, as it will repeatedly query the operating system for
  44         the current time, which may cause significant overhead on some
  45         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
  46         measure the overhead of timing on your system.  Cost-based vacuum delay
  47         timing information is displayed in
  48         <a class="link" href="progress-reporting.html#VACUUM-PROGRESS-REPORTING" title="27.4.5. VACUUM Progress Reporting"><code class="structname">pg_stat_progress_vacuum</code></a>,
  49         <a class="link" href="progress-reporting.html#ANALYZE-PROGRESS-REPORTING" title="27.4.1. ANALYZE Progress Reporting"><code class="structname">pg_stat_progress_analyze</code></a>,
  50         in the output of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> and
  51         <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> when the
  52         <code class="literal">VERBOSE</code> option is used, and by autovacuum for
  53         auto-vacuums and auto-analyzes when
  54         <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a> is set.
  55         Only superusers and users with the appropriate <code class="literal">SET</code>
  56         privilege can change this setting.
  57        </p></dd><dt id="GUC-TRACK-IO-TIMING"><span class="term"><code class="varname">track_io_timing</code> (<code class="type">boolean</code>)
  58       <a id="id-1.6.6.12.2.3.5.1.3" class="indexterm"></a>
  59       </span> <a href="#GUC-TRACK-IO-TIMING" class="id_link">#</a></dt><dd><p>
  60         Enables timing of database I/O waits.  This parameter is off by
  61         default, as it will repeatedly query the operating system for
  62         the current time, which may cause significant overhead on some
  63         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
  64         measure the overhead of timing on your system.
  65         I/O timing information is
  66         displayed in <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW" title="27.2.17. pg_stat_database">
  67         <code class="structname">pg_stat_database</code></a>,
  68         <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW" title="27.2.13. pg_stat_io">
  69         <code class="structname">pg_stat_io</code></a> (if <code class="varname">object</code>
  70         is not <code class="literal">wal</code>), in the output of the
  71         <a class="link" href="monitoring-stats.html#PG-STAT-GET-BACKEND-IO">
  72         <code class="function">pg_stat_get_backend_io()</code></a> function (if
  73         <code class="varname">object</code> is not <code class="literal">wal</code>), in the
  74         output of <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a> when the <code class="literal">BUFFERS</code>
  75         option is used, in the output of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> when
  76         the <code class="literal">VERBOSE</code> option is used, by autovacuum
  77         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
  78         <a class="xref" href="pgstatstatements.html" title="F.32. pg_stat_statements — track statistics of SQL planning and execution">pg_stat_statements</a>.
  79         Only superusers and users with the appropriate <code class="literal">SET</code>
  80         privilege can change this setting.
  81        </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>)
  82       <a id="id-1.6.6.12.2.3.6.1.3" class="indexterm"></a>
  83       </span> <a href="#GUC-TRACK-WAL-IO-TIMING" class="id_link">#</a></dt><dd><p>
  84         Enables timing of WAL I/O waits. This parameter is off by default,
  85         as it will repeatedly query the operating system for the current time,
  86         which may cause significant overhead on some platforms.
  87         You can use the <span class="application">pg_test_timing</span> tool to
  88         measure the overhead of timing on your system.
  89         I/O timing information is displayed in
  90         <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW" title="27.2.13. pg_stat_io">
  91         <code class="structname">pg_stat_io</code></a> for the
  92         <code class="varname">object</code> <code class="literal">wal</code> and in the output of
  93         the <a class="link" href="monitoring-stats.html#PG-STAT-GET-BACKEND-IO">
  94         <code class="function">pg_stat_get_backend_io()</code></a> function for the
  95         <code class="varname">object</code> <code class="literal">wal</code>.
  96         Only superusers and users with the appropriate <code class="literal">SET</code>
  97         privilege can change this setting.
  98        </p></dd><dt id="GUC-TRACK-FUNCTIONS"><span class="term"><code class="varname">track_functions</code> (<code class="type">enum</code>)
  99       <a id="id-1.6.6.12.2.3.7.1.3" class="indexterm"></a>
 100       </span> <a href="#GUC-TRACK-FUNCTIONS" class="id_link">#</a></dt><dd><p>
 101         Enables tracking of function call counts and time used. Specify
 102         <code class="literal">pl</code> to track only procedural-language functions,
 103         <code class="literal">all</code> to also track SQL and C language functions.
 104         The default is <code class="literal">none</code>, which disables function
 105         statistics tracking.
 106         Only superusers and users with the appropriate <code class="literal">SET</code>
 107         privilege can change this setting.
 108        </p><div class="note"><h3 class="title">Note</h3><p>
 109          SQL-language functions that are simple enough to be <span class="quote">“<span class="quote">inlined</span>”</span>
 110          into the calling query will not be tracked, regardless of this
 111          setting.
 112         </p></div></dd><dt id="GUC-STATS-FETCH-CONSISTENCY"><span class="term"><code class="varname">stats_fetch_consistency</code> (<code class="type">enum</code>)
 113       <a id="id-1.6.6.12.2.3.8.1.3" class="indexterm"></a>
 114       </span> <a href="#GUC-STATS-FETCH-CONSISTENCY" class="id_link">#</a></dt><dd><p>
 115         Determines the behavior when cumulative statistics are accessed
 116         multiple times within a transaction. When set to
 117         <code class="literal">none</code>, each access re-fetches counters from shared
 118         memory. When set to <code class="literal">cache</code>, the first access to
 119         statistics for an object caches those statistics until the end of the
 120         transaction unless <code class="function">pg_stat_clear_snapshot()</code> is
 121         called. When set to <code class="literal">snapshot</code>, the first statistics
 122         access caches all statistics accessible in the current database, until
 123         the end of the transaction unless
 124         <code class="function">pg_stat_clear_snapshot()</code> is called. Changing this
 125         parameter in a transaction discards the statistics snapshot.
 126         The default is <code class="literal">cache</code>.
 127        </p><div class="note"><h3 class="title">Note</h3><p>
 128          <code class="literal">none</code> is most suitable for monitoring systems. If
 129          values are only accessed once, it is the most
 130          efficient. <code class="literal">cache</code> ensures repeat accesses yield the
 131          same values, which is important for queries involving
 132          e.g. self-joins. <code class="literal">snapshot</code> can be useful when
 133          interactively inspecting statistics, but has higher overhead,
 134          particularly if many database objects exist.
 135         </p></div></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-STATISTICS-MONITOR"><div class="titlepage"><div><div><h3 class="title">19.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>)
 136       <a id="id-1.6.6.12.3.2.1.1.3" class="indexterm"></a>
 137       </span> <a href="#GUC-COMPUTE-QUERY-ID" class="id_link">#</a></dt><dd><p>
 138         Enables in-core computation of a query identifier.
 139         Query identifiers can be displayed in the <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity"><code class="structname">pg_stat_activity</code></a>
 140         view, using <code class="command">EXPLAIN</code>, or emitted in the log if
 141         configured via the <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> parameter.
 142         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
 143         identifier to be computed.  Note that an external module can
 144         alternatively be used if the in-core query identifier computation
 145         method is not acceptable.  In this case, in-core computation
 146         must be always disabled.
 147         Valid values are <code class="literal">off</code> (always disabled),
 148         <code class="literal">on</code> (always enabled), <code class="literal">auto</code>,
 149         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>
 150         automatically enable it, and <code class="literal">regress</code> which
 151         has the same effect as <code class="literal">auto</code>, except that the
 152         query identifier is not shown in the <code class="literal">EXPLAIN</code> output
 153         in order to facilitate automated regression testing.
 154         The default is <code class="literal">auto</code>.
 155        </p><div class="note"><h3 class="title">Note</h3><p>
 156          To ensure that only one query identifier is calculated and
 157          displayed, extensions that calculate query identifiers should
 158          throw an error if a query identifier has already been computed.
 159         </p></div></dd><dt id="GUC-LOG-STATEMENT-STATS"><span class="term"><code class="varname">log_statement_stats</code> (<code class="type">boolean</code>)
 160       <a id="id-1.6.6.12.3.2.2.1.3" class="indexterm"></a>
 161       <br /></span><span class="term"><code class="varname">log_parser_stats</code> (<code class="type">boolean</code>)
 162       <a id="id-1.6.6.12.3.2.2.2.3" class="indexterm"></a>
 163       <br /></span><span class="term"><code class="varname">log_planner_stats</code> (<code class="type">boolean</code>)
 164       <a id="id-1.6.6.12.3.2.2.3.3" class="indexterm"></a>
 165       <br /></span><span class="term"><code class="varname">log_executor_stats</code> (<code class="type">boolean</code>)
 166       <a id="id-1.6.6.12.3.2.2.4.3" class="indexterm"></a>
 167       </span> <a href="#GUC-LOG-STATEMENT-STATS" class="id_link">#</a></dt><dd><p>
 168         For each query, output performance statistics of the respective
 169         module to the server log. This is a crude profiling
 170         instrument, similar to the Unix <code class="function">getrusage()</code> operating
 171         system facility.  <code class="varname">log_statement_stats</code> reports total
 172         statement statistics, while the others report per-module statistics.
 173         <code class="varname">log_statement_stats</code> cannot be enabled together with
 174         any of the per-module options.  All of these options are disabled by
 175         default.
 176         Only superusers and users with the appropriate <code class="literal">SET</code>
 177         privilege can change these settings.
 178        </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="19.8. Error Reporting and Logging">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-vacuum.html" title="19.10. Vacuuming">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.8. Error Reporting and Logging </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.10. Vacuuming</td></tr></table></div></body></html>