EXPLAIN VERBOSE Output Columns and Workers #

A sort spills 4 GB while sorting what should be two columns. A parallel scan with four workers takes as long as a serial one. Neither problem is visible in a plain EXPLAIN ANALYZE: the first needs the node’s output column list, the second needs per-worker statistics. Both come from one option.

The full option set is described in EXPLAIN options and output formats. This page covers what VERBOSE adds and what it lets you diagnose.

The Condition #

VERBOSE adds four kinds of detail:

None of it changes the plan; it only reports more about the same plan. The cost is verbosity: on a large plan the Output: lines can double the text, which is why it is not the default.

Four additions, four diagnoses A table. Output column lists expose unnecessarily wide rows in sorts and joins. Schema-qualified names expose the wrong object being resolved. Per-worker statistics expose uneven work distribution. Inner Unique exposes whether a uniqueness proof was available. what it shows what it diagnoses Output: per node columns carried wide sorts and hashes schema-qualified names which object search_path surprises per-worker rows and time each worker skew between workers Inner Unique: true uniqueness proof missing unique index none of it changes the plan, only what is reported

Annotated EXPLAIN Evidence #

Width, exposed by the output list:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT customer_id, placed_at FROM v_order_details ORDER BY placed_at DESC LIMIT 100;
Limit  (actual time=8402.1..8402.2 rows=100 loops=1)
  Output: v_order_details.customer_id, v_order_details.placed_at
  ->  Sort  (actual time=8402.1..8402.2 rows=100 loops=1)
        Output: orders.customer_id, orders.placed_at, orders.id, orders.total, orders.notes,
                orders.metadata, customers.email, customers.address, …
        Sort Key: orders.placed_at DESC
        Sort Method: external merge  Disk: 4102208kB
-- the query asked for two columns; the sort carries fifteen, including two wide ones
-- the view selects * and the planner cannot drop columns the view's definition projects

Worker skew, exposed by per-worker statistics:

  ->  Gather  (actual time=0.9..21402.6 rows=41204110 loops=1)
        Workers Planned: 4  Workers Launched: 4
        ->  Parallel Seq Scan on events  (actual time=0.03..18204.2 rows=8240822 loops=5)
              Worker 0:  actual time=0.03..2104.1 rows=1204110 loops=1
              Worker 1:  actual time=0.03..2210.4 rows=1240118 loops=1
              Worker 2:  actual time=0.02..2180.6 rows=1198402 loops=1
              Worker 3:  actual time=0.03..18204.2 rows=36402110 loops=1
-- worker 3 processed 36 million rows while the others processed about 1.2 million each
-- typical cause: one very large partition or a skewed parallel-aware scan boundary

And a uniqueness proof:

Hash Join  (actual rows=398204 loops=1)
  Output: l.order_id, p.name
  Inner Unique: true
  Hash Cond: (l.sku = p.sku)
-- the planner knows each line matches at most one product, so probes stop at the first match
Three lines only VERBOSE shows Three items are annotated. An Output list far wider than the query's select list reveals unnecessary columns being carried. Per-worker lines with very different row counts reveal skew. An Inner Unique line reveals that a uniqueness proof was available to the join. Output: … 15 columns for a 2-column query wide rows through the sort Worker 3: rows=36402110 vs ~1.2M work distributed unevenly Inner Unique: true uniqueness proof in use plain EXPLAIN shows worker averages, hiding the skew

Step-by-Step Resolution #

  1. Capture with VERBOSE whenever a sort or hash uses unexpected memory. Compare the node’s Output: list with what the query actually needs.

  2. Trace wide columns to their source — usually a view with SELECT *, an ORM mapping every column, or a subquery projecting more than the outer query consumes.

  3. Narrow the projection at the source. A view that selects explicit columns lets the planner carry fewer, and often unlocks index-only scans.

  4. For parallel plans, read the per-worker lines. Roughly equal rows and times mean the work divided well; one worker doing most of the work means skew.

  5. Diagnose skew by its shape: a partitioned table where one partition dominates, an Append whose children are assigned per worker, or a filter whose selectivity varies across the table’s physical order.

  6. Check Inner Unique on joins to lookup tables; its absence points at a missing unique constraint, as covered in unique indexes and planner uniqueness proofs.

  7. Keep VERBOSE out of routine captures unless you need it; the extra text makes plans harder to read at a glance.

What the extra columns cost Sorting fifteen columns including two wide ones spills 4,102 megabytes. Sorting the two requested columns uses 84 megabytes in memory. Sorting ids only and joining back uses 31 megabytes. 15 columns (view SELECT *) 4,102 MB spilled 2 requested columns 84 MB in memory ids only, join back 31 MB the plan shape is identical in all three

Before and After #

-- BEFORE: view projects every column; sort carries them all
Sort  Output: 15 columns  Sort Method: external merge  Disk: 4102208kB     Execution Time: 8404.2 ms

-- AFTER: view projects the columns consumers use
Sort  Output: 2 columns  Sort Method: top-N heapsort  Memory: 84kB          Execution Time: 412.6 ms

Reading per-worker numbers correctly #

Without VERBOSE, a parallel node’s rows and actual time are averages across participants, and loops equals the number of participants. That presentation hides two different situations that look identical: four workers each doing a quarter of the work, and one worker doing nearly all of it. Only the per-worker lines distinguish them.

Skew matters because a parallel plan finishes when its slowest participant finishes. Four workers with one doing 90% of the rows perform like a serial plan plus coordination overhead. The fixes depend on the cause: for skewed partitions, rebalancing the partition scheme; for a parallel-aware scan over a table whose matching rows cluster physically, sometimes nothing — the work genuinely is where it is — in which case the honest conclusion is that parallelism will not help that query.

The same per-worker detail shows memory: each worker’s sort or hash reports its own figures, which is how you discover that one worker spilled while the others did not.

Common Pitfalls #

Diagnosing width without VERBOSE. The column list is invisible otherwise. Diagnostic signal: sort memory far above what the query seems to need. Fix: capture with VERBOSE.

Reading averages as totals. Parallel nodes report per-participant averages. Diagnostic signal: row counts that look too small. Fix: multiply by loops, or read per-worker lines.

Views with SELECT *. Every consumer carries every column. Diagnostic signal: wide Output: lists tracing to a view. Fix: explicit projections.

Ignoring skew. A parallel plan is as slow as its slowest worker. Diagnostic signal: one worker with most of the rows. Fix: address the distribution, or accept a serial plan.

Frequently Asked Questions #

What does EXPLAIN VERBOSE add? #

Per-node output column lists, schema-qualified object names, per-worker statistics for parallel nodes when combined with ANALYZE, and annotations such as Inner Unique. It reports more about the same plan; it does not change it.

How do I see what each parallel worker did? #

Use EXPLAIN with ANALYZE and VERBOSE. Each parallel node then lists a line per worker with that worker’s own actual time, rows and buffers, instead of only the averages.

Why does my sort use more memory than the selected columns suggest? #

The sort carries every column the node outputs, which can be far more than the query’s select list when a view or subquery projects extra columns. VERBOSE’s Output line shows exactly which columns are being carried.

Up: EXPLAIN Options and Output Formats