EXPLAIN vs EXPLAIN ANALYZE?

Definition:

EXPLAIN shows the execution plan the database expects to use. EXPLAIN ANALYZE actually executes the statement and adds observed rows and timing, which helps reveal bad estimates or expensive steps. Since it runs the statement, write queries and expensive reads need appropriate care.

Key points:

• EXPLAIN shows the planned execution steps and estimates.

• EXPLAIN ANALYZE executes the query and adds actual timings and row counts.

• Use care with writes: analysis executes them too; prefer a safe test database.

• Compare estimated and actual rows at each step, and use buffer information when investigating I/O costs.

Interview point:

• A large estimation error can explain a poor join choice; adding an index is not the only possible fix.