Does prefixing a statement with EXPLAIN run it, and what does EXPLAIN ANALYZE do differently?

## Two different requests There are two things a developer wants from a database when a query is slow, and they are not the same request. 1. **What plan would you choose?** That is plain `EXPLAIN`. The engine parses, plans and hands the plan back. No rows of your query are produced, nothing is written, and the statement's runtime cost is not paid. 2. **What actually happened when you ran it?** That is the analyze form. The engine executes the statement and returns the plan annotated with measurements taken during execution. Because the second form runs the statement, everything the statement does really happens. ## The write-statement trap The classic accident is treating `EXPLAIN` as universally read-only: ```sql EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP; ``` The rows are gone. Under autocommit they are gone permanently. The safe pattern is to bracket it in a transaction you never commit: ```sql BEGIN; EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP; ROLLBACK; ``` You still get real measurements, and the data is restored when the transaction unwinds. Two caveats: the work is genuinely performed, so locks are taken and other sessions can be blocked while it runs, and rolling back does not undo everything the world observed — a long-running measurement on a busy production table is still an intrusive act. Measure on a copy when you can. ## Reading what plain EXPLAIN gives you Plain `EXPLAIN` is cheap and safe, which makes it the right first tool while you are still editing the query. It tells you which access method the engine intends, which index it intends to use, and which of your predicates it can push into that access method. It cannot tell you how long anything took, because nothing ran; every number in it is the planner's own estimate. ## Engine spellings The idea is universal; the syntax is not. - PostgreSQL: `EXPLAIN stmt` and `EXPLAIN ANALYZE stmt`, plus parenthesised options. - MySQL: `EXPLAIN stmt`; MySQL 8.0 added `EXPLAIN ANALYZE`, which executes the statement. - Oracle: `EXPLAIN PLAN FOR stmt`, then read the plan table via `DBMS_XPLAN.DISPLAY`. - SQL Server: no `EXPLAIN` keyword — `SET SHOWPLAN_ALL ON` / `SET SHOWPLAN_XML ON` for the estimated plan, `SET STATISTICS PROFILE ON` / `SET STATISTICS XML ON` (or the client's "include actual execution plan" toggle) for the measured one. When you move between engines, check whether the form you are typing executes the statement before you type it against anything that matters. ## A workflow, not a party trick The habit worth forming as a query author is small and repeatable: run plain `EXPLAIN` while shaping the statement, switch to the analyze form on realistic data once the shape looks right, and re-run it after every edit so you know which edit did what. Guessing at a rewrite and shipping it without looking at a plan is how queries acquire changes that never helped anyone. ## What interviewers are checking They want to know you have actually used the tool: that you know the analyze form executes, that you know the transaction trick for writes, and that you do not assume the keyword is spelled the same on every product. Candidates who have only read about plans usually say "EXPLAIN shows the plan" and stop there.