A contrib module which automatically logs execution plans of slow statements

auto_explain is a contrib module which enables PostgreSQL to automatically log the execution plans of slow queries.

auto_explain was added in PostgreSQL 8.4.

Change history


Typically, auto_explain is included in postgresql.conf's shared_preload_libraries option to track slow queries across all database sessions. It can also be loaded into an individual session by a superuser with LOAD 'auto_explain'. Note that it is not possible to enable auto_explain for individual databases.

Use of auto_explain does entail some additional overhead and may impact overall database performance. Consequently it should only be enabled when required.

By default, auto_explain is not active until explicitly configured. At the very least, auto_explain.log_min_duration must be set with a value of zero ("log all plans") or greater (minimum duration in milliseconds of statements to log). See the auto_explain documentation for details of all configuration options. Configuration options can only be set by superusers.


Enabling auto_explain in a single session:

postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SELECT count(*)
             FROM pgbench_branches b
             JOIN pgbench_accounts a ON =;
(1 row)

The server log will contain output similar to the following:

LOG:  duration: 26.516 ms  plan:
        Query Text: SELECT count(*)
                      FROM pgbench_branches b
                      JOIN pgbench_accounts a ON =;
        Aggregate  (cost=4141.01..4141.02 rows=1 width=0)
          ->  Nested Loop  (cost=0.00..3891.01 rows=100000 width=0)
                Join Filter: ( =
                ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=4)
                ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=4)


Contrib module, Performance