✏️ Explanatory Question

EXPLAIN ANALYZE — reading actual vs estimated rows to catch bad optimizer plans

👁 7 Views
📘 Detailed Answer
🟢 Easy
← Previous EXPLAIN ANALYZE — reading actual vs estimated rows to catch bad optimizer plans
💡

Answer with Explanation

137

EXPLAIN ANALYZE — reading actual vs estimated rows to catch bad plans

Level: Hard — Goes beyond plain EXPLAIN; the gap between estimated and actual rows reveals WHY the optimizer chose wrong.

Scenario: A query is slow, and plain EXPLAIN shows it "using an index" — so it looks fine on paper. Yet it runs for 10 seconds. The interviewer asks you to go deeper: "How do you see what actually happened at runtime, and how do you tell the optimizer made a bad estimate?"

EXPLAIN vs EXPLAIN ANALYZE

  • EXPLAIN: Shows the optimizer's planned execution — estimated rows, chosen indexes. It does not run the query.
  • EXPLAIN ANALYZE (8.0.18+): Actually runs the query and reports real timings, actual row counts, and how many times each step executed — estimates vs reality side by side.

Sample Query

EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 50000;

Reading the Output (tree format)

-> Nested loop inner join
   (cost=... rows=100)                      -- ESTIMATED 100 rows
   (actual time=0.5..85.2 rows=45000 loops=1)  -- ACTUAL 45,000 rows!
    -> Table scan on e
       (cost=... rows=100)
       (actual time=0.3..40.1 rows=45000 loops=1)
    -> Index lookup on d using PRIMARY (dept_id=e.dept_id)
       (actual time=0.001..0.001 rows=1 loops=45000)

How to Read Each Number

FieldMeaning
rows=100 (in cost)Optimizer's estimate
actual ... rows=45000Rows the step really produced
actual time=0.5..85.2Time to first row .. time to last row (ms)
loops=45000How many times the step ran (key for joins)

The smoking gun — estimate vs actual mismatch: The optimizer estimated 100 rows but actually processed 45,000. A large gap like this means the optimizer's statistics are stale or misleading, so it likely chose a bad plan (e.g., a nested loop that made sense for 100 rows but is disastrous for 45,000).

What a Big Estimate/Actual Gap Tells You

  • Stale statistics → run ANALYZE TABLE to refresh cardinality.
  • Wrong join order → a huge loops= value means the outer table is bigger than expected.
  • Bad selectivity guess → the optimizer misjudged how many rows a condition returns.
  • Look for the step with the highest actual time → that is your real bottleneck.

Fixing the Bad Plan

-- 1) Refresh statistics so estimates match reality
ANALYZE TABLE employees, departments;

-- 2) Add histogram statistics for skewed columns the optimizer misjudges
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 100 BUCKETS;

-- 3) Re-check: estimate should now be much closer to actual
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 50000;

-- 4) If still wrong, add a supporting index so the plan changes
CREATE INDEX idx_salary ON employees(salary);

Histograms — the underused fix: For columns with skewed data (e.g., 95% of orders are 'completed'), plain index statistics mislead the optimizer. A histogram (UPDATE HISTOGRAM) tells it the real value distribution, so it estimates row counts accurately — often fixing a bad plan without adding any index.

Interviewer follow-up: "Can you run EXPLAIN ANALYZE safely on a production write query?" → Be careful — it actually executes the statement, so an EXPLAIN ANALYZE DELETE/UPDATE will really modify data. For write statements, test on a copy or wrap it in a transaction and ROLLBACK. For read queries it is safe, though it does consume real resources while running.

← Previous EXPLAIN ANALYZE — reading actual vs estimated rows to catch bad optimizer plans