<!-- doc/src/sgml/auto-explain.sgml -->

<sect1 id="auto-explain" xreflabel="auto_explain">
 <title>auto_explain &mdash; log execution plans of slow queries</title>

 <indexterm zone="auto-explain">
  <primary>auto_explain</primary>
 </indexterm>

 <para>
  The <filename>auto_explain</filename> module provides a means for
  logging execution plans of slow statements automatically, without
  having to run <xref linkend="sql-explain"/>
  by hand.  This is especially helpful for tracking down un-optimized queries
  in large applications.
 </para>

 <para>
  The module provides no SQL-accessible functions.  To use it, simply
  load it into the server.  You can load it into an individual session:

<programlisting>
LOAD 'auto_explain';
</programlisting>

  (You must be superuser to do that.)  More typical usage is to preload
  it into some or all sessions by including <literal>auto_explain</literal> in
  <xref linkend="guc-session-preload-libraries"/> or
  <xref linkend="guc-shared-preload-libraries"/> in
  <filename>postgresql.conf</filename>.  Then you can track unexpectedly slow queries
  no matter when they happen.  Of course there is a price in overhead for
  that.
 </para>

 <sect2 id="auto-explain-configuration-parameters">
  <title>Configuration Parameters</title>

 <para>
  There are several configuration parameters that control the behavior of
  <filename>auto_explain</filename>.  Note that the default behavior is
  to do nothing, so you must set at least
  <varname>auto_explain.log_min_duration</varname> if you want any results.
 </para>

  <variablelist>
   <varlistentry id="auto-explain-configuration-parameters-log-min-duration">
    <term>
     <varname>auto_explain.log_min_duration</varname> (<type>integer</type>)
     <indexterm>
      <primary><varname>auto_explain.log_min_duration</varname> configuration parameter</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      <varname>auto_explain.log_min_duration</varname> is the minimum statement
      execution time, in milliseconds, that will cause the statement's plan to
      be logged. Setting this to <literal>0</literal> logs all plans.
      <literal>-1</literal> (the default) disables logging of plans. For
      example, if you set it to <literal>250ms</literal> then all statements
      that run 250ms or longer will be logged. Only superusers can change this
      setting.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="auto-explain-configuration-parameters-log-parameter-max-length">
    <term>
     <varname>auto_explain.log_parameter_max_length</varname> (<type>integer</type>)
     <indexterm>
      <primary><varname>auto_explain.log_parameter_max_length</varname> configuration parameter</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      <varname>auto_explain.log_parameter_max_length</varname> controls the
      logging of query parameter values. A value of <literal>-1</literal> (the
      default) logs the parameter values in full. <literal>0</literal> disables
      logging of parameter values. A value greater than zero truncates each
      parameter value to that many bytes. Only superusers can change this
      setting.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="auto-explain-configuration-parameters-log-analyze">
    <term>
     <varname>auto_explain.log_analyze</varname> (<type>boolean</type>)
     <indexterm>
      <primary><varname>auto_explain.log_analyze</varname> configuration parameter</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      <varname>auto_explain.log_analyze</varname> causes <command>EXPLAIN ANALYZE</command>
      output, rather than just <command>EXPLAIN</command> output, to be printed
      when an execution plan is logged. This parameter is off by default.
      Only superusers can change this setting.
     </para>
     <note>
      <para>
       When this parameter is on, per-plan-node timing occurs for all
       statements executed,