Query Execution and EXPLAIN Plans

Summary#

A SQL query goes through five stages before it returns rows: parse → analyze → rewrite → plan → execute. The first three are mechanical; the fourth — plan — is where the query optimizer picks one of many possible execution strategies, and that choice can change the query’s runtime by orders of magnitude. EXPLAIN shows you the plan. EXPLAIN ANALYZE (Postgres) or EXPLAIN ANALYZE FORMAT=JSON (MySQL 8+) runs the query and shows the plan annotated with actual row counts and timings — the most useful single diagnostic any RDBMS gives you.

This page is about the third skill every backend engineer should have alongside writing SQL and designing schemas: reading plans. The four operators below cover ~90% of what you’ll meet; the rest are variations.

Why it matters#

A query that returns 100 rows in 5ms can return the same 100 rows in 5 minutes after a small schema or statistics change — the planner picked a different strategy. Without the ability to read a plan, “the query is slow” is unsolvable; with it, you can usually diagnose in two minutes.

Specifically:

• Index usage. A query that does a sequential scan over a 100M-row table is going to be slow. Plans tell you whether your index was used.
• Row estimates vs actual. Bad estimates lead to bad joins. ANALYZE updates statistics; stale stats are a top-three cause of regression incidents.
• Join order. N joins have N! possible orders. The optimizer picks one. Plans tell you which.
• Plan stability. A query that runs fast in dev and slow in prod often has different statistics. Plans tell you why.

Most “the database is slow” tickets at any company larger than a startup turn out to be one of: missing index, bad estimate, wrong join order, or a query that should have been a different query. Plans tell you which.

How it works#

The five-stage pipeline#

SQL text  →  parse        →  parse tree (raw AST)
              ↓
            analyze       →  query tree (resolved names, types, permissions)
              ↓
            rewrite       →  query tree (views inlined, rules applied)
              ↓
            plan          →  plan tree (chosen physical operators)
              ↓
            execute       →  rows

The first three are deterministic. The fourth depends on:

• Catalog statistics. pg_statistic (Postgres) or INFORMATION_SCHEMA.STATISTICS (MySQL) — row counts per table, value distributions per column, distinct counts.
• Cost model. A weighted sum of estimated CPU + I/O + network costs. Each operator has a cost function over its inputs.
• Search algorithm. For non-trivial join graphs the planner does a bounded search (dynamic programming for ≤12 tables in Postgres; genetic algorithm above that).

The four operators you’ll see most#

These cover the vast majority of plans:

1. Sequential scan (Seq Scan in Postgres, ALL in MySQL). Read every row of the table. Right when the predicate matches most rows or no useful index exists. Wrong when the predicate is very selective and an index exists.
2. Index scan (Index Scan, Index Only Scan). Walk an index to find matching rows, then either read the index entry alone or fetch the heap row. Index Only Scan is the fastest — no heap access — but requires every column the query needs to be in the index.
3. Hash join (Hash Join). Build an in-memory hash on the smaller input keyed by the join column; probe each row of the larger input. Best when one side fits in memory and there’s no useful sort order.
4. Nested-loop join (Nested Loop). For each row on the outer side, look up matches on the inner side (typically via an index). Best when the outer side is small and the inner side has an index on the join column.

Variations you’ll meet often: Bitmap Index Scan + Bitmap Heap Scan (multi-predicate index combination), Merge Join (sort both sides then merge — useful with pre-sorted inputs), Aggregate / HashAggregate / GroupAggregate, Sort, Limit, Materialize, Memoize.

EXPLAIN vs EXPLAIN ANALYZE#

EXPLAIN SELECT ...       -- planner's chosen plan; estimated costs and rows
EXPLAIN ANALYZE SELECT ...  -- runs it; shows estimated + actual rows, actual times
EXPLAIN (ANALYZE, BUFFERS) SELECT ...  -- + shared/local block hits and reads

EXPLAIN is free (no execution). EXPLAIN ANALYZE runs the query — including any side effects. For UPDATE/DELETE/INSERT, wrap in a transaction and roll back. ANALYZE is what catches estimate bugs: when the planner says rows=10 and the actual is rows=1000000, you have a statistics problem.

Variants and trade-offs#

Other axes:

• EXPLAIN ANALYZE vs production tracing. EXPLAIN ANALYZE is the ground truth on the box you run it on. Production may differ (concurrent load, different cache state, different parameter values). Always check on a real-prod replica when possible.
• Stable plans vs adaptive plans. Some engines (SQL Server, Oracle) have plan-cache mechanisms with hints to pin a plan. Postgres recomputes per statement (unless prepared). Pinning trades predictability for the loss of adaptive re-planning when stats change.
• Hints vs pg_hint_plan. MySQL has native query hints (/*+ INDEX(t idx_x) */); Postgres doesn’t — there’s an extension. Hints are a last resort; prefer fixing statistics or the query itself.

When this is asked in interviews#

Senior backend / data-engineer loops universally. Common shapes:

• “This query is slow. How do you debug it?” Expected answer walks through: capture the query, run EXPLAIN ANALYZE, identify the dominant operator, check estimates vs actuals, look for missing indexes / stale stats / bad join order.
• “What does this plan tell you?” Interviewer shows a printed plan; you read it. Practice this — read 10 real production plans and the pattern recognition gets sharp.
• “Why is the planner picking the wrong plan?” Usual answers: stats stale (run ANALYZE), parameter sniffing (try EXPLAIN ANALYZE with the actual parameters), out-of-date n_distinct for a column, missing extended statistics, or a query that’s genuinely ambiguous between two near-equal-cost plans.
• “What’s the cost of an Index Scan vs a Bitmap Heap Scan vs a Sequential Scan?” Index Scan: O(log N + K) where K = matching rows; cheap for selective predicates, expensive when K is large (random I/O). Bitmap: index walk → bitmap construction → sequential heap reads in physical order; better than Index Scan for large K. Sequential: O(N), no random I/O — best when K approaches N.

Read the plan, not the execution time alone. A query that takes 50ms because of a sequential scan over 10M rows might be the right plan if the predicate matches half the table; the same plan over 100M rows where you wanted index-scan is a bug.

Related concepts#

• B-Tree Indexes
• JOIN — INNER, LEFT, RIGHT, FULL, CROSS
• Window Functions
• Subqueries and CTEs