summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/auto-explain.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-16 19:46:48 +0000
commit311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/auto-explain.html
parentInitial commit. (diff)
downloadpostgresql-15-upstream.tar.xz
postgresql-15-upstream.zip
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/auto-explain.html')
-rw-r--r--doc/src/sgml/html/auto-explain.html189
1 files changed, 189 insertions, 0 deletions
diff --git a/doc/src/sgml/html/auto-explain.html b/doc/src/sgml/html/auto-explain.html
new file mode 100644
index 0000000..8527d17
--- /dev/null
+++ b/doc/src/sgml/html/auto-explain.html
@@ -0,0 +1,189 @@
+<?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>F.4. auto_explain</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="auth-delay.html" title="F.3. auth_delay" /><link rel="next" href="basebackup-to-shell.html" title="F.5. basebackup_to_shell" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.4. auto_explain</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="auth-delay.html" title="F.3. auth_delay">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="basebackup-to-shell.html" title="F.5. basebackup_to_shell">Next</a></td></tr></table><hr /></div><div class="sect1" id="AUTO-EXPLAIN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.4. auto_explain</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="auto-explain.html#id-1.11.7.13.5">F.4.1. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="auto-explain.html#id-1.11.7.13.6">F.4.2. Example</a></span></dt><dt><span class="sect2"><a href="auto-explain.html#id-1.11.7.13.7">F.4.3. Author</a></span></dt></dl></div><a id="id-1.11.7.13.2" class="indexterm"></a><p>
+ The <code class="filename">auto_explain</code> module provides a means for
+ logging execution plans of slow statements automatically, without
+ having to run <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a>
+ by hand. This is especially helpful for tracking down un-optimized queries
+ in large applications.
+ </p><p>
+ The module provides no SQL-accessible functions. To use it, simply
+ load it into the server. You can load it into an individual session:
+
+</p><pre class="programlisting">
+LOAD 'auto_explain';
+</pre><p>
+
+ (You must be superuser to do that.) More typical usage is to preload
+ it into some or all sessions by including <code class="literal">auto_explain</code> in
+ <a class="xref" href="runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES">session_preload_libraries</a> or
+ <a class="xref" href="runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES">shared_preload_libraries</a> in
+ <code class="filename">postgresql.conf</code>. Then you can track unexpectedly slow queries
+ no matter when they happen. Of course there is a price in overhead for
+ that.
+ </p><div class="sect2" id="id-1.11.7.13.5"><div class="titlepage"><div><div><h3 class="title">F.4.1. Configuration Parameters</h3></div></div></div><p>
+ There are several configuration parameters that control the behavior of
+ <code class="filename">auto_explain</code>. Note that the default behavior is
+ to do nothing, so you must set at least
+ <code class="varname">auto_explain.log_min_duration</code> if you want any results.
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">
+ <code class="varname">auto_explain.log_min_duration</code> (<code class="type">integer</code>)
+ <a id="id-1.11.7.13.5.3.1.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_min_duration</code> is the minimum statement
+ execution time, in milliseconds, that will cause the statement's plan to
+ be logged. Setting this to <code class="literal">0</code> logs all plans.
+ <code class="literal">-1</code> (the default) disables logging of plans. For
+ example, if you set it to <code class="literal">250ms</code> then all statements
+ that run 250ms or longer will be logged. Only superusers can change this
+ setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_analyze</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.2.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_analyze</code> causes <code class="command">EXPLAIN ANALYZE</code>
+ output, rather than just <code class="command">EXPLAIN</code> output, to be printed
+ when an execution plan is logged. This parameter is off by default.
+ Only superusers can change this setting.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When this parameter is on, per-plan-node timing occurs for all
+ statements executed, whether or not they run long enough to actually
+ get logged. This can have an extremely negative impact on performance.
+ Turning off <code class="varname">auto_explain.log_timing</code> ameliorates the
+ performance cost, at the price of obtaining less information.
+ </p></div></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_buffers</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.3.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_buffers</code> controls whether buffer
+ usage statistics are printed when an execution plan is logged; it's
+ equivalent to the <code class="literal">BUFFERS</code> option of <code class="command">EXPLAIN</code>.
+ This parameter has no effect
+ unless <code class="varname">auto_explain.log_analyze</code> is enabled.
+ This parameter is off by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_wal</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.4.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_wal</code> controls whether WAL
+ usage statistics are printed when an execution plan is logged; it's
+ equivalent to the <code class="literal">WAL</code> option of <code class="command">EXPLAIN</code>.
+ This parameter has no effect
+ unless <code class="varname">auto_explain.log_analyze</code> is enabled.
+ This parameter is off by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_timing</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.5.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_timing</code> controls whether per-node
+ timing information is printed when an execution plan is logged; it's
+ equivalent to the <code class="literal">TIMING</code> option of <code class="command">EXPLAIN</code>.
+ The overhead of repeatedly reading the system clock can slow down
+ queries significantly on some systems, so it may be useful to set this
+ parameter to off when only actual row counts, and not exact times, are
+ needed.
+ This parameter has no effect
+ unless <code class="varname">auto_explain.log_analyze</code> is enabled.
+ This parameter is on by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_triggers</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.6.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_triggers</code> causes trigger
+ execution statistics to be included when an execution plan is logged.
+ This parameter has no effect
+ unless <code class="varname">auto_explain.log_analyze</code> is enabled.
+ This parameter is off by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_verbose</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.7.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_verbose</code> controls whether verbose
+ details are printed when an execution plan is logged; it's
+ equivalent to the <code class="literal">VERBOSE</code> option of <code class="command">EXPLAIN</code>.
+ This parameter is off by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_settings</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.8.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_settings</code> controls whether information
+ about modified configuration options is printed when an execution plan is logged.
+ Only options affecting query planning with value different from the built-in
+ default value are included in the output. This parameter is off by default.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_format</code> (<code class="type">enum</code>)
+ <a id="id-1.11.7.13.5.3.9.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_format</code> selects the
+ <code class="command">EXPLAIN</code> output format to be used.
+ The allowed values are <code class="literal">text</code>, <code class="literal">xml</code>,
+ <code class="literal">json</code>, and <code class="literal">yaml</code>. The default is text.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_level</code> (<code class="type">enum</code>)
+ <a id="id-1.11.7.13.5.3.10.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_level</code> selects the log level at which
+ auto_explain will log the query plan.
+ Valid values are <code class="literal">DEBUG5</code>, <code class="literal">DEBUG4</code>,
+ <code class="literal">DEBUG3</code>, <code class="literal">DEBUG2</code>,
+ <code class="literal">DEBUG1</code>, <code class="literal">INFO</code>,
+ <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>,
+ and <code class="literal">LOG</code>. The default is <code class="literal">LOG</code>.
+ Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.log_nested_statements</code> (<code class="type">boolean</code>)
+ <a id="id-1.11.7.13.5.3.11.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.log_nested_statements</code> causes nested
+ statements (statements executed inside a function) to be considered
+ for logging. When it is off, only top-level query plans are logged. This
+ parameter is off by default. Only superusers can change this setting.
+ </p></dd><dt><span class="term">
+ <code class="varname">auto_explain.sample_rate</code> (<code class="type">real</code>)
+ <a id="id-1.11.7.13.5.3.12.1.3" class="indexterm"></a>
+ </span></dt><dd><p>
+ <code class="varname">auto_explain.sample_rate</code> causes auto_explain to only
+ explain a fraction of the statements in each session. The default is 1,
+ meaning explain all the queries. In case of nested statements, either all
+ will be explained or none. Only superusers can change this setting.
+ </p></dd></dl></div><p>
+ In ordinary usage, these parameters are set
+ in <code class="filename">postgresql.conf</code>, although superusers can alter them
+ on-the-fly within their own sessions.
+ Typical usage might be:
+ </p><pre class="programlisting">
+# postgresql.conf
+session_preload_libraries = 'auto_explain'
+
+auto_explain.log_min_duration = '3s'
+</pre></div><div class="sect2" id="id-1.11.7.13.6"><div class="titlepage"><div><div><h3 class="title">F.4.2. Example</h3></div></div></div><pre class="programlisting">
+postgres=# LOAD 'auto_explain';
+postgres=# SET auto_explain.log_min_duration = 0;
+postgres=# SET auto_explain.log_analyze = true;
+postgres=# SELECT count(*)
+ FROM pg_class, pg_index
+ WHERE oid = indrelid AND indisunique;
+</pre><p>
+ This might produce log output such as:
+ </p><pre class="screen">
+LOG: duration: 3.651 ms plan:
+ Query Text: SELECT count(*)
+ FROM pg_class, pg_index
+ WHERE oid = indrelid AND indisunique;
+ Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1)
+ -&gt; Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1)
+ Hash Cond: (pg_class.oid = pg_index.indrelid)
+ -&gt; Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1)
+ -&gt; Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 4kB
+ -&gt; Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1)
+ Filter: indisunique
+</pre></div><div class="sect2" id="id-1.11.7.13.7"><div class="titlepage"><div><div><h3 class="title">F.4.3. Author</h3></div></div></div><p>
+ Takahiro Itagaki <code class="email">&lt;<a class="email" href="mailto:itagaki.takahiro@oss.ntt.co.jp">itagaki.takahiro@oss.ntt.co.jp</a>&gt;</code>
+ </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="auth-delay.html" title="F.3. auth_delay">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="basebackup-to-shell.html" title="F.5. basebackup_to_shell">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.3. auth_delay </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.5. basebackup_to_shell</td></tr></table></div></body></html> \ No newline at end of file