Part 2 of 5 · 1 min read
EXPLAIN shows the actual plan, not a guess
EXPLAIN reveals the real scan type the database chose for a query.
Think of asking a delivery driver to describe the exact route taken. That beats guessing from delivery time alone whether they took the highway or the back roads.
In plain words: EXPLAIN, placed before a query, asks the database to report the execution plan it would use, or already used, for that query: which scan type it chose for each table, in what order tables are joined, and an estimate of cost and row count. Reading this plan directly answers the question "did this query use an index?" instead of relying on hallway assumptions about how the database must be behaving. Different databases phrase their output differently, Postgres, MySQL, and others, but the same core question, what scan type was chosen, is always answerable from it.
A worked example. Running EXPLAIN SELECT * FROM orders WHERE customer_id = 42; reports whether the database chose a full table scan or an index lookup via idx_orders_customer, settling the question directly rather than assuming.
The key lines.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- reports the actual chosen scan type for this specific query
Execution plan
An execution plan is the database's own report of how it will carry out, or did carry out, a specific query: which scan type it chose per table, the join order, and estimated cost and row counts.
Example: a delivery driver's reported route, rather than a guess from delivery time alone, is the execution-plan equivalent for a delivery.