codekingpro/portable-devtools
115k
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>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.3 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>3 These parameters control the server-wide cumulative statistics system.4 When enabled, the data that is collected can be accessed via the5 <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 28. Monitoring Database Activity">Chapter 28</a> for more7 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.7.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 currently12 executing command of each session, along with its identifier and the13 time when that command began execution. This parameter is on by14 default. Note that even when enabled, this information is only15 visible to superusers, roles with privileges of the16 <code class="literal">pg_read_all_stats</code> role and the user owning the17 sessions being reported on (including sessions belonging to a role they18 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.7.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 the25 currently executing command for each active session, for the26 <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.7.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 autovacuum35 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-IO-TIMING"><span class="term"><code class="varname">track_io_timing</code> (<code class="type">boolean</code>)39 <a id="id-1.6.7.12.2.3.4.1.3" class="indexterm"></a>40 </span> <a href="#GUC-TRACK-IO-TIMING" class="id_link">#</a></dt><dd><p>41 Enables timing of database I/O calls. This parameter is off by42 default, as it will repeatedly query the operating system for43 the current time, which may cause significant overhead on some44 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 to45 measure the overhead of timing on your system.46 I/O timing information is47 displayed in <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW" title="28.2.16. pg_stat_database">48 <code class="structname">pg_stat_database</code></a>, in the output of49 <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a> when the <code class="literal">BUFFERS</code> option50 is used, in the output of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> when51 the <code class="literal">VERBOSE</code> option is used, by autovacuum52 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 by53 <a class="xref" href="pgstatstatements.html" title="F.32. pg_stat_statements — track statistics of SQL planning and execution">pg_stat_statements</a>.54 Only superusers and users with the appropriate <code class="literal">SET</code>55 privilege can change this setting.56 </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>)57 <a id="id-1.6.7.12.2.3.5.1.3" class="indexterm"></a>58 </span> <a href="#GUC-TRACK-WAL-IO-TIMING" class="id_link">#</a></dt><dd><p>59 Enables timing of WAL I/O calls. This parameter is off by default,60 as it will repeatedly query the operating system for the current time,61 which may cause significant overhead on some platforms.62 You can use the <span class="application">pg_test_timing</span> tool to63 measure the overhead of timing on your system.64 I/O timing information is65 displayed in <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW" title="28.2.15. pg_stat_wal">66 <code class="structname">pg_stat_wal</code></a>.67 Only superusers and users with the appropriate <code class="literal">SET</code>68 privilege can change this setting.69 </p></dd><dt id="GUC-TRACK-FUNCTIONS"><span class="term"><code class="varname">track_functions</code> (<code class="type">enum</code>)70 <a id="id-1.6.7.12.2.3.6.1.3" class="indexterm"></a>71 </span> <a href="#GUC-TRACK-FUNCTIONS" class="id_link">#</a></dt><dd><p>72 Enables tracking of function call counts and time used. Specify73 <code class="literal">pl</code> to track only procedural-language functions,74 <code class="literal">all</code> to also track SQL and C language functions.75 The default is <code class="literal">none</code>, which disables function76 statistics tracking.77 Only superusers and users with the appropriate <code class="literal">SET</code>78 privilege can change this setting.79 </p><div class="note"><h3 class="title">Note</h3><p>80 SQL-language functions that are simple enough to be <span class="quote">“<span class="quote">inlined</span>”</span>81 into the calling query will not be tracked, regardless of this82 setting.83 </p></div></dd><dt id="GUC-STATS-FETCH-CONSISTENCY"><span class="term"><code class="varname">stats_fetch_consistency</code> (<code class="type">enum</code>)84 <a id="id-1.6.7.12.2.3.7.1.3" class="indexterm"></a>85 </span> <a href="#GUC-STATS-FETCH-CONSISTENCY" class="id_link">#</a></dt><dd><p>86 Determines the behavior when cumulative statistics are accessed87 multiple times within a transaction. When set to88 <code class="literal">none</code>, each access re-fetches counters from shared89 memory. When set to <code class="literal">cache</code>, the first access to90 statistics for an object caches those statistics until the end of the91 transaction unless <code class="function">pg_stat_clear_snapshot()</code> is92 called. When set to <code class="literal">snapshot</code>, the first statistics93 access caches all statistics accessible in the current database, until94 the end of the transaction unless95 <code class="function">pg_stat_clear_snapshot()</code> is called. Changing this96 parameter in a transaction discards the statistics snapshot.97 The default is <code class="literal">cache</code>.98 </p><div class="note"><h3 class="title">Note</h3><p>99 <code class="literal">none</code> is most suitable for monitoring systems. If100 values are only accessed once, it is the most101 efficient. <code class="literal">cache</code> ensures repeat accesses yield the102 same values, which is important for queries involving103 e.g. self-joins. <code class="literal">snapshot</code> can be useful when104 interactively inspecting statistics, but has higher overhead,105 particularly if many database objects exist.106 </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>)107 <a id="id-1.6.7.12.3.2.1.1.3" class="indexterm"></a>108 </span> <a href="#GUC-COMPUTE-QUERY-ID" class="id_link">#</a></dt><dd><p>109 Enables in-core computation of a query identifier.110 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>111 view, using <code class="command">EXPLAIN</code>, or emitted in the log if112 configured via the <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> parameter.113 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 query114 identifier to be computed. Note that an external module can115 alternatively be used if the in-core query identifier computation116 method is not acceptable. In this case, in-core computation117 must be always disabled.118 Valid values are <code class="literal">off</code> (always disabled),119 <code class="literal">on</code> (always enabled), <code class="literal">auto</code>,120 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>121 automatically enable it, and <code class="literal">regress</code> which122 has the same effect as <code class="literal">auto</code>, except that the123 query identifier is not shown in the <code class="literal">EXPLAIN</code> output124 in order to facilitate automated regression testing.125 The default is <code class="literal">auto</code>.126 </p><div class="note"><h3 class="title">Note</h3><p>127 To ensure that only one query identifier is calculated and128 displayed, extensions that calculate query identifiers should129 throw an error if a query identifier has already been computed.130 </p></div></dd><dt id="GUC-LOG-STATEMENT-STATS"><span class="term"><code class="varname">log_statement_stats</code> (<code class="type">boolean</code>)131 <a id="id-1.6.7.12.3.2.2.1.3" class="indexterm"></a>132 <br /></span><span class="term"><code class="varname">log_parser_stats</code> (<code class="type">boolean</code>)133 <a id="id-1.6.7.12.3.2.2.2.3" class="indexterm"></a>134 <br /></span><span class="term"><code class="varname">log_planner_stats</code> (<code class="type">boolean</code>)135 <a id="id-1.6.7.12.3.2.2.3.3" class="indexterm"></a>136 <br /></span><span class="term"><code class="varname">log_executor_stats</code> (<code class="type">boolean</code>)137 <a id="id-1.6.7.12.3.2.2.4.3" class="indexterm"></a>138 </span> <a href="#GUC-LOG-STATEMENT-STATS" class="id_link">#</a></dt><dd><p>139 For each query, output performance statistics of the respective140 module to the server log. This is a crude profiling141 instrument, similar to the Unix <code class="function">getrusage()</code> operating142 system facility. <code class="varname">log_statement_stats</code> reports total143 statement statistics, while the others report per-module statistics.144 <code class="varname">log_statement_stats</code> cannot be enabled together with145 any of the per-module options. All of these options are disabled by146 default.147 Only superusers and users with the appropriate <code class="literal">SET</code>148 privilege can change these settings.149 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.10. Automatic Vacuuming</td></tr></table></div></body></html>