auto_explain

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 (release notes; commit e125e28e).

Change history

Configuration

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.

Examples

Enabling auto_explain in a single session:

test=# LOAD 'auto_explain';
LOAD
test=# SET auto_explain.log_min_duration = 0;
SET
test=#  SELECT count(*)
          FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid;
 count  
--------
 100000
(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 b.bid = a.bid;
        Aggregate  (cost=4141.01..4141.02 rows=1 width=0)
          ->  Nested Loop  (cost=0.00..3891.01 rows=100000 width=0)
                Join Filter: (b.bid = a.bid)
                ->  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)

Categories

Contrib module, Performance