Capturing Plans for Long-Running Queries #

A report has been running for forty minutes. You cannot run EXPLAIN ANALYZE on it — that would start a second forty-minute execution — and by the time it finishes, whoever ran it will have cancelled it. Meanwhile last night’s batch job was slow once and has been fine since; no one captured a plan, and nobody can reproduce the conditions.

Capturing plans deliberately is covered in EXPLAIN options and output formats. This page is about the cases where you cannot simply run the query.

The Condition #

Four sources cover the cases where EXPLAIN ANALYZE is not an option:

What none of them provides is the plan of a query currently executing in another backend. Some extensions add that capability; in core PostgreSQL, the practical substitutes are auto_explain for after the fact and EXPLAIN for the equivalent plan now.

Which source fits the situation? Three cases. For a query running right now, inspect pg_stat_activity for its state and wait event, and run EXPLAIN without ANALYZE for the equivalent plan. For a query that already ran, read the auto_explain log. For maintenance operations, read the matching progress view. when do you need the plan? running right now pg_stat_activity + EXPLAIN no ANALYZE already finished auto_explain log actual rows and times a maintenance command pg_stat_progress_* phase and completion core PostgreSQL cannot dump another backend's running plan

Annotated Evidence #

What is running, and what it is doing:

SELECT pid, now() - query_start AS runtime, state, wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '1 minute'
ORDER BY query_start;
  pid  |   runtime   | state  | wait_event_type | wait_event |                query
-------+-------------+--------+-----------------+------------+-------------------------------
 41204 | 00:41:12    | active | IO              | DataFileRead | SELECT customer_id, sum(total) …
-- running 41 minutes, currently waiting on storage reads, not on a lock

The plan it would get now:

EXPLAIN (VERBOSE, SETTINGS)
SELECT customer_id, sum(total) FROM orders WHERE placed_at >= '2026-01-01' GROUP BY customer_id;
Finalize GroupAggregate  (cost=18402104.42..19204112.10 rows=3812044 width=40)
  ->  Gather Merge  (cost=…)
        Workers Planned: 4
        ->  Sort  (cost=…)
              Sort Key: customer_id
              ->  Partial HashAggregate  (cost=…)
                    ->  Parallel Seq Scan on orders  (cost=…)
Settings: work_mem = '64MB', effective_cache_size = '48GB'
-- costs only; no execution. Enough to see the shape and spot the sort

And what actually ran last night, from auto_explain:

LOG:  duration: 2481204.118 ms  plan:
  Query Text: SELECT customer_id, sum(total) FROM orders WHERE placed_at >= $1 GROUP BY customer_id
  Finalize GroupAggregate  (cost=… rows=3812044) (actual rows=18402110 loops=1)
    ->  Gather Merge  (actual rows=41204 loops=1)
          Workers Planned: 4  Workers Launched: 0
          ->  Sort  (actual rows=8240 loops=1)
                Sort Method: external merge  Disk: 4102208kB
-- Workers Launched: 0 explains the outlier: the pool was exhausted that night
What each source tells you Three items are annotated. pg_stat_activity gives runtime and the current wait event but no plan. EXPLAIN without ANALYZE gives the plan shape and estimates with no execution. An auto_explain log entry gives the plan that actually ran, with actual rows, timings and worker counts. wait_event: DataFileRead after 41 min waiting on storage, not locks EXPLAIN (VERBOSE, SETTINGS), no ANALYZE shape and estimates, no execution auto_explain: Workers Launched: 0 what really happened that night only auto_explain records what actually ran

Step-by-Step Resolution #

  1. Set up auto_explain before you need it. A duration threshold of a few seconds captures the outliers without flooding the log:

    ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '3s';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_settings = on;
ALTER SYSTEM SET auto_explain.log_format = 'json';
SELECT pg_reload_conf();

    log_analyze adds instrumentation overhead to every statement that qualifies; on a workload with slow clock reads, add auto_explain.log_timing = off to keep row counts without per-node timing.

  2. For a query running now, read pg_stat_activity for its runtime and wait event, which tells you whether it is working, waiting on I/O, or blocked on a lock — the distinction in lock waits that look like slow plans.

  3. Run EXPLAIN without ANALYZE on the same text to see the plan shape it would get now. Bind the same parameter types, and use GENERIC_PLAN when the application uses prepared statements.

  4. Check the progress views when the statement is a maintenance command; they give completion percentages no plan can.

  5. Decide whether to cancel. A query waiting on a lock will not finish until the blocker does; one reading steadily may be close to done — the progress views and Buffers growth over samples of pg_stat_activity help estimate.

  6. Keep the captured plans. An auto_explain entry for a rare outlier is often the only evidence that will ever exist.

What is available, and when A timeline across a query's life. Before it runs, auto_explain must already be configured. While it runs, pg_stat_activity shows state and wait events and progress views show maintenance phases. After it finishes, the auto_explain log holds the plan that ran. Later, EXPLAIN without ANALYZE gives the plan it would get now. before configure auto_explain while running pg_stat_activity, progress views on completion auto_explain logs the plan later EXPLAIN without ANALYZE the only irreplaceable step is the first one

Before and After #

-- BEFORE: no capture configured; the outlier is unexplained
pg_stat_activity: 41 minutes, wait_event DataFileRead — and nothing after it finishes

-- AFTER: auto_explain with log_analyze
LOG: duration: 2481204 ms  plan: … Workers Launched: 0 … Sort Method: external merge  Disk: 4102208kB

Estimating progress without a progress view #

Ordinary queries have no progress view, but two rough signals help decide whether to wait or cancel. Sampling pg_stat_activity does not show buffer counts, but pg_stat_statements accumulates per-statement totals as executions complete, so it cannot help for one in flight either. What does work is sampling the backend’s I/O from the operating system, or — more practically — reasoning from the plan: an EXPLAIN of the same statement gives the estimated row counts, and comparing them with what the query is expected to return tells you the order of magnitude of work remaining.

For maintenance commands the progress views are exact, and they are worth knowing precisely because those are the operations most likely to be waited on: pg_stat_progress_create_index reports the phase and blocks done, pg_stat_progress_vacuum the phase and heap blocks scanned, and pg_stat_progress_copy the tuples processed during a bulk load.

Where investigating in-flight queries is routine, the most valuable investment is not a tool but a habit: auto_explain configured in every environment before anything goes wrong, with a threshold low enough to catch the queries you would want to see and high enough not to flood the log.

Common Pitfalls #

Configuring auto_explain after the incident. The evidence is gone. Diagnostic signal: “we will add logging next time” repeated. Fix: enable it permanently with a sensible threshold.

log_min_duration = 0 in production. Every statement’s plan is logged. Diagnostic signal: log volume explosion. Fix: a threshold of seconds.

Assuming EXPLAIN shows the running plan. It shows what the planner would choose now. Diagnostic signal: a mismatch with auto_explain output. Fix: treat it as an approximation, and check GENERIC_PLAN for prepared statements.

Cancelling without checking the wait event. A blocked query will not progress; a working one might be nearly done. Diagnostic signal: repeated cancellations of the same report. Fix: read wait_event_type first.

Frequently Asked Questions #

Can I see the execution plan of a query that is currently running? #

Not from core PostgreSQL. You can see what it is doing through pg_stat_activity and progress views, and you can run EXPLAIN without ANALYZE on the same statement to see the plan the planner would choose now.

How do I capture plans for slow queries in production? #

Configure auto_explain with a duration threshold and log_analyze enabled. It logs the plan, with actual rows and timings, for every statement exceeding the threshold — including ones you did not anticipate.

Is EXPLAIN without ANALYZE safe on an UPDATE? #

Yes. Without ANALYZE the statement is planned but not executed, so no rows change. Only ANALYZE executes the statement.

Up: EXPLAIN Options and Output Formats