Detailed explanation
An execution plan is the database's strategy for executing a SQL statement: which indexes or tables to scan, how to join tables, and where to sort or aggregate results. EXPLAIN is a command that displays that strategy, usually without executing the statement. EXPLAIN ANALYZE executes the statement and displays the plan annotated with runtime measurements. Therefore, the precise comparison is between an estimated plan and a plan with execution statistics, not between two unrelated concepts. Exact syntax and output depend on the database engine.
In PostgreSQL, plain EXPLAIN shows estimated startup cost, total cost, output rows, and average row width for each plan node. Costs are optimizer units, not milliseconds. The optimizer derives estimates from table statistics, value distributions, and assumptions about operations such as disk access. EXPLAIN ANALYZE adds actual startup and total times, rows, and loop counts. These measurements show how the selected strategy behaved for that execution; they do not mean the database tried every possible plan to find the fastest one.
For example, EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; might show rows=1000 in the estimated section and actual rows=90000 in the runtime section. That mismatch means the optimizer underestimated how many rows would match. Stale statistics, skewed customer activity, or correlations between filtered columns can cause such errors. Poor estimates can influence scan choices, join order, and join algorithms. Compare estimates with actual rows node by node, starting where the mismatch first appears. In PostgreSQL, actual rows and times are averages per execution when loops exceeds one.
This distinction matters because estimated plans explain the optimizer's expectations, while runtime plans provide evidence for tuning. However, EXPLAIN ANALYZE adds measurement overhead and really performs the work: modifying statements can change data, and expensive reads can load production systems. Interviewers expect you to distinguish costs from elapsed time, interpret cardinality errors, and mention execution safety rather than simply saying that ANALYZE is more detailed.
Key points to remember
- An execution plan is a strategy; EXPLAIN and EXPLAIN ANALYZE are ways to inspect it.
- Plain EXPLAIN normally reports estimates without executing the statement.
- EXPLAIN ANALYZE executes the statement and adds actual rows, timings, and loop counts.
- Optimizer costs are not milliseconds; large estimated-versus-actual row differences indicate cardinality estimation problems.
- Runtime analysis has overhead and execution side effects, so use it carefully on production systems.
Common mistakes candidates make
- Treating an execution plan and EXPLAIN ANALYZE as unrelated concepts instead of distinguishing estimated and runtime-annotated plans.
- Reading estimated costs as elapsed time or ignoring loop counts when interpreting PostgreSQL runtime measurements.
- Running EXPLAIN ANALYZE on expensive or data-modifying statements without considering workload impact and side effects.
Likely follow-up questions
What should you investigate when estimated and actual row counts differ greatly?
Check whether statistics are current and whether the data contains skew or correlated predicates that ordinary statistics do not capture well. In PostgreSQL, refreshing statistics with ANALYZE or adding suitable extended statistics can help, but neither is a universal fix.
Can you safely use EXPLAIN ANALYZE on an UPDATE or DELETE?
It executes the modification, so use a controlled environment whenever possible. In PostgreSQL, wrapping it in BEGIN and ROLLBACK can undo transactional data changes, but it still acquires locks, consumes resources, and cannot undo every possible side effect.
What does EXPLAIN (ANALYZE, BUFFERS) add in PostgreSQL?
It adds buffer activity, such as shared buffer hits and reads, to the runtime plan. This helps identify data-access-heavy nodes, although a buffer read does not necessarily mean a physical disk read because the operating system may cache the data.
Interview tip: Define the distinction first, walk through the 1,000-versus-90,000-row example, and finish with the warning that EXPLAIN ANALYZE actually executes the statement.