(opens in a new tab)

auto_explain: Log Slow Query Plans in Postgres

September 25, 2026 by Chat2DB Team

Running EXPLAIN ANALYZE by hand is the standard way to understand a slow query, but it has a blind spot: by the time you run it, the conditions that made the query slow may be gone. The data distribution changed, the cache was cold, a different parameter value produced a different plan, or the query came from a nightly job nobody was watching. The auto_explain module closes that gap. It hooks into the executor and writes the execution plan of any statement that runs longer than a threshold directly into the PostgreSQL server log, exactly as it was executed.

This guide explains how to load auto_explain, what every important setting does, how much overhead to expect and how to control it, how to read the logged output, and how to combine it with pg_stat_statements. It closes with notes on managed services such as Amazon RDS.

What auto_explain does

auto_explain is a contrib module shipped with PostgreSQL. It has no SQL objects, so there is no CREATE EXTENSION step. Once the shared library is loaded into a backend, it:

1. Checks each statement as it starts to decide whether it might need to be logged (sampling and instrumentation).
2. Lets the statement run normally.
3. When the statement finishes, compares its duration with auto_explain.log_min_duration.
4. If the duration is at or above the threshold, writes the plan to the server log, optionally with actual row counts, timings, and buffer usage.

The key point is step 3: the plan is logged at the end of execution. A statement that is cancelled, hits statement_timeout, or fails with an error does not produce an auto_explain entry. For queries that never finish, you still need other tools, such as pg_stat_activity and a manual EXPLAIN without ANALYZE.

Three ways to load auto_explain

Option 1: LOAD in a single session

The quickest way to experiment is to load the module into your own session. This requires superuser privileges:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- log every statement
SET auto_explain.log_analyze = on;
SET client_min_messages = log;          -- also show log output in this session
 
SELECT count(*) FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid;

Setting client_min_messages to log is a handy trick for learning: the plan is sent back to your client as a message in addition to the server log, so you can see the output immediately. Only your session is affected, and the module disappears when you disconnect.

Option 2: session_preload_libraries

To enable auto_explain for specific roles or databases without a restart, use session_preload_libraries. The library is loaded at the start of each new session that matches:

ALTER ROLE reporting_app SET session_preload_libraries = 'auto_explain';
ALTER ROLE reporting_app SET auto_explain.log_min_duration = '500ms';

Or for a whole database:

ALTER DATABASE shop SET session_preload_libraries = 'auto_explain';
ALTER DATABASE shop SET auto_explain.log_min_duration = '1s';

Existing connections are not affected; only new sessions pick up the change. If you use a connection pooler, you must recycle the pooled server connections before the setting takes effect. Changing session_preload_libraries and the auto_explain parameters requires superuser privileges.

This option is ideal for targeted investigations: enable it for the one application role that is misbehaving, collect plans for a day, and remove it with ALTER ROLE reporting_app RESET session_preload_libraries.

Option 3: shared_preload_libraries

For permanent, server-wide use, add the module to shared_preload_libraries in postgresql.conf. This requires a server restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
 
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off
auto_explain.log_nested_statements = on
auto_explain.log_format = 'text'
auto_explain.sample_rate = 1.0

Keep any existing entries in shared_preload_libraries; the value is a comma-separated list, and overwriting it could remove modules such as pg_stat_statements. After the restart, confirm the module is loaded:

SHOW shared_preload_libraries;
SHOW auto_explain.log_min_duration;

Once the library is loaded server-wide, the auto_explain.* parameters can be changed with a configuration reload (SELECT pg_reload_conf();) and adjusted per role or database with ALTER ROLE ... SET, without further restarts.

Key settings explained

auto_explain.log_min_duration

The threshold that decides which statements are logged. It accepts a duration such as '250ms' or '2s'; a bare number is interpreted as milliseconds. 0 logs every statement, and -1 (the default) disables logging. Start high, for example a few seconds, and lower it once you see how much volume it generates. A threshold of 0 on a busy server can produce enormous logs.

auto_explain.log_analyze

When off (the default), the log contains the plan as EXPLAIN would print it: estimated costs and rows only. When on, it contains EXPLAIN ANALYZE output: actual rows, loops, and, depending on log_timing, actual times per node. The actual numbers are what make the log useful for diagnosing misestimates, so most people turn this on, but it is also the main source of overhead (see the overhead section).

auto_explain.log_buffers

Adds buffer usage (shared hits, reads, dirtied, written, and temp I/O) to each node, equivalent to EXPLAIN (ANALYZE, BUFFERS). It only has an effect when log_analyze is on. Buffer counts show whether a slow query was reading from disk or from cache, which often explains why the same query is fast in testing and slow in production.

auto_explain.log_timing

Controls per-node timing when log_analyze is on. It defaults to on. Turning it off keeps actual row counts and loops but skips reading the clock for every row passing through every node, which is the expensive part of instrumentation on some systems. The total statement duration is still reported. A common production setup is log_analyze = on with log_timing = off: you still see where row estimates are wrong, at a fraction of the cost.

You can check how expensive clock reads are on your hardware with the pg_test_timing utility that ships with PostgreSQL.

auto_explain.log_nested_statements

By default only top-level statements are considered. Statements executed inside functions, procedures, and DO blocks, such as queries in a PL/pgSQL function, are invisible. Set this to on to evaluate and log nested statements individually. This is essential when your slow path is inside a stored function: without it you would only see a single "Function Scan" or a SELECT my_function() with no detail.

auto_explain.log_format

The output format: text (default), json, yaml, or xml. Text is the easiest for humans to read in a log file. JSON is better if you ship logs to a pipeline that parses them, or if you paste plans into a visualizer that accepts JSON. Note that multi-line plans in text format are written as one log entry; using a structured log destination (log_destination = 'jsonlog', available since PostgreSQL 15, or csvlog) keeps each plan together as a single record for log shippers.

auto_explain.sample_rate

A fraction between 0 and 1 of statements to consider in each session. The default is 1, meaning every statement. Setting it to 0.1 means roughly one statement in ten is instrumented and eligible for logging. When log_nested_statements is on, the sampling decision is made for the top-level statement and applies to its nested statements too, so you get complete traces rather than fragments.

auto_explain.log_parameter_max_length

Available since PostgreSQL 16. For statements with bind parameters, auto_explain can log the parameter values next to the query text. The default -1 logs values in full, 0 disables parameter logging, and a positive number truncates each value to that many bytes. Parameter values are often the missing piece when a query is only slow for certain inputs, but they may also contain personal data or secrets, so choose the setting deliberately.

Other useful settings

• auto_explain.log_verbose: equivalent to EXPLAIN VERBOSE, including output column lists and, when query identifiers are computed, the query identifier.
• auto_explain.log_settings: lists planner-related settings that differ from their defaults (PostgreSQL 12 and later), useful when a role or session has unusual work_mem or enable_* settings.
• auto_explain.log_wal: WAL usage per node (PostgreSQL 13 and later), requires log_analyze.
• auto_explain.log_triggers: includes trigger execution statistics, requires log_analyze.
• auto_explain.log_level: the log level used for the entries (default LOG).

Overhead considerations

It is tempting to enable everything with a zero threshold. That is fine on a development machine and risky in production. The costs come from three places.

Instrumentation. With log_analyze on, PostgreSQL must count rows and, with log_timing on, read the clock at every node for every row. Crucially, it cannot know in advance whether a statement will exceed the threshold, so it instruments every sampled statement, including the fast majority that will never be logged. Queries that process many rows through many nodes pay the most. The cost depends heavily on the workload and the platform's clock source, so measure it on your own system rather than relying on a generic figure.

Log volume. Every logged plan is written synchronously to the log. A low threshold on a busy system can produce gigabytes of logs per day, filling disks and slowing the log collector.

Sensitive data. Query text and parameters in logs may contain personal data. Treat plan logs with the same care as the data itself.

A reasonable rollout sequence:

1. Enable with log_analyze = off and a high threshold. This has very low overhead because no instrumentation is added; you only get estimated plans for slow queries.
2. Turn on log_analyze with log_timing = off and log_buffers = on. Watch CPU and latency for a while.
3. If you need per-node timing, enable log_timing together with a sample_rate below 1 to limit the number of instrumented statements.
4. Lower the threshold gradually while monitoring log volume.

Reading the logged plans

A logged entry in text format looks like this (the numbers here are illustrative):

LOG:  duration: 3120.418 ms  plan:
	Query Text: SELECT o.id, c.email
	            FROM orders o JOIN customers c ON c.id = o.customer_id
	            WHERE o.status = $1 AND o.created_at >= $2
	Query Parameters: $1 = 'pending', $2 = '2026-09-01 00:00:00+00'
	Hash Join  (cost=1280.44..58211.30 rows=410 width=40) (actual rows=182344 loops=1)
	  Hash Cond: (o.customer_id = c.id)
	  Buffers: shared hit=2210 read=51877
	  ->  Seq Scan on orders o  (cost=0.00..56310.00 rows=410 width=16) (actual rows=182344 loops=1)
	        Filter: ((status = 'pending'::text) AND (created_at >= '2026-09-01 00:00:00+00'::timestamptz))
	        Rows Removed by Filter: 1817656
	        Buffers: shared hit=1480 read=51877
	  ->  Hash  (cost=780.00..780.00 rows=40000 width=32) (actual rows=40000 loops=1)
	        Buckets: 65536  Batches: 1  Memory Usage: 2815kB
	        Buffers: shared hit=730
	        ->  Seq Scan on customers c  (cost=0.00..780.00 rows=40000 width=32) (actual rows=40000 loops=1)
	              Buffers: shared hit=730

This entry was produced with log_analyze = on, log_buffers = on, and log_timing = off, which is why nodes show actual rows but no actual times. Read it in this order:

1. Header. The duration line tells you the total execution time. The query text and, on PostgreSQL 16 and later, the parameter values tell you exactly what ran.
2. Estimates versus actuals. The planner expected 410 rows from orders but got 182,344. A misestimate of that size is the most common root cause of bad plans; here it probably made the planner accept a sequential scan and a hash join sized for a tiny input. Check statistics (ANALYZE orders), consider extended statistics for correlated columns, or look for a missing index on (status, created_at).
3. Rows Removed by Filter. Nearly two million rows were read and discarded. That is the signature of a missing or unusable index.
4. Buffers. read=51877 means those pages came from outside PostgreSQL's shared buffers, from the OS cache or disk. A query that is fast when the data is cached and slow when it is not will show this pattern.
5. Loops. For nodes under a nested loop, multiply per-loop values by loops to get totals.

The details of each node type and the meaning of every field are covered in the guide to reading EXPLAIN ANALYZE query plans. The same reading skills apply, because auto_explain output is ordinary EXPLAIN output.

For long plans, paste them into a plan visualizer or a SQL client that renders plans graphically. Before changing anything, reproduce the plan manually with EXPLAIN (ANALYZE, BUFFERS) and the same parameter values, then test your fix the same way. Chat2DB (opens in a new tab) is convenient for that loop: paste the logged query, fill in the parameters, run the explain, and compare plans before and after adding an index.

Pairing auto_explain with pg_stat_statements

auto_explain and pg_stat_statements answer different questions, and they work best together:

• pg_stat_statements tells you which query shapes consume the most total time, how often they run, and their mean and maximum duration. It aggregates, so it cannot show a plan.
• auto_explain shows individual executions and their plans, but only those above the threshold, with no aggregation.

A typical workflow:

1. Find the top statements by total time:

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       round(max_exec_time::numeric, 1)   AS max_ms,
       left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

2. If a statement has a low mean but a high maximum, it is sometimes slow, a classic case for auto_explain. Set the threshold between its mean and its maximum so you capture the slow executions.
3. Match log entries to pg_stat_statements rows. Since PostgreSQL 14, with compute_query_id enabled (it is enabled automatically when pg_stat_statements is loaded, under the default auto setting), you can add %Q to log_line_prefix to print the query identifier on each log line, and auto_explain.log_verbose = on includes the identifier in the plan output. That gives you an exact join key between the log and the view.

log_line_prefix = '%m [%p] %u@%d qid=%Q '

For more on configuring and querying the statistics view, see the pg_stat_statements guide.

Managed services

Managed PostgreSQL services support auto_explain in general, but you configure it through the provider's settings interface rather than by editing postgresql.conf or running LOAD as a superuser, since you usually do not have a true superuser account.

The general pattern, using Amazon RDS as the example:

1. Create or edit a custom DB parameter group (on Aurora, the relevant parameter group for the cluster or instance).
2. Add auto_explain to shared_preload_libraries, keeping existing entries such as pg_stat_statements.
3. Set auto_explain.log_min_duration, auto_explain.log_analyze, and the other parameters in the same group.
4. Apply the group and reboot the instance, because shared_preload_libraries only changes at startup.
5. Read the plans in the database log files through the console or API, or export them to the provider's log service for searching and retention.

Other providers follow a similar idea through server parameters or database flags. Check the provider's documentation for which auto_explain parameters are exposed, whether a restart is needed, and how long logs are retained. Also check log storage limits: a low threshold can fill the provider's log volume quickly and may add log ingestion costs.

Troubleshooting

• Nothing is logged. Confirm the library is loaded (SHOW shared_preload_libraries; or session_preload_libraries), that auto_explain.log_min_duration is not -1, that the session was started after the change, and that sample_rate is not 0. Check log_min_messages: entries are written at LOG level by default.
• Function internals are missing. Enable auto_explain.log_nested_statements.
• Actual times are missing. log_timing is off, or log_analyze is off.
• Plans for a timed-out query never appear. That is expected; plans are logged only when execution completes.
• Setting a parameter fails with a permission error. The auto_explain.* parameters can only be changed by superusers, or through the managed service's parameter interface.

Summary

auto_explain records the real execution plans of slow statements as they happen in production, which is exactly the evidence you need when a problem cannot be reproduced by hand. Load it per session with LOAD, per role or database with session_preload_libraries, or server-wide with shared_preload_libraries. Choose a threshold with log_min_duration, add log_analyze and log_buffers for actual rows and I/O, keep log_timing off or sample with sample_rate to control overhead, enable log_nested_statements when logic lives in functions, and use log_parameter_max_length on PostgreSQL 16 and later to see the inputs that triggered a slow plan. Pair it with pg_stat_statements to decide what to look at, and read the resulting plans with the same skills you use for EXPLAIN ANALYZE.