<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >auto_explain</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.4.12 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Additional Supplied Modules" HREF="contrib.html"><LINK REL="PREVIOUS" TITLE="adminpack" HREF="adminpack.html"><LINK REL="NEXT" TITLE="btree_gin" HREF="btree-gin.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2012-05-31T23:30:11"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.4.12 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="adminpack.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="contrib.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Appendix F. Additional Supplied Modules</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="contrib.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="btree-gin.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="AUTO-EXPLAIN" >F.2. auto_explain</A ></H1 ><A NAME="AEN114320" ></A ><P > The <TT CLASS="FILENAME" >auto_explain</TT > module provides a means for logging execution plans of slow statements automatically, without having to run <A HREF="sql-explain.html" ><I >EXPLAIN</I ></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 all sessions by including <TT CLASS="LITERAL" >auto_explain</TT > in <A HREF="runtime-config-resource.html#GUC-SHARED-PRELOAD-LIBRARIES" >shared_preload_libraries</A > in <TT CLASS="FILENAME" >postgresql.conf</TT >. 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" ><H2 CLASS="SECT2" ><A NAME="AEN114330" >F.2.1. Configuration parameters</A ></H2 ><P > There are several configuration parameters that control the behavior of <TT CLASS="FILENAME" >auto_explain</TT >. Note that the default behavior is to do nothing, so you must set at least <TT CLASS="VARNAME" >auto_explain.log_min_duration</TT > if you want any results. </P ><P ></P ><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="VARNAME" >auto_explain.log_min_duration</TT > (<TT CLASS="TYPE" >integer</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_min_duration</TT > is the minimum statement execution time, in milliseconds, that will cause the statement's plan to be logged. Setting this to zero logs all plans. Minus-one (the default) disables logging of plans. For example, if you set it to <TT CLASS="LITERAL" >250ms</TT > then all statements that run 250ms or longer will be logged. Only superusers can change this setting. </P ></DD ><DT ><TT CLASS="VARNAME" >auto_explain.log_analyze</TT > (<TT CLASS="TYPE" >boolean</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_analyze</TT > causes <TT CLASS="COMMAND" >EXPLAIN ANALYZE</TT > output, rather than just <TT CLASS="COMMAND" >EXPLAIN</TT > 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" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > 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. </P ></BLOCKQUOTE ></DIV ></DD ><DT ><TT CLASS="VARNAME" >auto_explain.log_verbose</TT > (<TT CLASS="TYPE" >boolean</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_verbose</TT > causes <TT CLASS="COMMAND" >EXPLAIN VERBOSE</TT > output, rather than just <TT CLASS="COMMAND" >EXPLAIN</TT > output, to be printed when an execution plan is logged. This parameter is off by default. Only superusers can change this setting. </P ></DD ><DT ><TT CLASS="VARNAME" >auto_explain.log_nested_statements</TT > (<TT CLASS="TYPE" >boolean</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_nested_statements</TT > 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 ></DL ></DIV ><P > In order to set these parameters in your <TT CLASS="FILENAME" >postgresql.conf</TT > file, you will need to add <TT CLASS="LITERAL" >auto_explain</TT > to <A HREF="runtime-config-custom.html#GUC-CUSTOM-VARIABLE-CLASSES" >custom_variable_classes</A >. Typical usage might be: </P ><PRE CLASS="PROGRAMLISTING" ># postgresql.conf shared_preload_libraries = 'auto_explain' custom_variable_classes = 'auto_explain' auto_explain.log_min_duration = '3s' </PRE ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN114388" >F.2.2. Example</A ></H2 ><PRE CLASS="PROGRAMLISTING" > postgres=# LOAD 'auto_explain'; postgres=# SET auto_explain.log_min_duration = 0; 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="PROGRAMLISTING" > LOG: duration: 0.986 ms plan: Aggregate (cost=14.90..14.91 rows=1 width=0) -> Hash Join (cost=3.91..14.70 rows=81 width=0) Hash Cond: (pg_class.oid = pg_index.indrelid) -> Seq Scan on pg_class (cost=0.00..8.27 rows=227 width=4) -> Hash (cost=2.90..2.90 rows=81 width=4) -> Seq Scan on pg_index (cost=0.00..2.90 rows=81 width=4) Filter: indisunique STATEMENT: SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique; </PRE ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN114393" >F.2.3. Author</A ></H2 ><P > Takahiro Itagaki <CODE CLASS="EMAIL" ><<A HREF="mailto:itagaki.takahiro@oss.ntt.co.jp" >itagaki.takahiro@oss.ntt.co.jp</A >></CODE > </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="adminpack.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="btree-gin.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >adminpack</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="contrib.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >btree_gin</TD ></TR ></TABLE ></DIV ></BODY ></HTML >