Home/Learn/SQL/EXPLAIN / EXPLAIN ANALYZE

EXPLAIN / EXPLAIN ANALYZE

Intermediate

Indexing & Performance

EXPLAIN shows the query execution plan chosen by the optimizer; EXPLAIN ANALYZE actually runs the query and reports real row counts and timing — the primary tool for diagnosing slow queries.

Overview

Every SQL query goes through the query planner, which picks an execution plan from many alternatives. EXPLAIN (without ANALYZE) shows the estimated plan — no rows are touched. EXPLAIN ANALYZE executes the query and augments the plan with actual row counts, loops, and timing. Key plan nodes to recognise: Seq Scan (full table read), Index Scan (B-tree lookup + heap fetch), Index Only Scan (no heap fetch), Bitmap Heap Scan (index + bitmap before heap), Hash Join, Nested Loop, and Merge Join. Each node shows cost=(startup..total), rows (estimated), and width (row size in bytes). A large mismatch between estimated and actual rows indicates stale statistics or skewed data.

Reading an EXPLAIN ANALYZE Output

Plans are read bottom-up (innermost child first). cost=0.00..X means X is the total page-fetch cost estimate. Actual time is in milliseconds. The outermost node's total cost is the query cost estimate.

SQL — annotated EXPLAIN ANALYZE output

-- PostgreSQL EXPLAIN ANALYZE for a JOIN query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
  AND u.city = 'Mumbai'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

/*  Sample output (annotated):

HashAggregate                              ← GROUP BY via hash
  (cost=1240.50..1255.50 rows=150 width=48)
  (actual time=28.3..29.1 rows=142 loops=1) ← actual rows close to estimate ✓
  ->  Hash Join                             ← JOIN strategy chosen
        Hash Cond: (o.user_id = u.id)
        ->  Index Scan on orders            ← index used for status filter
              Index Cond: (status = 'completed')
              (actual rows=8543 loops=1)
        ->  Hash                            ← hash table built from users
              ->  Seq Scan on users         ← no index on city → seq scan ⚠
                    Filter: (city = 'Mumbai')
                    Rows Removed by Filter: 9650
                    (actual rows=350 loops=1)
Planning Time: 1.2 ms
Execution Time: 31.4 ms
*/

Identifying Slow Nodes: Bad Nested Loop and Row Estimate Mismatch

A Nested Loop join is efficient only when the inner side is small or index-accessed. A Nested Loop on a large table without an index on the join column causes N full scans of the inner table.

SQL — bad nested loop detection and row estimate mismatch

-- Spotting a bad nested loop (inner table has no index on user_id)
/*
Nested Loop
  ->  Seq Scan on users             (rows=10000 loops=1)
  ->  Seq Scan on orders            (rows=100000 loops=10000)  ← 10000 full scans!
        Filter: (orders.user_id = users.id)

Fix: add index on orders.user_id
*/
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- After index: planner switches to Hash Join or Index-Nested Loop

-- Row estimate mismatch: planner estimates 10 rows, actual is 50000
-- Cause: stale statistics after a bulk INSERT
ANALYZE orders;   -- refresh stats
-- Or in MySQL:
ANALYZE TABLE orders;

-- EXPLAIN ANALYZE shows mismatch:
-- (cost=0.56..8.58 rows=10 width=24)
-- (actual time=0.1..45.3 rows=50000 loops=1)  ← huge mismatch → bad plan!

Key Points to Remember

1EXPLAIN shows the plan without executing; EXPLAIN ANALYZE executes and adds actual timings — use ANALYZE in development, not production under load.
2Plans are read bottom-up: child nodes execute first and feed rows upward to parent nodes.
3cost=(startup..total) is in abstract page units; actual time= is wall-clock milliseconds.
4A large gap between estimated rows and actual rows means stale statistics — run ANALYZE to fix.
5Seq Scan on a join's inner side inside a Nested Loop is a red flag — check for a missing index on the join column.
6Bitmap Heap Scan combines multiple index scans into a bitmap before accessing heap pages — efficient for medium-selectivity queries.

Interview Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EasyFlipkart

How do you read a query plan and identify which node is causing slowness?

MediumAmazon

What does a large mismatch between estimated and actual rows indicate in EXPLAIN ANALYZE?

MediumGoogle

When is a Nested Loop join efficient and when is it harmful?

HardMicrosoft

Ask Aria about EXPLAIN / EXPLAIN ANALYZE

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Index Selectivity & Cardinality

Query Optimization Patterns