How a Query Executes — Parse, Plan, Run & EXPLAIN

What happens between SELECT and the rows coming back

You send SQL; the database doesn't run it literally — it compiles it into an execution plan and runs that. Understanding the pipeline is what lets you make slow queries fast.

The four stages

1. Parse — SQL text → syntax tree (catch typos, resolve table/column names).
2. Rewrite — expand views, apply rules.
3. Plan / optimize — the heart of it. The cost-based optimizer enumerates ways to run the query (which index, scan type, join order, join algorithm), estimates each one's cost from table statistics (row counts, value distribution/histograms), and picks the cheapest.
4. Execute — run the chosen plan: a tree of operators pulling rows from each other.

Reading EXPLAIN — the plan the optimizer chose

EXPLAIN shows the plan; EXPLAIN ANALYZE also runs it and reports real timings. Read it bottom-up — leaf operators execute first.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 7;

-- BEFORE an index:
Seq Scan on orders  (cost=0..18334  rows=12  width=64)
                    (actual time=0.2..86.4ms  rows=12)      <- scanned 1,000,000 rows to find 12

-- AFTER  CREATE INDEX ON orders(user_id);
Index Scan using orders_user_id on orders
                    (cost=0.4..33  rows=12)  (actual time=0.05..0.11ms  rows=12)  <- ~800x faster

Each node shows estimated cost, estimated rows, and (with ANALYZE) actual time + actual rows. The number that matters most:

If estimated rows is wildly different from actual rows, the optimizer is working from stale statistics and likely chose a bad plan. Run ANALYZE (refresh stats) and re-check.

What to look for

• Seq Scan on a big table with a selective filter → a missing/unused index.
• Nested Loop with huge actual rows → bad join order / missing index on the join key.
• Sort or Hash spilling to disk → raise work_mem or add an ordered index.
• estimate ≠ actual → stale stats; ANALYZE.

Takeaways

• SQL is compiled: parse → rewrite → cost-based plan → execute.
• The optimizer picks index/scan/join by estimated cost from statistics — keep them fresh.
• EXPLAIN ANALYZE is the truth: read bottom-up; a big estimate-vs-actual gap means stale stats.

---

Re-authored for this guide; query-pipeline diagram hand-authored as SVG. Follows the PostgreSQL "Using EXPLAIN" docs and CMU 15-445. See also: How Indexes Work, Indexes in Practice, Join Algorithms.

I just read a lesson on **How a Query Executes — Parse, Plan, Run & EXPLAIN** (Databases) and want to truly understand it. Explain How a Query Executes — Parse, Plan, Run & EXPLAIN from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **How a Query Executes — Parse, Plan, Run & EXPLAIN** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **How a Query Executes — Parse, Plan, Run & EXPLAIN** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **How a Query Executes — Parse, Plan, Run & EXPLAIN** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.