<!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 9.0.22 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="2015-06-13T20:14:15"></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" ><A HREF="index.html" >PostgreSQL 9.0.22 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="adminpack" HREF="adminpack.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="contrib.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Appendix F. Additional Supplied Modules</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="btree_gin" 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 ><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" >EXPLAIN</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="AEN125055" >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_buffers</TT > (<TT CLASS="TYPE" >boolean</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_buffers</TT > causes <TT CLASS="COMMAND" >EXPLAIN (ANALYZE, BUFFERS)</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. This parameter has no effect unless <TT CLASS="VARNAME" >auto_explain.log_analyze</TT > parameter is set. </P ></DD ><DT ><TT CLASS="VARNAME" >auto_explain.log_format</TT > (<TT CLASS="TYPE" >enum</TT >)</DT ><DD ><P > <TT CLASS="VARNAME" >auto_explain.log_format</TT > selects the <TT CLASS="COMMAND" >EXPLAIN</TT > output format to be used. The allowed values are <TT CLASS="LITERAL" >text</TT >, <TT CLASS="LITERAL" >xml</TT >, <TT CLASS="LITERAL" >json</TT >, and <TT CLASS="LITERAL" >yaml</TT >. The default is text. 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="AEN125141" >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="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) -> 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) -> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1) -> 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 -> 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" ><H2 CLASS="SECT2" ><A NAME="AEN125146" >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 >