Reading an EXPLAIN Plan

Once you reach a senior screen, interviewers stop asking write a query and start asking why is this query slow. The tool that answers that is EXPLAIN.

EXPLAIN shows the database's execution plan: the step-by-step strategy the planner chose to run your SQL. It reveals which tables are scanned, in what order they are joined, and roughly how expensive each step is.

Being able to read a plan signals that you understand the engine, not just the syntax. That is exactly the line interviewers use to separate mid-level from senior.

There are two flavors and interviewers love the distinction.

• EXPLAIN shows the planner's estimated plan without running the query. Fast and safe.
• EXPLAIN ANALYZE actually executes the query and reports the real row counts and timings alongside the estimates.

The gold mine is comparing estimated rows to actual rows. A big mismatch means the planner has bad statistics and is likely making a poor choice.

Caution: EXPLAIN ANALYZE runs the query for real, so it will perform any INSERT or UPDATE unless wrapped in a rolled-back transaction.

A plan is a tree, not a list. The most indented nodes are the leaves that run first; results flow upward to the root, which produces the final output.

Read it inside-out: find the deepest node, that is where execution begins. Each parent consumes the rows its children emit.

In an interview, narrate it that way: first we scan this table, those rows feed into this join, the join feeds into the sort, the sort feeds the limit. That bottom-up narration is what they want to hear.

Every node in a Postgres plan carries the same key numbers:

• cost=0.00..35.50 startup cost..total cost in arbitrary planner units
• rows=1000 estimated number of rows produced
• width=64 estimated average row size in bytes

The first cost is the startup cost (work before the first row appears, like building a hash table). The second is the total cost to return all rows. Higher total cost is the planner's guess at relative expense.

Consider a simple filtered query. The plan below tells a story in one line.

It is a Seq Scan (full table read) on orders, applying the filter status = 'shipped'. The planner estimates 1000 matching rows.

If orders has 10 million rows and only 1000 match, an interviewer expects you to say: a sequential scan here is wasteful, an index on status (or on a more selective column) would let us avoid reading the whole table.

With EXPLAIN ANALYZE you also get actual numbers in parentheses.

Look at the example: the planner estimated 1000 rows but actually got 480000. That is a 480x underestimate. The planner picked its strategy assuming few rows, so its choice is probably wrong for the real data.

In interviews, this gap is your headline diagnosis: the statistics are stale, run ANALYZE on the table, then the planner will likely pick a better plan.

The loops value matters more than candidates expect. It is the number of times a node was executed.

This appears on the inner side of a nested loop join: the inner node runs once per outer row. If loops=480000, that inner step executed 480 thousand times.

Important: the per-row time and row count shown are per loop. To get the true total you multiply by loops. A node that looks cheap at 0.004ms per loop becomes nearly 2 seconds across 480000 loops.

A frequent trap: candidates read cost=18334 and say that takes 18 seconds. Wrong.

Cost is in arbitrary planner units, calibrated so that one sequential page read equals 1.0. It is only meaningful for comparing plans against each other, not as a wall-clock figure.

For real timing you need EXPLAIN ANALYZE and its actual time values, measured in milliseconds. Say this clearly in an interview; it shows you actually understand the metric.

Here is a two-table plan. Read it bottom-up.

First two scans gather rows from orders and customers. They feed a Hash Join: one side is hashed, the other side probes the hash. The join's output then feeds the final result.

Notice the indentation shows the structure: both scans sit under the Hash Join. The interviewer wants you to identify the join method (hash here) and which table is being hashed (usually the smaller one).

Train your eye for these warning signs in any plan:

• Seq Scan on a huge table with a selective filter, an index could help.
• Estimated rows far from actual, stale statistics.
• Nested Loop with high loops over a big table, often a missing index on the inner join key.
• Sort or Hash spilling to disk (shown as Disk usage), work_mem is too small.
• Rows Removed by Filter very high, you read and discarded most of the table.

Plans come in several formats. The default TEXT is what you read aloud in interviews. But you can also request structured output.

EXPLAIN (FORMAT JSON) or FORMAT YAML produces machine-readable plans that tools and dashboards parse. You rarely need them by hand, but knowing they exist is a nice senior touch.

Add options in parentheses: EXPLAIN (ANALYZE, BUFFERS). The BUFFERS option reports cache hits versus disk reads, which is gold for diagnosing I/O-bound queries.

An interviewer shows you an EXPLAIN ANALYZE node with rows=1000 in the cost section but actual ... rows=480000. What is the most likely diagnosis?

You can now read a plan like a senior:

• EXPLAIN estimates, EXPLAIN ANALYZE runs and measures.
• Read the tree bottom-up; leaves run first, root produces output.
• Each node shows cost (relative units), rows, and width; actual time is the real millisecond figure.
• loops multiplies per-loop numbers, watch nested loops.
• The estimated-vs-actual row gap is your top diagnostic signal.

Narrate the plan aloud and call out red flags, that is the interview-winning behavior.