Explain shows a plan; explain analyze executes the statement and reports observed behavior. Compare estimates, actual rows and timing.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: A large estimated-to-actual row mismatch can reveal statistics or data-correlation issues. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Distinguish planning from execution
EXPLAIN describes; EXPLAIN ANALYZE actually runs the statement.
Step 2: Compare estimates and observations
Look for row-count mismatches, repeated loops and costly sorts or scans.
Step 3: Choose safe evidence
Use isolated representative workloads for statements that modify data.
Worked scenario
A large estimated-to-actual row mismatch can reveal statistics or data-correlation issues.
A nested loop expected to process ten rows actually processes thousands, multiplying inner work. That mismatch suggests investigating statistics, selectivity and correlations. Timing alone does not explain the cause. EXPLAIN ANALYZE on DELETE deletes rows, so execution safety matters as much as plan interpretation.
Common mistake
Explain analyze on a modifying statement performs the modification unless contained appropriately.
Verify the behavior
Inspect actual rows, loops and buffer activity without modifying important data.
Interview exercise
Investigate a slow query safely.
Answer and reasoning
Use representative non-destructive workloads or an isolated transaction/environment, then inspect scans, joins, sorting and buffer activity.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.