auto_explain The auto_explain module provides a means for logging execution plans that execution times are longer than configuration. You can LOAD this module dynamically or preload it automatically with shared_preload_libraries or local_preload_libraries. The definitions of the columns exposed by the view are: There is one row for each statement. Statements are grouped when they have same SQL text, are in the same database, and are executed by the same user. Because of security restriction, non-super users cannot see query strings executed by other users. explain.log_min_duration (integer) explain.log_min_duration is the minimum execution time in milliseconds which execution plan will be logged. Setting this to zero prints all plans. Minus-one (the default) disables logging plans. For example, if you set it to 250ms then all plan that run 250ms or longer in executor will be logged. Enabling this parameter can be helpful in tracking down unoptimized queries in your applications. Only superusers can change this setting. explain.log_analyze (boolean) explain.log_analyze enables to use EXPLAIN ANALYZE when an execution plan is logged by explain.log_min_duration. This parameter is off by default. Only superusers can change this setting. NOTE: If you set the parameter on, instrument timers are enabled even if you don't use EXPLAIN ANALYZE. This has some overhead to execute plans. explain.log_verbose (boolean) explain.log_verbose enables to use EXPLAIN VERBOSE when an execution plan is logged by explain.log_min_duration. This parameter is off by default. Only superusers can change this setting. If you set these explain.* parameters in your postgresql.conf, you also need to add 'explain' in custom_variable_classes. # postgresql.conf shared_preload_libraries = 'auto_explain' custom_variable_classes = 'explain' explain.log_min_duration = 3s postgres=# LOAD 'auto_explain'; postgres=# SET explain.log_min_duration = 0; postgres=# SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique; 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; Takahiro Itagaki itagaki.takahiro@oss.ntt.co.jp