auto_explain The auto_explain module provides a means for logging execution plans of slow statements automatically, without having to run by hand. This is especially helpful for tracking down un-optimized queries in large applications. The module provides no SQL-accessible functions. To use it, simply load it into the server. You can load it into an individual session: LOAD 'auto_explain'; (You must be superuser to do that.) More typical usage is to preload it into all sessions by including auto_explain in in postgresql.conf. Then you can track unexpectedly slow queries no matter when they happen. Of course there is a price in overhead for that. There are several configuration parameters that control the behavior of auto_explain. Note that the default behavior is to do nothing, so you must set at least auto_explain.log_min_duration if you want any results. auto_explain.log_min_duration (integer) auto_explain.log_min_duration configuration parameter auto_explain.log_min_duration 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 250ms then all statements that run 250ms or longer will be logged. Only superusers can change this setting. auto_explain.log_analyze (boolean) auto_explain.log_analyze configuration parameter auto_explain.log_analyze causes EXPLAIN ANALYZE output, rather than just EXPLAIN output, to be printed when an execution plan is logged. This parameter is off by default. Only superusers can change this setting. 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 extremely negative impact on performance. auto_explain.log_verbose (boolean) auto_explain.log_verbose configuration parameter auto_explain.log_verbose causes EXPLAIN VERBOSE output, rather than just EXPLAIN output, to be printed when an execution plan is logged. This parameter is off by default. Only superusers can change this setting. auto_explain.log_buffers (boolean) auto_explain.log_buffers configuration parameter auto_explain.log_buffers causes EXPLAIN (ANALYZE, BUFFERS) output, rather than just EXPLAIN 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 auto_explain.log_analyze parameter is set. auto_explain.log_format (enum) auto_explain.log_format configuration parameter auto_explain.log_format selects the EXPLAIN output format to be used. The allowed values are text, xml, json, and yaml. The default is text. Only superusers can change this setting. auto_explain.log_nested_statements (boolean) auto_explain.log_nested_statements configuration parameter auto_explain.log_nested_statements 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. In order to set these parameters in your postgresql.conf file, you will need to add auto_explain to . Typical usage might be: # postgresql.conf shared_preload_libraries = 'auto_explain' custom_variable_classes = 'auto_explain' auto_explain.log_min_duration = '3s' 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; This might produce log output such as: 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; ]]> Takahiro Itagaki itagaki.takahiro@oss.ntt.co.jp